tSQL Stored Procedure with CTE; CASE statement in WHERE slowing run

I have a user-defined Stored Procedure
When calling without passing explicit values, I want to just pass all Locations (nvarchar(50)), which is the primary key'd field of a table: Monitor_Locations (with ~850 entries)

One part of the SP is defined as follows (clipped).

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
    Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
    INSERT INTO @loclist(Location)
        SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
    INSERT INTO @loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
            tvf_splitstring(@Locations) as ss ON 
                ML.Location=ss.Item OR 
                ML.Location like ss.Item+'[_]%'
        ORDER BY ML.Location;
With Deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D 
    WHERE 
        D.Stop is not NULL
)

...do a bunch of other stuff...

in order to improve the speed of the stored procedure when a restricted list of sites is sent into the SP, I wanted to replacing the WHERE clause with

WHERE 
    CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
    END=1

but where the SP once took 6-8 seconds to execute, now it takes 2.5 mins (for calling without a restrictive list). I thought it would take roughly the same amount of time each way for the full list, as the secon

Two big performance problems:

• Your CSV splitter function is a major performance killer. Swap it out for Jeff Moden's DelimitedSplit8k function. You can read all about it here. Or better yet, swap it out for the CLR function or a table-valued parameter if you are on 2008+. Check out Aaron Bertrand's performance tests for the various CSV splitter functions. CLR is the winner overall.

• The table variable can be a big performance killer even if it only has 1 row. Switch it to a temporary table and add a clustered index to it.

In your now provided execution plans, the function shows a 0% cost, but that is not the case. The function cost is higher, but you won't see the actual cost in the execution plan unless it's an inline table valued function.


Unfortunately, although I once had a runtime of 1 sec, by changing the
SP and then back again, it takes 6 seconds again.

That smells of parameter sniffing.

StackExchange Database Administrators Q#148074, answer score: 4