Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
A properly parametrized query is just as good as a stored procedure, from a performance point of view. Both gets compiled before first use, both will reuse the cached execution plan on subsequent executions, both plans get stored in the same plan cache and both will get handled the same name. There is no performance benefit for a stored procedure anymore, in SQL Server today. –  marc_s Jun 14 '13 at 20:51
    
@marc_s that's true if the queries are identical. However as I pointed out in my answer there are some characteristics of ad hoc queries that can be performance issues even for queries that seem identical. –  Aaron Bertrand Jun 14 '13 at 22:57
add comment

1 Answer 1

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):

  • If I have multiple apps that re-use the same queries, a stored procedure encapsulates that logic, rather than littering the same ad hoc query multiple times in different codebases. Applications that re-use the same queries can also be subject to plan cache bloat, unless they are copied verbatim. Even differences in case and white space can lead to multiple versions of the same plan being stored (wasteful).
  • I can inspect and troubleshoot what a query is doing without having access to the application source code.
  • I can also control (and know in advance) what queries the application can run, what tables it can access and in what context, etc. If the developers are writing queries ad-hoc in their application, they're either going to have to come tug my shirt sleeve every time they need access to a table that I didn't know about or couldn't predict, or if I'm less responsible/enthused and/or security-conscious, I'm just going to promote that user to dbo so they stop bugging me. Typically this is done when the developers outnumber the DBAs or the DBAs are stubborn. That last point is our bad, and we need to be better about providing the queries that you need.
  • On a related note, a set of stored procedures is a very easy way to inventory exactly what queries may be running on my system. As soon as an application is allowed to bypass procedures and submit its own ad-hoc queries, in order to find them, I have to run a trace that covers an entire business cycle, or parse through all of the application code (again, that I might not have access to) to find anything that looks like a query. Being able to see the list of stored procedures (and grep a single source, sys.sql_modules, for references to specific objects) makes everyone's lives much easier.
  • I can go to much greater lengths to prevent SQL injection; even if I take input and execute it with dynamic SQL, I can control a lot of what is allowed to happen. I have no control over what a developer is doing when constructing inline SQL statements.
  • I can optimize the query (or queries) without having access to application source code, the ability to make changes, the knowledge of the application language to do so effectively, the authority (never mind the hassle) to re-compile and re-deploy the app, etc. This is particularly problematic if the app is distributed.
  • I can force certain set options within the stored procedure to avoid individual queries from being subject to some of the Slow in the application, fast in SSMS? problems.
  • I can control things like data types and how parameters are used, unlike certain ORMs - some earlier versions of things like EF would parameterize a query based on the length of a parameter, so if I had a parameter N'Smith' and another N'Johnson' I would get two different versions of the plan. They've fixed this. They've fixed this but what else is still broken?
  • I can do things that ORMs and other "helpful" frameworks and libraries are not yet able to support.

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.

share|improve this answer
    
Another reason for stored procedures? For long, complicated queries, you have to push the query to the server every time, unless it's a sproc, then you are basically just pushing "exec sprocname" and a few parameters. This could make a difference on a slow (or busy) network. –  David Crowell Mar 10 at 13:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.