Why Query Cost is So different for same type of Query?

Question

Here is my SQL query in SQL Server 2008.

Three identical queries, with different approaches

Declare @UploadDetailID nvarchar(500) = '62703,62709,67161,67167,74580,76728,76774,76777,89001,116048,602337,52674,626855,626863,626877,626862,626874,626861,626873,626857,626860,626872,636929,636938,             636925,636937,636924,636920,636922,636934,636923,644566,644574,644565,644577,644564,644563,644561,646566,646578,646567,646575,646565,646562,646564,653093,653096,653103';
Begin
    select top 500 * 
    from SignatureDetail 
    where (Case When @UploadDetailID = '' then 1 
                When ','+@UploadDetailID+',' like '%,' + ltrim(UploadDetailID) + ',%' then 1
                Else 0 
           End) = 1

    select top 500 * 
    from SignatureDetail 
    where ',' + @UploadDetailID + ',' like '%,' + ltrim(UploadDetailID) + ',%'

    select top 500 * 
    from SignatureDetail 
    where (Case when ',' + @UploadDetailID + ',' like '%,' + ltrim(UploadDetailID) + ',%' then 1 else 0 End) = 1
End

Here are the query costs:

(1) First query with Case and two When conditions is taking only 4% of query cost

select top 500* from SignatureDetail where 
    (Case when @UploadDetailID='' then 1 
        When ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%' then 1
        else 0 End)=1

(2) Second query without Case condition is taking 48% of the total query cost

select top 500* from SignatureDetail where  ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%'

(3) Third query with Case and one When condition is also taking 48% of the total query costs

 select top 500* from SignatureDetail where  (Case when ','+@UploadDetailID+',' like '%,'+ltrim(UploadDetailID)+',%' then 1 Else 0 End)=1

Why do the second and third query take up so much of the total query costs?

Maybe estimated rows fetched from index... can you paste the plan here: https://www.brentozar.com/pastetheplan/ — S3S, Jul 10 '18 at 13:25
I see in your picture that all query plans are essentially identical. — Gordon Linoff, Jul 10 '18 at 14:17
the middle query seems to select fewer rows, but it is the top query that takes least resources. — Cato, Jul 10 '18 at 15:16

score 0 · Answer 1 · answered Jul 10 '18 at 14:00

You are confusing the query analyzer in a few ways:

You are either selecting ALL rows, or a set of IDs.
You are doing a lookup for string matching, instead of getting the IDs directly.

I recommend you have two queries:

Query 1 - if no IDs, just select from the table.
Query 2 - If you have a list of IDs, make a temp table out of them, so you can JOIN against the other table for an exact match. You'll get better performance that your string matching. Newer versions of SQL Server can even do it automatically for you using STRING_SPLIT

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Why Query Cost is So different for same type of Query?

1 Answers1