Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

Question

I have been working on making this query work more efficiently.

I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored procedure.

I am to the point where the only options I can think of is. :

• Creating 16 different queries for all the different inbound parameter possibilities.
• Creating a dynamic sql query but I don't believe this will be any faster
• Reverting back to string sql, but I don't ever like to do this as these do not perform as quickly as stored procedures.

I am sure others have ran into this issue before. The query performance isn't terrible to begin with at roughly a second or less, but there are instances where it is hit numerous times causing a lag of up to 5 or 6 seconds.

Query below. :

DECLARE @PERSON_ID AS INT
DECLARE @ITEM_ID AS INT
DECLARE @ITEM_VERSION AS INT
DECLARE @ITEM_SUB_NAME AS VARCHAR(250)
DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250)

--DEFAULTS
SET @PERSON_ID = 0
SET @ITEM_ID = 0
SET @ITEM_VERSION = 1
SET @ITEM_SUB_NAME = NULL
SET @ITEM_SUB_SUB_NAME = NULL

    SELECT ID, PERSON_ID, 
           ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
           ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME',
           ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME',
           ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE',
    FROM PERSON_TBL s WITH (NOLOCK)     
    WHERE    ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 )
    AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL ))
    AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 )
    AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL )
    AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL )
    ORDER BY PERSON_ID, ITEM_SUB_NAME

Answer 1

This is what I call the "kitchen sink" stored procedure - you want one procedure that handles all possible combinations of search criteria that a user might enter. It is virtually impossible to make SQL Server derive a single execution plan that will be optimal and efficient for all of these combinations - I don't care what kind of tricks you might think ISNULL can pull that COALESCE or OR can't.

The solutions I usually attempt, in this order, are:

1. Add OPTION (RECOMPILE) to the query. Yes, you pay a compilation cost every time, but you will get the right plan given the parameters that are supplied and their values.

2. Use dynamic SQL. Now you will be able to cache multiple different plans based on the different parameters passed. Combine this with the server-level setting optimize for ad hoc workloads (more info from Kimberly Tripp here and here) so that only versions of the plan that are used more than once are fully cached. Example:

   DECLARE
     @PERSON_ID         INT = 0,
     @ITEM_ID           INT = 0,
     @ITEM_VERSION      INT = 1,
     @ITEM_SUB_NAME     VARCHAR(250),
     @ITEM_SUB_SUB_NAME VARCHAR(250);
   
   DECLARE @sql NVARCHAR(MAX) = N'',
   
   SET @sql = N'SELECT ID, PERSON_ID, 
      ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
      ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
      ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
      ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
   FROM dbo.PERSON_TBL AS s WITH (NOLOCK)     
   WHERE ITEM_VERSION ' 
   + CASE WHEN @ITEM_VERSION <> 1 THEN 
      N' = @ITEM_VERSION' ELSE N' IS NULL' END
   + CASE WHEN @PERSON_ID <> 0 THEN 
      N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
   + CASE WHEN @EMPLOYEE_ID <> 0 THEN
      N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
   + CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
      N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
   + CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
      N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
   ORDER BY PERSON_ID, ITEM_SUB_NAME;';
   
   EXEC sys.sp_executesql @sql,
     N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
       @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
     @PERSON_ID, @ITEM_ID, @ITEM_VERSION, 
     @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;
   

If you find that parameter sniffing is still an issue even when you have individual plans for each combination of parameters provided (due to vastly different cardinality based on the actual values), you can add OPTION (RECOMPILE) inside the dynamic SQL, too - you once again pay the compile cost but it should be better when you have a much simpler where clause.

Some resources about this pattern:

• The "Kitchen Sink" procedure (video)
• #BackToBasics: An Updated Kitchen Sink Example

Paul White has a great post worth reading too:

• Parameter Sniffing, Embedding, and the RECOMPILE Options

As an aside:

• Stop using single quotes for alias delimiters. It makes them look like strings and is deprecated in some scenarios, never mind that delimiters are not even necessary here. (I talk about this issue here.)
• Stop using ambiguous, regional date formats like m/d/yyyy (or is that d/m/yyyy?).
• Always use the schema prefix.
• Be very careful about using NOLOCK as a magic turbo button.
• My thoughts on COALESCE vs. ISNULL.