Stored procedures DO NOT increase performance
Stored procedures are precompiled so they are faster.
Ok, now walk your talk
What about other flavors?
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
The above statement also says / implies that for inline queries all the above steps are repeated again and again which brings down performance considerably.
Ok, now walk your talk
 |
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.
“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.
What about other flavors?
Many developers think that if you execute SQL in naked way as shown below it will not use SQL cache.
SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='"
+ UserName +
"' and Password='"
+ Password +
"'", objConnection);
If you write with parameters as shown below it will use.
SqlCommand objCommand = new SqlCommand("Select * from Users where UserName=@UserName and Password=@Password", objConnection);
objCommand.Parameters.Add(new SqlParameter("UserName", UserName));
objCommand.Parameters.Add(new SqlParameter("Password", Password));
objCommand.CommandType = CommandType.Text;
So as said previously all SQL go through the same caching mechanism and plan creation mechanism. So the flavors of SQL do not matter at all. If you want to test you can repeat the above experiments with the same, we are confident you will see cache hit.
Hmm, What about network traffic?
Courtesy: - 300 movie
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_logiIf 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.
No comments:
Post a Comment