Good Article Regarding Stored Procedures Performance

Stored Procedures DO NOT increase performance

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.

 Collapse | Copy Code

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”.

 Collapse | Copy Code

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.

 Collapse | Copy Code

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).

 Collapse | Copy Code

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 (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.

 Collapse | Copy Code

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.

Extracted From - http://www.codeproject.com/Articles/414272/Stored-Procedures-DO-NOT-increase-performance