Microsoft Sql Server stored procedures – don’t trust in sql server optimization

 

 

It is an understood thing that Microsoft SQL Server (as many dbms) makes optimizations in the background for the user to try make queries faster. Well, it’s a nice and good thing and of course if we spend less time for optimization we got more time for beer. We could be happy at this point, but let see what happens when using stored procedures.

Sql server generates a so-called ‘execution plans’ and executes queries by them. It stores these plans and decides by them which indexes will be used at the next running.

The problem starts at stored procedures that contains optional parameters. In that time we don’t need to give all of the parameters. In that case we should need different query plans with different given parameters.

When sql server executes a stored procedure it makes a single query plan for each stored procedures based on the given parameters at the first run. The server uses this plan for all runs and generates new query plan only in this cases:

executing stored procedure with WITH RECOMPILE option

making schema changes on any objects depending on stored procedures

executing sp_recompile on a table referenced by the stored procedure

on cross database operations restoring any dependent object

making a given number of server activities

…

Let’s see a simple example for query plan caching (using northwind sample database):

 

create procedure [dbo].[GetCustomersStatic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    set @companyName = '%' + @companyName + '%'  
    select CustomerID, CompanyName
    from Customers
    where
        (@customerID is null or CustomerID = @customerID)
        and (@companyName is null or CompanyName like @companyName)
end

go

exec dbo.GetCustomersStatic @companyName = N'a'
exec dbo.GetCustomersStatic @customerID = N'BONAP'

Let’s see the actual execution plans:

As we can see sql server uses the same query plan for both executing: it uses the index for company name when user searches by ID.

 

The best solution that I found and has well expressed had written by Erland Sommarskog (Sql server MVP). He says we can solve it this ways:

Static sql. Use WITH RECOMPILE option when executing stored procedure. At this time we make sql server to recompile the stored procedure and it must generate a brand new query plan. In that case the problem is given when we call the procedure many times. Recompile needs processor time and many recompiles causes performance problems.

-- Static sql with recompile
exec dbo.GetCustomersStatic @companyName = N'a' with recompile
exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile

2.: Dynamic sql. Create a query string by hand dynamically and run it with sp_executesl:

CREATE procedure [dbo].[GetCustomersDynamic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    declare @query nvarchar(max)
            , @paramTypes nvarchar(100)

    set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
    set @query = N'
    select CustomerID, CompanyName
    from Customers
    where 1 = 1'
    
    if @customerID is not null
        set @query = @query + N'
        and CustomerID = @customerID'
        
    if @companyName is not null
    begin
        set @companyName = '%' + @companyName + '%'
        
        set @query = @query + N'
        and CompanyName like @companyName'
    end
    
    exec sp_executesql @query, @paramTypes, @customerID, @companyName
end

go

-- Dynamic sql
exec dbo.GetCustomersDynamic @customerID = N'BONAP'
exec dbo.GetCustomersDynamic @companyName = N'a'

The complete source code is:

USE [Northwind]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersStatic]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[GetCustomersStatic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    set @companyName = '%' + @companyName + '%'  
    select CustomerID, CompanyName
    from Customers
    where
        (@customerID is null or CustomerID = @customerID)
        and (@companyName is null or CompanyName like @companyName)
end


GO


GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersDynamic]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[GetCustomersDynamic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    declare @query nvarchar(max)
            , @paramTypes nvarchar(100)

    set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
    set @query = N'
    select CustomerID, CompanyName
    from Customers
    where 1 = 1'
    
    if @customerID is not null
        set @query = @query + N'
        and CustomerID = @customerID'
        
    if @companyName is not null
    begin
        set @companyName = '%' + @companyName + '%'
        
        set @query = @query + N'
        and CompanyName like @companyName'
    end
    
    exec sp_executesql @query, @paramTypes, @customerID, @companyName
end
GO


go

exec dbo.GetCustomersStatic @companyName = N'a'
exec dbo.GetCustomersStatic @customerID = N'BONAP'


-- Solutions

-- Static sql with recompile
exec dbo.GetCustomersStatic @companyName = N'a' with recompile
exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile

-- Dynamic sql
exec dbo.GetCustomersDynamic @customerID = N'BONAP'
exec dbo.GetCustomersDynamic @companyName = N'a'

GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:29 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersStatic]
GO


GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:09:46 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersDynamic]
GO

C# object initializers: use them smart!

Object initializers came with .Net framework in 2008. Using object initializers we can easily initialize our objects fields without writing many constructors:

            // Using object initializer
            Person demoP = new Person()
            {
                ID = 0,
                Name = "Happy",
                Age = 1
            };

As we can see, this method is simple. As in the old way, we can write a constructor or set fields by hand:

            Person oldDemoP = new Person();
            oldDemoP.ID = 0;
            oldDemoP.Name = "Happy";
            oldDemoP.Age = 1;

Using object initializers, we are able to save a lot of coding. That's good, isn't it? Less coding means less work and more beer, so we happy, aren't we?

Sorry, but the answer is: Not at every cases. In the real life, we spend our work hours with creating new lines of codes only about 10% and we are debugging our code all day from morning to night. And in real life, things don't work. You have databases and objects with bad design or with good design and bad data. You think every person has Age but after 3 migrates you can find some records that don't have.
Let's take a simple object (Person):

    public class Person
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

Person has ID, Name and Age properties.

In real life scenarios there are many case when you know that, you can get _Always_ the age of the person and at two case in the 1.5 million records you won't get it. In this way you get Exception when try assign a null value to an Age property. The question is: How does the Exception look like? What we can find in the error logs?

Presenting the problem I made a simple example. I simulated my database with a list and use a special person class with object as property types:

    public class PersonDS
    {
        public object ID { get; set; }
        public object Name { get; set; }
        public object Age { get; set; }
    }

I used this type for making an emulated simple sample database:

            List<PersonDS> database = new List<PersonDS>();
 
            database.Add(new PersonDS() { ID = 1, Name = "Peter", Age = 18 });
            database.Add(new PersonDS() { ID = 2, Name = "Liza", Age = null });
            database.Add(new PersonDS() { ID = 3, Name = "Cloe", Age = 16 });

I. In the old way, we can set values one by one. Now, we get error at the assignment:

 // Old way to read datas
            try
            {
                List<Person> oldPersons = new List<Person>();
                foreach (var datarow in database)
                {
                    Person p = new Person();
                    p.ID = (datarow.ID as int?).Value;
                    p.Name = datarow.Name as string;
                    p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"

                    , exc.Message, exc.StackTrace));
            }

II. Using the new way, we get the Exception when initializing the object:

            // The new way to read datas
            try
            {
                List<Person> newPersons =   
                        (from p in database
                         select new Person()
                         {// Throws Exception at object initialize
                             ID = (p.ID as int?).Value,
                             Name = p.Name as string,
                             Age = (p.Age as int?).Value
                         }).ToList();
            }
            catch (Exception exc)
            {
                Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"

                    , exc.Message, exc.StackTrace));
            }
        }

As we can see, we get our exceptions in both cases. But at the old way, we can see that we have a missing Age value at the first look. With object initializers, we see only that we got an error at the initialize. And we have to find out what was the error exactly.
We can find many problems with this example: at the real life, I never used this form to read data from database: p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment

There are many ways to do this properly but the target is showing a simple problem with object initializers: error messages are not enough well detailed. In real life we usually don't have objects with only three properties. And we can get data with many ways: databases, xmls, streams, user input, ... 

The conclusion is: let's type a bit more but make error safe code. When we should bet our month salary for that property and want to say: 'Oh, it must be filled', don't be lazy and handle the possibility when it's not and we can sit down and open a beer.

Morzel

The entire code example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace ObjectInitializersVSOldWays
{
    public class Person
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
 
    public class PersonDS
    {
        public object ID { get; set; }
        public object Name { get; set; }
        public object Age { get; set; }
    }
 
    class Program
    {
        static void Main(string[] args)
        {
            // Using object initializer
            Person demoP = new Person()
            {
                ID = 0,
                Name = "Happy",
                Age = 1
            };
 
            Person oldDemoP = new Person();
            oldDemoP.ID = 0;
            oldDemoP.Name = "Happy";
            oldDemoP.Age = 1;
 
            List<PersonDS> database = new List<PersonDS>();
 
            database.Add(new PersonDS() { ID = 1, Name = "Peter", Age = 18 });
            database.Add(new PersonDS() { ID = 2, Name = "Liza", Age = null });
            database.Add(new PersonDS() { ID = 3, Name = "Cloe", Age = 16 });
 
            // Old way to read datas
            try
            {
                List<Person> oldPersons = new List<Person>();
                foreach (var datarow in database)
                {
                    Person p = new Person();
                    p.ID = (datarow.ID as int?).Value;
                    p.Name = datarow.Name as string;
                    p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                    , exc.Message, exc.StackTrace));
            }
 
            // The new way to read datas
            try
            {
                List<Person> newPersons =   
                        (from p in database
                         select new Person()
                         {   // Throws Exception at object initialize
                             ID = (p.ID as int?).Value,
                             Name = p.Name as string,
                             Age = (p.Age as int?).Value
                         }).ToList();
            }
            catch (Exception exc)
            {
                Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                    , exc.Message, exc.StackTrace));
            }
        }
    }
}
And the output is:
Message:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Nullable`1.get_Value()
   at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 41
StackTrace:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Nullable`1.get_Value()
   at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 41
Message:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Nullable`1.get_Value()
   at ObjectInitializersVSOldWays.Program.<Main>b__4(PersonDS p) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 54
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 52
StackTrace:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Nullable`1.get_Value()
   at ObjectInitializersVSOldWays.Program.<Main>b__4(PersonDS p) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 54
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 52