performance of stored procedures

There are very few circumstances in which only a query will work. In general, a stored procedure can do anything a query can do, but more, and faster. The stored procedure has already been processed and an execution plan created for it. Every time a query is run, this task must be done. So the stored procedure has that performance advantage. The stored procedure can also do some things that queries cannot, but can also use a lot of advanced stuff that queries cannot. Stored procedures are considered a different type of source, so will work in different situations where queries won't and vice versa, such as in unions. This is their greatest difference. In general, a server costs a certain amount of money to buy for both the machine and the software license. The more work you can get it to do before upgrading the machine or buying a second machine altogether, the more money you save. If your stored procedures run twice as fast or with half the resources and all your tasks are contained in stored procedures, your total cost of ownership is cut in half. In addition, if you use stored procedures, your database administrator can modify the database to tune it for the task at hand, and programs will not have to change to adapt. The stored procedures can be modified and not break anything. This is a huge reason to use stored procedures. Lastly, if 10 programs all use the same query to perform the same task, you have to keep them in synch when changes are made. With a stored procedure, you only change it once and all the programs benefit from it. Yes, you can mix and match stored procs and queries all you like, but stored procs can also be used as recordsets inside queries or other stored procs where queries may not work. You can see the performance by simply running the queries repeatedly and timing them. I have done this, and stored procedures have always been considerably faster for me.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

sunil21y ago
That was a good information. Thanks.
spgilmore21y ago
There are very few circumstances in which only a query will work. In general, a stored procedure can do anything a query can do, but more, and faster. The stored procedure has already been processed and an execution plan created for it. Every time a query is run, this task must be done. So the stored procedure has that performance advantage. The stored procedure can also do some things that queries cannot, but can also use a lot of advanced stuff that queries cannot. Stored procedures are considered a different type of source, so will work in different situations where queries won't and vice versa, such as in unions. This is their greatest difference. In general, a server costs a certain amount of money to buy for both the machine and the software license. The more work you can get it to do before upgrading the machine or buying a second machine altogether, the more money you save. If your stored procedures run twice as fast or with half the resources and all your tasks are contained in stored procedures, your total cost of ownership is cut in half. In addition, if you use stored procedures, your database administrator can modify the database to tune it for the task at hand, and programs will not have to change to adapt. The stored procedures can be modified and not break anything. This is a huge reason to use stored procedures. Lastly, if 10 programs all use the same query to perform the same task, you have to keep them in synch when changes are made. With a stored procedure, you only change it once and all the programs benefit from it. Yes, you can mix and match stored procs and queries all you like, but stored procs can also be used as recordsets inside queries or other stored procs where queries may not work. You can see the performance by simply running the queries repeatedly and timing them. I have done this, and stored procedures have always been considerably faster for me.