I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.
I'd like to know the technical reasoning for this (in a way that I can explain it to someone later).
Can anyone help me formulate a good answer?
Best Answer
I believe this sentiment was true at one point, but not in current versions of SQL Server. The whole problem was that in the old days ad hoc SQL statements could not be properly optimized because SQL Server could only optimize / compile at the batch level. Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure.
I still prefer stored procedures from the DBA side for the following reasons (and several of them can have a huge impact on performance):
sys.sql_modules
, for references to specific objects) makes everyone's lives much easier.SET ANSI_WARNINGS ON
, and the other could haveSET ANSI_WARNINGS OFF
, and they would each have their own copy of the plan. The plan they get depends on the parameters in use, stats in place, etc. the first time the query is called in each case, which could lead to different plans and hence very different performance.That all said, this question is likely to stir up more religious arguments than technical debate. If we see that happening we'll probably shut it down.