Hi all,
Are there any performance gains in using stored procedures for simple SQL inserts into tables over using direct SQL from java / JDBC? in my case I am using sybase but this could be a more general question.
Thanks
+3
A:
As long as you're using parameterized queries rather than building you queries via string concatenation, no (actually, you don't need the parameterized queries for performance either, just security).
A stored procedure won't give you performance gains on simple inserts.
Justin Niessner
2009-12-03 14:54:17
Not entirely true - a cached, compiled query that is not parameterised cannot be reused when the parameter values change - certainly not in all DBMSs (you may be able to name some where it would be true, but there are certainly ones where it isn't). So for performance as well as security you should parameterise.
David M
2009-12-03 15:00:45
@David M - True, but he's talking about simple inserts. On those kinds of queries, a cached query plan isn't going to buy you much. For more complex queries, I totally agree.
Justin Niessner
2009-12-03 15:13:09
For single, unrelated inserts there should be no performance gain but if these simple inserts are related to each other and need to be done together or as a transaction, wouldn't a stored proc be more efficient to avoid multiple trips back and forth?
DyingCactus
2009-12-03 15:37:47
@DyingCactus - That may be the case, but my guess would be any gain would be minimal. If your inserts get pre-compiled, and you use JDBC transactions, I'd imagine performance would be near on par. In my opinion, the gain from Stored Procedures is often negligable compared to the maintanence they introduce. Jeff Atwood summed it up far better than I ever could: http://www.codinghorror.com/blog/archives/000117.html
Justin Niessner
2009-12-03 15:50:45
A:
On one hand it looks like there will be a performance improvement in going with SPs. Your insert statement is pre-compiled.
But, even if you are using SPs, you will be invoking the SPs via JDBC. Hence parsing that invocation, and binding the SP parameter-list will also require processing. If its 30-columns that you are trying to insert, thats still 30 parameters into the SP, that will need to be bound in the JDBC call.
Hence if its a simple SP thats doing an insert, I dont think Stored Procedures will buy you significant gains.
blispr
2009-12-03 15:32:24
I see how pre-compiled queries improve performance. How will it improve performance of inserts?
Raj N
2009-12-03 15:38:11
Every statement that the Database engine executes needs to go thru a cycle of Parse-compile-plan-execute (crudely speaking). Pre-compiling helps in that regard. So a pre-compiled statement avoids some steps.However, in your case by using an SP, the insert syntax will have been pre-compiled, but the 'exec mySP(parm1,parm2)' statement will still need to syntax-validated by the db-engine.
blispr
2009-12-03 16:09:03