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

Question

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 calculations.

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?

Answer 1

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.

Answer 2

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