Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Revisions
 

Stored procedures DO NOT increase performance

, 13 Jul 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.
This is an old version of the currently published article.

Introduction

Stored procedures are precompiled so they are faster.

Ok, now walk your talk

Cheater, Change the data?

Dynamic SQL and Dynamic SQL

Hmm, What about network traffic?

I will still use stored procedures☺

Abstraction

Security

Maintenance ease

Centralized Tuning

Cursors, temp tables complications

References

Introduction

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 experiments and then the comments sections are 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 them 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. So when we fire SQL for the first time 3 things happen:-

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

So the above statement states that when you run stored procedure for the first time it will go through all the above 3 steps and the plan will be cached in-memory. So next time when the stored procedure is executed it just takes the plan from 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 performance considerably.

 

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

Ok, now walk your talk

 

Image from  http://www.stegman.com/site/wp-content/uploads/2011/07/Man-walking-rope.jpg

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

Below follows a simple experiment to prove the same.

So 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 the 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 in wrapped in to a stored procedure called as “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 SQL’s where fired from the application with profiler running at the back ground. We captured two events when we ran the profiler “CacheHit” and “CacheInsert”. “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 stored procedure we saw the below results. You can see in the trace below:-

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

 

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

 

Cheater, Change the data? 

If you see in the previous practical the data is absolutely same. The time I change the data as shown in the figure below, you can see it’s no more 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 captured the cache events in the profiler it is using 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 most confusing terminology 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

So dynamic SQL is of form as shown below(Its can be more dynamic where column names are also built on 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 cache until auto parameterization helps (http://msdn.microsoft.com/en-us/library/aa175264(v=sql.80).aspx).

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 same 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 the network traffic decreases when using stored procedures is less as compared to inline SQL.

In simple words if we use stored procedures we just send

Sp_login 

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 app pounding on one SQL server. So that can lead to lot of network traffic if there are lots of transactions.

In case of web application where the SQL and the ASP.NET code (in the same data center) is 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 procedure is definitely a plus over inline SQL.

Abstraction

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

Security 

This is best part where stored procedures again scores, you can assign execution rights on user and roles.

 

Maintenance ease

Now because we have centralized our stored procedures. Any issues like  fixing defects , changed can be easily done in the stored procedure and it will be reflected across installed clients. At least we do not need to compile and deploy DLL’s and EXE.

Centralized Tuning

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

Cursors, temp tables complications

Simple TSQL’s are ok. But what if you have bunch of statements with IF, ELSE, Cursors etc. For those kind of scenarios again stored procedures are very handy.

References

http://msdn.microsoft.com/en-us/library/ee343986.aspx

http://msdn.microsoft.com/en-us/library/ms973918.aspx

You can also watch the below video where i have demonstrated by experiments if you still do not trust this article : -

<OBJECT type="application/x-shockwave-flash" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=3,0,0,0" WIDTH="560" HEIGHT="315" data="http://www.youtube.com/v/hISdcezP7ik?version=3&hl=en_GB"></OBJECT>

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

License

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

About the Author

Shivprasad koirala
Architect http://www.questpond.com
India India

I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small
E-learning company in India. We are very much active in making training videos ,
writing books and corporate trainings. Do visit my site for 
.NET, C# , design pattern , WCF , Silverlight
, LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server  training 
and Interview questions and answers


Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
Generalvery good PinmemberEwert Bergh5-May-14 1:29 
QuestionYou proved nothing... PinpremiumKornfeld Eliyahu Peter2-Apr-14 12:46 
AnswerRe: You proved nothing... PinmvpShivprasad koirala2-Apr-14 15:47 
GeneralRe: You proved nothing... PinpremiumKornfeld Eliyahu Peter2-Apr-14 18:05 
QuestionCursors PinmemberScottGimpel55510-Feb-14 6:00 
QuestionStored procedures are a maintenance problem PinmemberJarmoM19-Nov-13 21:08 
GeneralMy vote of 5 PinmemberOshtri Deka28-Jul-13 21:25 
GeneralMy vote of 5 PinmemberTapasU8-Apr-13 19:13 
QuestionVote 5 Pinmembernarenderkumardhull4-Apr-13 19:09 
GeneralMy vote of 5 Pinmembernarenderkumardhull4-Apr-13 19:09 
GeneralMy vote of 4 PinmemberVijaySai Panchadi28-Feb-13 1:17 
GeneralMy vote of 4 Pinmemberkimberly wind21-Feb-13 17:41 
GeneralMy vote of 5 PinmemberVarun Sareen22-Oct-12 2:26 
QuestionSP are outdated PinmemberThornik11-Oct-12 1:45 
AnswerRe: SP are outdated Pinmembereghetto11-Oct-12 21:14 
GeneralStored Procedures can Increase performance Pinmembert4teacher8-Oct-12 4:21 
BugSQL Injection PinmemberRichard Deeming8-Oct-12 4:00 
GeneralMy vote of 3 Pinmembermerlin9813-Oct-12 6:27 
SuggestionDo not use the prefix “sp_” in the stored procedure name, link attached for you ref. PinmemberURVISH_SUTHAR2-Oct-12 20:45 
GeneralRe: Do not use the prefix “sp_” in the stored procedure name, link attached for you ref. PinmemberVarun Sareen22-Oct-12 2:28 
GeneralRe: Do not use the prefix “sp_” in the stored procedure name, link attached for you ref. PinmvpShivprasad koirala30-Aug-13 13:38 
QuestionArticle has nothing to do with the real performance issues. Pinmemberziggyfish2-Oct-12 20:29 
GeneralMy vote of 5 PinmemberMihai MOGA8-Aug-12 5:44 
GeneralVery Good PinmemberOmar Gamil7-Aug-12 1:13 
GeneralA comment on "Maintenance ease" Pinmembersapatag6-Aug-12 23:43 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 14 Jul 2012
Article Copyright 2012 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid