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 have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would be interested in any and all databases.

share|improve this question
1  
Can you post links to some of what you have read? I don't think performance is at stake here (at least not directly) –  Jack Douglas Aug 17 '11 at 0:06
    
@JackDouglas, check out mrdenny 's answer. Performance is very much a part of this question/answer. –  Thomas Stringer Aug 17 '11 at 0:18
add comment

3 Answers 3

up vote 28 down vote accepted

It is less so in SQL Server 2008 and higher, but it is still there. What it comes down to is the execution plan cache and SQL Server being able to auto-parametrize queries which are sent in. When using stored procedures (that don't have dynamic SQL within them) the queries are already parametrized so SQL Server doesn't need to generate a plan for each query when it's run as the plans are already stored in the plan cache.

And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.

When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly. So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.

If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return. Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.

Given that SQL Injection is the easiest way to break into a database, this is kind of important.

share|improve this answer
1  
+1 great answer. –  Thomas Stringer Aug 17 '11 at 0:16
    
@mrdenny - can you get the same effect with "raw queries" if they are parameterized? –  Jack Douglas Aug 17 '11 at 0:24
1  
@mrdenny - can you explain that in a bit more detail? On the face of it, the issues with dynamic SQL are the same for SPs and plain queries - and permissions can be applied to base objects in the same way they can to SPs, can't they? –  Jack Douglas Aug 17 '11 at 0:58
1  
+1 for humouring me :) –  Jack Douglas Aug 17 '11 at 1:31
1  
No problem, happy to. –  mrdenny Aug 17 '11 at 1:38
show 3 more comments

As an addendum to Denny's answer, it's not uncommon to find systems where significant buffer pool memory is wasted on single or low use ad-hoc execution plans, created as a result of queries being used over procs.

Worst cases lately, 8GB allocated to an instance, 3GB plan cache, 2.5GB single use plans. The majority of these have been SQL2005 so it hasn't been an option to try the optimise for ad-hoc workloads setting.

It's certainly getting more difficult to include performance in a justification for procedures over raw queries. One of the strongest arguments for me now is "If you use procedures, it's far easier for me to help when performance issues arise". A dynamic/linq/orm interface doesn't prevent you from tuning, but it can severely limit your options.

share|improve this answer
    
There's a great related article here, including scripts to delete those single-use plans. sqlskills.com/blogs/kimberly/… –  SomeGuy Mar 15 '13 at 15:15
add comment

SQL Server caches and optimizes stored procedures and ad-hoc SQL in the same way. For example, this procedure:

create procedure dbo.TestSB(@id int) as select * from Orders where id = @id

Will be optimzed and cached identically to:

select * from Orders where id = @id

However, the following ad-hoc SQL cannot be cached effectively, because of the hardcoded value:

select * from Orders where id = 42

Although performance is the same, there are good reasons to use stored procedures. Stored procedures provide a clear separation between DBA and application developers. It's good to have an extra layer of defense between your valuable data and constantly changing programs :)

share|improve this answer
    
+1 especially if you force all access to go through your SPs and they are well thought out as a transactional API not just a CRUD layer –  Jack Douglas Aug 17 '11 at 12:07
    
In 2008+ the id = 42 query can be optimized using the same plan depending on simple/forced parameterization settings. Of course queries should be properly parameterized. :-) –  Aaron Bertrand Jun 14 '13 at 17:23
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.