Query Design performance issues #01 – All-in-one query

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

When looking into performance issues on support cases there is one pattern called ALL-IN-ONE QUERY that we always sees that cause many issues. This pattern is very common and logically looks correct but will cause huge performance degradation.

 

The idea behind this pattern is that you want a query that filter by the parameter or ignore the filter if send null or zero. And as said logically it looks correct

  • (CustomerID = @CustomerID OR @CustomerID = 0)

Or some other variations

  • (CustomerID = @CustomerID OR @CustomerID IS NULL)
  • CustomerID = CASE WHEN ISNULL(@CustomerID, 0) = 0 THEN CustomerID ELSE @CustomerID END

 

Find below a sample. For this test I want to filter by Customer ID or by Last Name.

  • If I send @CustomerID = XXX I want to filter specific customer ID filter
  • If I send @CustomerID = 0 I want the query to ignore the customer ID filter
  • if I send @LastName I want to filter specific last name
  • if send NULL to @LastName filter, want to ignore it
  • I can also ignore both to list all users

 

 

DROP PROCEDURE IF EXISTS spTEST GO CREATE PROCEDURE spTEST ( @CustomerID int = 0 ,@LastName varchar(50) = NULL ) AS SELECT * FROM [SalesLT].[Customer] WHERE (CustomerID = @CustomerID OR @CustomerID = 0) AND (LastName = @LastName OR @LastName IS NULL) GO EXEC spTEST @CustomerID = 10, @LastName = NULL EXEC spTEST @CustomerID = 0, @LastName = 'Gates' EXEC spTEST @CustomerID = 0, @LastName = NULL

 

 

 

 

 

 

What will happen is that the query works, usually on dev databases with small datasets will run fine, but when you go to production with huge databases you going to notice a huge slowdown

 

FonsecaSergio_0-1596471251747.png

 

Does not matter the parameter you sent it will always scan the index. Even that you have a covering index it will completely ignore it.

 

This is not a defect this is an expected behavior when SQL is building query plan

 

2020-08-04 11_52_59-Clipboard.png

 

 

Solution 1 - OPTION RECOMPILE

One workaround, if query is not executed very often, is to use OPTION (RECOMPILE) at end. This might not be a good option if running very frequently because it will increase the CPU usage and can cause compilation queue waits because for each new execution SQL will have to create a new plan.

 

 

 

 

 

DROP PROCEDURE IF EXISTS spTEST GO CREATE PROCEDURE spTEST ( @CustomerID int = 0 ,@LastName varchar(50) = NULL ) AS SELECT * FROM [SalesLT].[Customer] WHERE (CustomerID = @CustomerID OR @CustomerID = 0) AND (LastName = @LastName OR @LastName IS NULL) OPTION (RECOMPILE) --------- MAY INCREASE CPU / RESOURCE_SEMAPHORE_QUERY_COMPILE GO EXEC spTEST @CustomerID = 10, @LastName = NULL EXEC spTEST @CustomerID = 0, @LastName = 'Gates' EXEC spTEST @CustomerID = 0, @LastName = NULL

 

 

 

 

 

 

It will create the best plan for each set of filters used

FonsecaSergio_0-1596472358893.png

 

 

Solution 2 - IF/ELSEs

One workaround when you have a limited number of options is to create a series of IF and ELSEs. But this can be an issue as number of options increase.

 

 

 

DROP PROCEDURE IF EXISTS spTEST GO CREATE PROCEDURE spTEST ( @CustomerID int = 0 ,@LastName varchar(50) = NULL ) AS IF @CustomerID = 0 AND @LastName IS NULL SELECT * FROM [SalesLT].[Customer] ELSE IF @CustomerID = 0 AND @LastName IS NOT NULL SELECT * FROM [SalesLT].[Customer] WHERE LastName = @LastName ELSE IF @CustomerID != 0 AND @LastName IS NULL SELECT * FROM [SalesLT].[Customer] WHERE CustomerID = @CustomerID ELSE IF @CustomerID != 0 AND @LastName IS NOT NULL SELECT * FROM [SalesLT].[Customer] WHERE (CustomerID = @CustomerID) AND (LastName = @LastName) GO EXEC spTEST @CustomerID = 10, @LastName = NULL EXEC spTEST @CustomerID = 0, @LastName = 'Gates' EXEC spTEST @CustomerID = 0, @LastName = NULL

 

 

Solution 3 - Dynamic query

When you have multiple options the best option is to use Dynamic query + sp_executesql sending the parameters, this way you will have good plan depending on parameters sent to procedure and also will reuse plan.

 

!!! Use sp_executesql parameters. Do not concatenate parameters to the string. This can lead to SQL Injection issues

 

 

 

DROP PROCEDURE IF EXISTS spTEST GO CREATE PROCEDURE spTEST ( @CustomerID int = 0 ,@LastName varchar(50) = NULL ) AS DECLARE @SQL NVARCHAR(MAX) = '' SET @SQL += 'SELECT * FROM [SalesLT].[Customer] ' + CHAR(10) SET @SQL += 'WHERE 1=1' + CHAR(10) IF @CustomerID != 0 SET @SQL += ' AND (CustomerID = @CustomerID)' + CHAR(10) IF @LastName IS NOT NULL SET @SQL += ' AND (LastName = @LastName)' + CHAR(10) EXEC sp_executesql @SQL ,N'@CustomerID int, @LastName varchar(50)' ,@CustomerID = @CustomerID ,@LastName = @LastName GO EXEC spTEST @CustomerID = 10, @LastName = NULL EXEC spTEST @CustomerID = 0, @LastName = 'Gates' EXEC spTEST @CustomerID = 0, @LastName = NULL

 

 

 

 

I hope this help you build better queries

 

REF: https://deep.data.blog/2008/12/19/t-sql-anti-pattern-of-the-day-all-in-one-queries/

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

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