Click here to Skip to main content
13,554,130 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


107 bookmarked
Posted 2 Jul 2012
Licenced CPOL

Stored Procedures DO NOT increase performance

, 2 Oct 2012
Rate this:
Please Sign up or sign in to vote.
Many developers think stored procedures are precompiled so they are faster. I have a different story to tell.

Table of contents


I assume that you have clicked on this article / blog because you are an awesome fan of stored procedures (like me) and you cannot see these kind of loose statements. My sincere suggestion would be to read this article once, give a thought on the experiments, and then the comments section is always there to throw bricks at me.

Stored Procedures are precompiled so they are faster

Ask any one why he prefers stored procedures as compared to inline queries and most will reply back with a standard statement:

“Stored procedures are precompiled and cached so the performance is much better.”

Let me just explain the above sentence more diagrammatically. When we fire SQL for the first time, three things happen:

  • The SQL syntax is checked for any errors.
  • The best plan is selected to execute the SQL (choice to use clustered index, non-clustered etc.).
  • Finally the SQL is executed.

The above statement states that when you run a stored procedure for the first time it will go through all the above steps and the plan will be cached in-memory. So the next time when the stored procedure is executed it just takes the plan from the cache and executes the same. This increases performance as the first two steps are completely eliminated.

The above statement also says / implies that for inline queries all the above steps are repeated again and again which brings down the performance considerably.

The above explanation was pretty valid and true for older versions of SQL Server, but from 2005 onwards, all SQL statements, irrespective of it’s a SQL coming from inline code or stored procedure or from anywhere else, they are compiled and cached.

OK, now walk your talk

Image from

In order prove the above point I did a couple of experiments. I wrote a simple .NET application which makes calls to SQL Server by using both methodologies, i.e., simple inline SQL and stored procedure.

Below is a simple experiment to prove the same.

We have created two scenarios: one which will run a simple inline SQL as shown below. This SQL goes and queries a simple “Users” table to check if a user exists in the database or not.

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='"
                                        + UserName + "' and Password='"
                                        + Password + "'", objConnection);

In the second scenario the same inline SQL is wrapped in a stored procedure called “sp_login”.

SqlCommand objCommand = new SqlCommand("sp_Login", objConnection);
objCommand.Parameters.Add(new SqlParameter("UserName", UserName));
objCommand.Parameters.Add(new SqlParameter("Password", Password));
objCommand.CommandType = CommandType.StoredProcedure;

Both these SQLs are fired from the application with a profiler running in the background. We capture two events when we ran the profiler: CacheHit and CacheInsert. The CacheInsert event is fired when the plan is inserted in the cache while CacheHit is fired when the plan is used from the cache.

When we ran the experiment with the stored procedure we saw the below results. You can see in the trace below:

CacheInsert” first creates the plan and inserts it into the cache. Once the plan is cached the CacheHit event occurs which means it has taken the plan from the cache rather than recreating it from scratch.

When we ran the experiment with inline SQL we saw similar kinds of results. You can see how the CacheHit event is hit after the CacheInsert event is fired.

Cheater, change the data?

If you see look at the previous experiment, the data is absolutely the same. The time I change the data as shown in the figure below, you can see it’s no longer using the cache, rather creating new cache entries. 


Let me go ahead and tweak the ADO.NET code to support parameters as shown below.

SqlCommand objCommand = new SqlCommand(
   "Select * from Users where UserName=@userName and Password=@Password", objConnection);
objCommand.Parameters.AddWithValue("@userName", UserName);
objCommand.Parameters.AddWithValue("@Password", Password);

When I capture the cache events in the profiler it is using the cache. You can see in the below figure how first the cache insert event occurs and after that it always hits the cache for the plan rather than recreating it.

Dynamic SQL and Dynamic SQL

One of the most confusing terminologies people use is Dynamic SQL. Let’s refine this word further. There are two types of dynamic SQL: one is dynamic SQL and the other is parameterized dynamic SQL.

Courtesy: Spiderman 3

Dynamic SQL is of the form as shown below (it can be more dynamic where column names are also built on the fly).

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='"
                                        + UserName + "' and Password='"
                                        + Password + "'", objConnection);

The above dynamic SQL will probably not use the plan from the cache until auto parameterization helps (

If you use parameterized dynamic SQL like below, it will use the SQL plan from the cache as done by stored procedures.

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName=@userName and Password=@Password", objConnection);

objCommand.Parameters.AddWithValue("@userName", UserName);
objCommand.Parameters.AddWithValue("@Password", Password);

In simple words performance of inline parameterized SQL is the same as that of Stored Procedures.

Hmm, what about network traffic?

If you have read so far you must be embarrassed, the way I was when I lost this argument. To counter protect many developers would also argue that network traffic decrease when using stored procedures is less as compared to that for inline SQL.

In simple words if we use stored procedures we just send:


If we use inline SQL we send the complete SQL which will increase traffic.

'Select * from Users where UserName=@UserName and Password=@Password'

Must be this is a valid point if we are having many Windows apps pounding on one SQL Server. That can lead to a lot of network traffic if there are a lot of transactions.

In case of web applications where the SQL and the ASP.NET code (in the same data center) are very much near I do not really buy this point out.

As said this is just my personal opinion.

I will still use stored procedures

At the end of the day I will still prefer stored procedures. The choice of choosing stored procedures will not be performance but it will be more from the aspect of security and maintenance. Below are some of the points where stored procedures are definitely a plus over inline SQL.


By putting all your SQL code into a stored procedure, your application is completely abstracted from the field names, tables names, etc. So when you make changes in the SQL, you have less impact in your C# code.


This is the best part where stored procedures again score, you can assign execution rights on users and roles.

Maintenance ease

Now because we have centralized our stored procedures any issue like fixing defects and other changes can be easily done in a stored procedure and it will be reflected across the installed clients. At least we do not need to compile and deploy DLLs and EXEs.

Centralized tuning

If we know we have a slow running stored procedure, we can isolate it and the DBA guys can performance tune it separately.

Cursors, temp table complications

Simple TSQLs are OK. But what if you have a bunch of statements with IF, ELSE, Cursors, etc? For those kind of scenarios, again stored procedures are very handy.


You can also watch the below video where I have demonstrated my point using samples if you still do not trust this article:

<OBJECT type="application/x-shockwave-flash" codebase=",0,0,0" WIDTH="560" HEIGHT="315" data=""><PARAM NAME="movie" VALUE=""><PARAM NAME="quality" VALUE="high"><PARAM NAME="wmode" value="transparent">

and also feel free to visit my site for more SQL Server interview questions.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

You may also be interested in...

Comments and Discussions

GeneralRe: Tough Topic to Tackle Pin
Shivprasad koirala17-Jul-12 17:14
memberShivprasad koirala17-Jul-12 17:14 
GeneralMy vote of 4 Pin
Amol_B16-Jul-12 0:18
memberAmol_B16-Jul-12 0:18 
GeneralMy vote of 1 Pin
fpnzmarksh15-Jul-12 19:57
memberfpnzmarksh15-Jul-12 19:57 
GeneralRe: My vote of 1 Pin
Shivprasad koirala15-Jul-12 20:14
memberShivprasad koirala15-Jul-12 20:14 
QuestionFantastic Pin
vbfengshui14-Jul-12 10:30
membervbfengshui14-Jul-12 10:30 
AnswerRe: Fantastic Pin
Shivprasad koirala14-Jul-12 17:59
memberShivprasad koirala14-Jul-12 17:59 
GeneralRe: Fantastic Pin
vbfengshui14-Jul-12 18:34
membervbfengshui14-Jul-12 18:34 
QuestionThe title should be, "Stored procedures do not always increase performance" Pin
JesperMadsen12314-Jul-12 7:48
memberJesperMadsen12314-Jul-12 7:48 
The article is correct, but is misleading, due to the simple examples chosen.

A dynamic query will be "compiled" and stored in the query cache, as well as a stored procedure. The plan is stored with the dynamic sql statement as key. This means if the exact same statement is run again, then there is no problem. If the dynamic sql does not use bind variables, and the "variables changes", each permutation of the dynamic sql will be compiled, and the query plan will be saved for reuse (until it is dropped by the server). There is a finite number of queries available to the query-cache. If these cached statements are wasted on permutations of the same statements, (e.g. statements that require a user id on systems with 1000 , 10000 or 100000 users), dynamic sql will lead to miserable performance, due to statement cache trashing. When a statement is executed that is not found in cache, a query plan must be created, and that can be expensive.

Dynamic sql can also lead to unnecessary network bandwidth usage. When using a stored procedure it is possible to use T-SQL, and do some extractions server side, that will process multiple tables and temporary result, resulting in a small result set. When doing dynamic sql, you usually have simple statements, processing is client side, and a lot of unnecessary data is transferred to the client side.

Dynamic sql can be faster when than using a precompiled stored procedure when your data is very unevenly distributed, and you require a different query plan depending on bind variables. Here you can either let the stored procedure be recompiled on every call, or is you can anticipate the pattern, use one stored procedure to branch to two or more different stored procedures (with the same implementation, but different names and different execution plans).

Stored procedures are usually a layer between the database and your client, therefore I think your example is too simple. You have a statement, that accesses one table, that requires a "no brainer" execution plan (if there is an index, use the index, else use full table scan). You should try setting up a sample, where you generate statistics for a report, saying which users have seen what table entries on an hourly basis, or something similar. Something that requires joining and selection, that cannot be accomplished using a single statement, and a statement that will only return a fragment of the data it processes. I think you will see a big difference at some point.
AnswerRe: The title should be, "Stored procedures do not always increase performance" Pin
Shivprasad koirala15-Jul-12 20:23
memberShivprasad koirala15-Jul-12 20:23 
AnswerRe: The title should be, "Stored procedures do not always increase performance" Pin
Jasmine250116-Jul-12 11:44
memberJasmine250116-Jul-12 11:44 
GeneralRe: The title should be, "Stored procedures do not always increase performance" Pin
Shivprasad koirala17-Jul-12 17:10
memberShivprasad koirala17-Jul-12 17:10 
GeneralRe: The title should be, "Stored procedures do not always increase performance" Pin
Jasmine250118-Jul-12 5:15
memberJasmine250118-Jul-12 5:15 
General[My vote of 1] Dont think so Pin
Anoop Ananthan13-Jul-12 20:42
memberAnoop Ananthan13-Jul-12 20:42 
GeneralRe: [My vote of 1] Dont think so Pin
Shivprasad koirala13-Jul-12 21:59
memberShivprasad koirala13-Jul-12 21:59 
SuggestionSome Feedback Pin
AspDotNetDev13-Jul-12 7:31
protectorAspDotNetDev13-Jul-12 7:31 
GeneralRe: Some Feedback Pin
Shivprasad koirala14-Jul-12 1:11
memberShivprasad koirala14-Jul-12 1:11 
GeneralMy vote of 5 Pin
taha bahrami12-Jul-12 0:10
membertaha bahrami12-Jul-12 0:10 
GeneralMy vote of 4 Pin
Prafulla Hunde10-Jul-12 20:11
memberPrafulla Hunde10-Jul-12 20:11 
QuestionAbout cursors Pin
armagedescu9-Jul-12 10:37
memberarmagedescu9-Jul-12 10:37 
QuestionAre you using right driver? Pin
armagedescu9-Jul-12 3:17
memberarmagedescu9-Jul-12 3:17 
QuestionQuite interesting stuff Pin
GanesanSenthilvel9-Jul-12 0:55
memberGanesanSenthilvel9-Jul-12 0:55 
GeneralThese tests are too simplistic to extrapolate a generic answer Pin
David Lean8-Jul-12 20:16
memberDavid Lean8-Jul-12 20:16 
GeneralRe: These tests are too simplistic to extrapolate a generic answer Pin
Shivprasad koirala8-Jul-12 22:09
memberShivprasad koirala8-Jul-12 22:09 
QuestionMisleading Pin
Jakub Müller8-Jul-12 15:43
memberJakub Müller8-Jul-12 15:43 
GeneralRe: Misleading Pin
Shivprasad koirala8-Jul-12 22:14
memberShivprasad koirala8-Jul-12 22:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180515.1 | Last Updated 2 Oct 2012
Article Copyright 2012 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid