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?
Edit: Any documentation one way or another would be greatly appreciated - thanks!
The idea behind Stored Procedures being faster is that they can store cached query plans, kinda like precompiling the stored procedure. So if the procedure is very complex with lots of joins between lots of tables you may see a speed benefit.
I think stored procs are desired for more than their speed, they help abstract the database schema and help to prevent sql injection attacks.
Frans Bouma caused a stir a while back with this topic.
On SQL Server, it's more important to understand that any performance increase is due to the query-plan being cached. A query plan is quite expensive to compile, so it makes sense to cache them. EDIT: Ad-hoc SQL is less likely to be matched to a cached query plan.. Dynamic SQL can be cached if it is run by calling sp_executesql.
If you use EXEC to run SQL, e.g.'EXEC @dynamic_sql', it will cache a plan but will not parametrise (or normalise it), meaning that unless exactly the same query is sent it will not re-use the cached query plan.
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.
Stored Procedures providing a performance benefit is a myth held over from earlier versions of SQL Server. SQL Server > version 7 treats all queries the same and will cache execution plans for all commonly used queries, no matter their genesis.
I wonder how many more years this myth will live on.
A badly written stored procedure will give no performance boost. The same as a bad inline query will have worse performance than a well thought out one.
The greatest advantages of stored procedures, in my opinion, are:
1/ They are centralized at the database management system so the DBAs know exactly what they're doing and can optimize the database to suit them. Ad-hoc queries from clients are much harder to do this for. I've seen databases bought to their knees because the DBAs allowed unfettered queries. They also force clients to think about what they need.
2/ They can do very complicated processing at the server minimizing the amount of data sent across the wire. This saves the applications from getting a bucketload of data, deciding something from it, then getting more data, and so on.
By all means, allow any random queries during development but, before you go to production, work out which DB actions should be moved to stored procs.
As a part-time DBA, I think that means all of them but it's not necessary as long as you keep a modicum of control over what queries the applications can send through.
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:
However, there are advantages to SQL too:
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.
We've found a benefit of Stored Procedures is that once initially created by the Developer, they can be handed over to the DBA or Database Tuning experts to be better written for performance reasons if needs be.
The DBA's obviously don't need access to the application code to do this, and can work alongside a pending application release.
Granted, the same is true of inline or embedded SQL queries, but I think Stored Procedures lend themselves better to this co-operative, larger team way of working.
One of the ideas behind a stored procedure is that it allows you to do several things in a single trip to the database.
MSSQL allows query batches to be submitted in a single request; this is roughly equivalent.
However, the principle is the same. In a large scale distributed system, latency to the database (rather than time the query takes on the server) can be a performance issue. Issuing fewer queries can be of benefit.
So using query batches or stored procedures vs issuing single queries is a potential performance win in a distributed system.
As is common with software, the SQL caching issue is more subtle than it looks. For example, let's look at caching of an ad-hoc SQL query:
-- First, clear the cache
DBCC FREEPROCCACHE
-- Look at what executable plans are in cache
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- Execute the following statement
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99
-- Look at what executable plans are in cache and you'll
-- find that there's a plan for a NUMERIC(4,2)
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- If you execute the EXACT same statement with a 4,2
-- then you will get THAT plan. But if you execute with a 5,2
-- then you'll get a new plan. Try this:
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99
-- Look again at the cached executable plans, and you'll see a NEW one...
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
You can however use sp_executesql to type the parameters and force the plan to be cached. All subsequent uses will get the same plan, but some people don't like the obscurity of this approach:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT t.* FROM dbo.titles AS t WHERE t.price = @price'
EXEC sp_executesql @ExecStr, N'@price money', 19.99
Now if you create a similar query as a stored procedure with a parameter for the price, the plan will be created and cached in memory (not disk) on the first execution, and reused regardless of the parameter's value.
The fact that the stored procedure plan is cached in memory and not on disk means that it will fall out of the cache on a server restart or due to low re-use. It can also fall out of cache if the data on which the procedure depends changes enough to cause the statistics to be invalidated. This causes SQL Server to invalidate the plan.
One thing people are skipping are that all those CRUD stored procedures are filled with coalesce and if statements and those are painfully slow.
If you are using parametrized statements vs a coalesce using stored procedure the dynamic statements are going to win.