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

by | Aug 4, 2020 | Uncategorized | 0 comments

This article is contributed. See the original author and article here.

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/

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment