Stored Procedure Performance Optimisation Tips- Part 1

SQL Server gives us several ways to write a query and it’s up to us, how are we using them effectively. The essential thing is “always check execution plan” if you are writing any code.

Sometimes we write a stored procedure smartly which works perfectly as far as functional outcome is concerned but may impact performance adversely.

For example, I have a requirement to provide all the records from person table on the basis of “rowguid” column if @ShowRowID is 0, and if it is 1 then, extract the records on the basis of @BusinessEntityID. Indexing strategy on the table is not so good, as table has only one clustered index on BusinessEntityID, so when query will extract the data on the basis of BusinessEntityID, I would expect to see a clustered index seek else a scan. Let’s come back to development, I tried to cover both the conditions in one query to fulfill functional requirements as below:

USE AdventureWorks2012
GO
CREATE PROC dbo.GetPersonDetails_WithCaseStatement
    (
      @rowguid UNIQUEIDENTIFIER ,
      @ShowRowID INT ,
      @BusinessEntityID INT
    )
AS
    BEGIN
        SELECT  PersonType,NameStyle,EmailPromotion
        FROM    Person.Person
        WHERE   BusinessEntityID = CASE WHEN @ShowRowID = 0
                                        THEN BusinessEntityID
                                        ELSE @BusinessEntityID
                                   END
                AND rowguid = CASE WHEN @ShowRowID = 1 THEN rowguid
                                   ELSE @rowguid
                              END 
    END

The query is looking quite good and covering all the functional requirements as far as output is concerned.

I executed the query with following parameters:

EXEC dbo.GetPersonDetails_WithCaseStatement @ShowRowID = 0,
    @rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2', @BusinessEntityID = 1

With @ShowRowID=0, query will provide the result on the basis of rowguid column so we would expect a clustered index scan.

This looks exactly same as we anticipated. Let’s run the stored procedure with @ShowRowID=1 which should extract the resultset on the basis of our clustered index key so I would expect a clustered seek, nice and simple!

 EXEC dbo.GetPersonDetails_WithCaseStatement @ShowRowID = 1,
@rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2', @BusinessEntityID = 1

let’s review the plan: oh it is exactly same and still scanning the whole index for one row!

Look at the cost and estimated number of rows:

This is because we have written both the conditions together and SQL is generating a plan with combined filter.

How can we simplify it:

We can write this stored procedure in IF/ELSE as two separate queries so that SQL Server covers both the queries separately in execution plan.

USE AdventureWorks2012 
GO
CREATE PROC dbo.GetPersonDetails_WithIFELSE
    (
      @rowguid UNIQUEIDENTIFIER ,
      @ShowRowID INT ,
      @BusinessEntityID INT
    )
AS
    BEGIN
        IF @ShowRowID = 0
            SELECT  PersonType ,
                    NameStyle ,
                    EmailPromotion
            FROM    Person.Person
            WHERE   rowguid = @rowguid
        ELSE IF @ShowRowID = 1
                SELECT  PersonType ,
                        NameStyle ,
                        EmailPromotion
                FROM    Person.Person
                WHERE   BusinessEntityID = @BusinessEntityID
    END

Let’s execute the stored procedure for @ShowRowID=0, we would expect a clustered index scan.

EXEC dbo.GetPersonDetails_WithIFELSE @ShowRowID = 0,
@rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2', @BusinessEntityID = 1

Great, worked as expected. Now let’s test the stored procedure on @ShowRowID=1.

EXEC dbo.GetPersonDetails_WithIFELSE @ShowRowID = 1,
@rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2', @BusinessEntityID = 1

We can see, now it seeking the index if we are extracting the records on the basis of BusinessEntityID column. Let’s check the cost of this improved plan:

We can see, subtree cost has come down from 2.8 to .003, which is massive improvement. If you look at the compiled plan of the optimised stored procedure, you will see both the statements are covered separately in the plan :

This example illustrates how the simple tricks/changes can impact the performance of your query.

Advertisement