1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

WHERE IN performs much better with Table Type than with hardcoded values in SQL Server

Discussão em 'Outras Linguagens' iniciado por Stack, Janeiro 13, 2021 às 16:13.

  1. Stack

    Stack Membro Participativo

    I have 2 queries that are essentially the same (at least if I didn't miss something).

    DECLARE @siloIds SiloIdsTableType
    INSERT INTO @siloIds VALUES
    (1),(2),(3)

    -- Query 1
    SELECT *
    FROM [Transaction]
    WHERE
    SiloId IN (1,2,3)
    AND Time > '2000-02-01'

    -- Query 2
    SELECT *
    FROM [Transaction]
    WHERE
    SiloId IN (select SiloId from @siloIds)
    AND Time > '2000-02-01'


    I was thinking that one cannot beat constants declared in the query itself, but apparently the first query is few times slower than the second one. It seems that SQL server is not smart enough to provide a good plan for the hardcoded values, or am I missing something here?

    It seems that one shall not use where in with a long list and TVP should be always favored

    P.S. I use thousand values instead of 1,2,3 in my query

    P.P.S. I have a non-clustered index on SiloId ASC, Time ASC, but it seems that the first query is not using it favoring clustered index scan for some reason.

    P.P.P.S. Execution plan shares the cost 14% to 86% in favor of the second query

    Execution plan:

    [​IMG]

    Continue reading...

Compartilhe esta Página