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

Microsoft

Aug 04, 2020

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

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.

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

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

*Update from Product Group

https://twitter.com/JoeSackMSFT/status/1291374994149302275

Yes indeed! This pattern is very common and have had lots of discussions across the team on addressing it. Not automatically solving this specific flavor of PSP in vNext, but hoping vNext +1.
— Joe Sack (@JoeSackMSFT) August 6, 2020