Does stored procedure increase performance


When you ask any dot net developer that why he prefer stored procedure as compared to inline queries. Most of them will reply back with standard statement like;

  1. Stored procedure are pre compiled
  2. They are cached so the performance must be better.

In this article you will know that this are miss conception that stored procedures increase performance. Now first understand this statement in which developer say that they are pre complied and cached. So when you fire SQL for first time, then there three things happens,

  • The SQL syntax is checked for any error.
  • Best plan is selected to execute the SQL based on selected or non-selected indexes.
  • And finally the SQL is executed.

Now when second time SQL is run it again goes through this three stages. But in case of stored procedure there is an exception. In case of stored procedure the plane which is generated at first time it get cached in other word when second time stored procedure runs it does not have to generate the plan. It just goes and executes the query.

                This statements where valid for the older version of the SQL servers, but for SQL server 2005 onwards all SQL statement irrespective it come form inline SQL, SQL analyzer, stored procedure, or from anywhere they are compiled and cached in other word if you fire SQL in the form of the stored procedure or either you fire from other dot net code both of them will have same kind of performance. 

                You can see your self with the help of SQL profiler when inline SQL is done then does it is cached or not. And you will get to know that in SQL server 2005 and onward which ever statement you fire is first cached and then used again and again.

Leave a Comment

Your email address will not be published.