What is the advantage of using stored procedures to do insert/update/delete/select?

I am currently looking at some code that is doing even trivial insert/select/update by running stored procedures.

So the code basically does

CallableStatememt stm= jdbcConnection.prepareCall(sp_name with ??) ;
stm.setParameters()
stm.execute();

As I said before the code behint sp_name is mostly trivial. No multi table inserts or complicated cal开发者_开发问答culations.

Are there any benefits against just doing

Statement stm = jdbcConnection.prepareStatement(insert_query)
stm.setParameters();
stm.execute();

where insert_queryis a 'normal' single INSERT / SELECT / ... statement?

Three main performance advantages come to mind: 1. The string sent to the SQL Server is shorter
2. That makes the parsing time shorter
3. The execution plan is ready in advance

Although seemingly trivial, the latter point can be significant; checking that objects exist, the correct number of fields, etc.

Overall, however, I'd say those only matter when being called many many times in succession.

A more significant advantage, in my opinion, is an engineering one; Encapsulation.

In the future, you may decide to add logging, or consistency checks, business logic, or anything. By encapsulating it in an SP, it will only ever need revising in one place.

A stored procedure is a precompiled executable object that contains one or more SQL statements.Since, stored procedures are precompiled objects they execute faster at the database server. Most of the time, stored procedures contain more than one command;

See more here :

• Insert-update-delete vs Stored procedures

More on Stored procedures :

• Advantages and Disadvantages of Stored Procedures

The main advantages of using Stored Procedure are:

1. All the sql commands in the SP will execute together or none will get executed. This ensures atomicity. All sql commands in SP are in transaction scope.
2. SPs are much faster than normal sql commands. SPs are compiled and optimized by the DB by creating proper execution plan.
3. control flow statements like IF-ELSE and loops like FOR can be used in SPs but not in sql queries. This saves lot of data being fetched in the DAO and then processing the data.
4. Reduces network traffic as only required data is transmitted though the network.
5. Modularises the code of the SP as it helps in reusability and helps to maintain the logic.