Do Stored Procedures really boost performance in MS SQL / .NET?

Question

Jeff Atwood wrote about this here, and while I understand the theoretical performance boost a stored procedure could offer, it does seem like a tremendous pain.

What types of queries would you see the most performance increase using stored procedures, and what types of queries would you rather just build on the fly?

Any documentation one way or another would be greatly appreciated.

Answer 1

The stored proc/no stored procs argument has become a religious issue. For every person that emphasizes optimized execution plans for procs, another points out common dynamic queries are cached and optimized in most modern DBMSes. For anyone that points out the security a proc might offer, another explains that dynamic queries can be made just as secure. Some like the flexibility of changing a proc without recompiling your app, while others argue queries should be captured in app code so they live and grow in the same code base.

I say...

Do what you like. I doubt we can come up with the correct answer. If procs are a hassle, don't use them. If they seem like a good idea, go for it. I've worked with both models and I honestly don't have a preference. I'm productive with or without 'em.

Answer 2

In days-gone-by, there were considerable performance benefits from using stored procedures, but query plan re-use is now significantly better, such that the two are nearly the same in many cases.

If you are building dynamic SQL at the server, you can further increase query plan re-use (and injection safety) by using sp_ExecuteSQL (rather than just EXEC) to execute the SQL.

There are some advantages to using stored procedures:

• They fix exactly what will happen at the db
• For tight security scenarios, you can tightly control access to the stored procedure (rather than the table)
• You can sub-divide the query if the plan is going freaky in spots (sniffing)

However, there are advantages to SQL too:

• It allows ORM tools (LINQ, for example) to write composable queries (page 4, ordered by Foo,Bar) on the fly
• It allows you to write dynamic code in a more expressive language (TSQL is not really intended to be used to write TSQL!)
• You generally have better tooling

Some thing where I would definitely use an SP is in (for example) a data-migration step - i.e. for a bulk operation I might use SqlBulkCopy to push the data into a staging table, then a stored procedure to move the data over. Beyond that, I'm fairly flexible.