|
Using stored procedures
(2/23/2006) |
Question from Diaa
I want to know why stored procedure faster than select statement
Answer:
Stored procedures are faster in execution than add-hoc queries (not only select statement). There are a couple of reasons behind it that I just mention some of them.
The steps of executing any SQL statement by SQL Server are as follows:
Parse --> Validate à Optimize --> Compile --> Execute. These steps come one after another when you submit any add-hoc query to SQL Server. When you make a Stored Procedure after the first execution the compiled query is stored in the procedure cache in memory, which means all the four first steps are bypassed for the next execution.
The other very simple reason is that by doing a significant part of the application login on server using stored procedures you are actually minimizing client-server round trips which significantly enhances the performance.
The only disadvantage of using stored procedures is that you have to accept what ever the stored procedure returns while sometimes we need to sort or filter the data returned by a request. If this is your case consider using User-defined functions.
By the way if you need more info about user-defined functions just let me know in your comments.
Regards
Alireza |
|