0

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
  • 2
    What version of SQL Server? – Martin Smith Sep 19 '14 at 21:46
9

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.

Kevin Kline and I have a video about this here:

Paul White has a great post worth reading too:

As an aside:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.