0

I have a stored procedure of the form:

CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN

    SELECT  A.Value1,
            B.Value1,
            C.Value1,
            ...
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
END

Even when it gets called with @SearchString set to N'' the query takes a minute and half to execute. If I remove the "potentially expensive query" parts then the query returns the expected data virtually instantaneously.

From what I've read, the above shouldn't evaluate anything in the ELSE part of the outer CASE statement as @SearchString is N'' so why does it act as though it is?

If this is working as expected then how do I code this to force SQL not to execute the "potentially expensive query" parts?

I'm trying to understand why they appear to affect the main query despite my efforts to ensure that they aren't called at all.

1

Inspect the @ComparisonType value first to obtain the desired parameter value, then use that value in the original query.

Doing that will allow SQL Server to run only the query that is needed each time the store procedure is called.

CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN
DECLARE @result SMALLINT;
IF (@SearchString = '')
BEGIN
   SET   @result = 0;
END
ELSE
    BEGIN
      IF (@ComparisonType = 0)
        BEGIN
           SELECT @result =  A   FROM TableC
        END
      IF (@ComparisonType = 1)
        BEGIN
           SELECT  @result =  B   FROM TableC
        END
      IF (@ComparisonType = 6)
        BEGIN
           SELECT  @result =  C   FROM TableC
        END
      IF (@ComparisonType = 10)
        BEGIN
           SELECT  @result =  D   FROM TableC
        END                                        
      ELSE
      BEGIN
         SET   @result = 0;
      END
    END
    SELECT  A.Value1,
            B.Value1,
            C.Value1
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = @result
END
2

If you add OPTION (RECOMPILE) to the very end of the WHERE clause so it looks like this, does the procedure run faster now?

WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
OPTION (RECOMPILE)

If so, then you likely have a parameter sniffing issue which could be causing bad execution plans to be generated with poor cardinality estimates. If you update your post with your execution plan, then we can pinpoint exactly how that's happening.

If that's the case, this is a good article on Parameter Sniffing and OPTION (RECOMPILE).

Note I usually only use OPTION (RECOMPILE) for debugging to quickly confirm a parameter sniffing issue and try to resolve the underlying problem instead. There are use cases for OPTION (RECOMPILE) however it's a trade-off for minor performance hits since the query gets recompiled every time it's executed.

Brent Ozar has an awesome series of blog posts on Parameter Sniffing issues. Too many to individually link to be honest, so here's the root page that links the main important ones: Parameter Sniffing

  • ...you update your post with your Execution Plan, we may be able to suggest a better solution to solving your parameter sniffing issue. Sometimes just updating your Table's statistics more frequently is all that's needed, but it really depends on your situation. – J.D. Dec 4 '20 at 13:07

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.