Is there a faster way to run an SQL Where Case

Question

I have the following stored procedure (In MS SQL):

ALTER PROCEDURE [dbo].[proc_GetWorksWithEngineerVisits3]
    @sTextSearch nvarchar(255) = NULL,
    @bCompleteFlag bit = NULL,
    @dExpectedStartDateTime datetime = NULL,
    @dExpectedEndDateTime datetime = NULL,
    @sResponsible_UserIDs nvarchar(255) = NULL,
    @bEnableTextSearchFilter bit = false,
    @bEnableCompleteFlagFilter bit = false,
    @bEnableExpectedDateTimeRangeFilter bit = false,
    @bEnableResponsible_UserIDFilter bit = false
AS
    SELECT *
    FROM dbo.vwWorksWithEngineerVisits
    WHERE
        --TextSearch Filter Start
        (sCustomer LIKE CASE
                           WHEN @bEnableTextSearchFilter = 1
                              THEN '%' + @sTextSearch + '%'
                              ELSE sCustomer
                        END
         OR
         sSite LIKE CASE
                       WHEN @bEnableTextSearchFilter = 1
                          THEN '%' + @sTextSearch + '%'
                          ELSE sSite
                    END
         OR
         sCallID LIKE CASE
                         WHEN @bEnableTextSearchFilter = 1
                            THEN '%' + @sTextSearch + '%'
                            ELSE sCallID
                      END)
    --TextSearch Filter End
    AND

    --Complete Filter Start
    bIsComplete = CASE 
                     WHEN @bEnableCompleteFlagFilter = 1
                        THEN @bCompleteFlag
                        ELSE bIsComplete
                  END
    --Complete Filter End

    AND

    --Expected DateTime Range Filter Start
    dExpectedStartDateTime >= CASE 
                                 WHEN @bEnableExpectedDateTimeRangeFilter = 1
                                    THEN @dExpectedStartDateTime
                                    ELSE dExpectedStartDateTime
                              END

AND

dExpectedEndDateTime <= 
CASE 
    WHEN @bEnableExpectedDateTimeRangeFilter = 1
    THEN @dExpectedEndDateTime
    ELSE dExpectedEndDateTime
END
----Expected DateTime Range Filter End

AND

--Responsible_UserID Filter Start

lResponsible_UserID in (
CASE 
    WHEN @bEnableResponsible_UserIDFilter = 0
    THEN lResponsible_UserID
    ELSE (SELECT Value FROM dbo.CSVToList(@sResponsible_UserIDs) AS CSVToList_1) 
END
)
--Responsible_UserID Filter End

ORDER BY dExpectedEndDateTime

The output is fine, but it is very slow (15 sec for only 5000 rows) Executing dbo.vwWorksWithEngineerVisits directly takes 1sec for the same number. When executing the SP, I am setting all enable flags = 0.

DECLARE @return_value int

EXEC    @return_value = [dbo].[proc_GetWorksWithEngineerVisits3]
        @sTextSearch = NULL,
        @bCompleteFlag = False,
        @dExpectedStartDateTime = N'01/01/1969',
        @dExpectedEndDateTime = N'01/01/2021',
        @sResponsible_UserIDs = NULL,
        @bEnableTextSearchFilter = 0,
        @bEnableCompleteFlagFilter = 0,
        @bEnableExpectedDateTimeRangeFilter = 0,
        @bEnableResponsible_UserIDFilter = 0

SELECT  'Return Value' = @return_value

I want to be able to only filter a column, if the corresponding flag is set. I probably could just check for NULL in the primary parameters and reduce the parameters, but I don't think it changes the problem I am having.

The first 4 Case filters are very basic, and when I comment the remaining last 3 out, the performance/result is instantaneous. As soon as I add one of last 3 back into the mix, things slow down as above. What makes these different is that they do ">=" or "in", rather than just an "=" or "like". The other thing that I noticed is that when I changed the following:

lResponsible_UserID in (
CASE 
    WHEN @bEnableResponsible_UserIDFilter = 0
    THEN lResponsible_UserID
    ELSE (SELECT Value FROM dbo.CSVToList(@sResponsible_UserIDs) AS CSVToList_1) 
END

to

lResponsible_UserID in (
CASE 
    WHEN @bEnableResponsible_UserIDFilter = 0
    THEN lResponsible_UserID
    ELSE lResponsible_UserID
END

This also speed things up to 1 sec. How is this the case that changing the else part of the statement makes any difference whatsoever, when the flag is always 0, so should never run?

I need these filters, and I need them dynamic. There are a mix of operator types (including an IN that targets a function). Is there a way to refactor this stored procedure to have the same result (it does work), but in a much more optional way?

Apologies if I have missed something in my post, and I will edit if this pointed out.

Thanks

Answer 1

That's a big query!

SQL Server runs a compiler against the queries in your sp when you define it. Then it uses that compiled procedure, blithely ignoring any optimizations that might come from your specific parameter values. This page explains:

When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

In your situation, your parameter settings dramatically simplify the search you want. But the compiled sp doesn't know that so it uses an excessively generalized search plan.

Try appending this to the query in your SP (after your ORDER BY clause) to force the generation of a new, hopefully more specific, execution plan.

OPTION (RECOMPILE)

Also, you can tidy up your filter clauses and make them a little less gnarly.

Try this for your text-search cases: Change

    sCustomer LIKE CASE
                   WHEN @bEnableTextSearchFilter = 1
                          THEN '%' + @sTextSearch + '%'
                          ELSE sCustomer
                    END

to

  (@bEnableTextSearchFilter <> 1 OR sCustomer LIKE '%' + @sTextSearch + '%')

This will refrain from saying column LIKE column when your filter is disabled, and may save some time.

You can apply the same principle to the rest of your CASE statements too.

Note: the filter pattern column LIKE '%value%' is inherently slow; it can't use an index range scan on column because the text-matching isn't anchored at the beginning of the pattern. Rather it must scan all the values.