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.

1

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!

1

Look at the cost and estimated number of rows:

1

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

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

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:

1

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 :

1

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

6 thoughts on “Stored Procedure Performance Optimisation Tips- Part 1

Add yours

  1. Great use of examples. This has really made it very easy to understand and relatable to my day to day work.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: