Search code examples
sql-serverdatabasestored-proceduresdatabase-performancedatabase-indexes

Query is fast with direct comparison, but not with table comparison with same column index

I have a fairly complex query that does a direct comparision with @EventId if provided and fast since it grabs the clustered index row. However, sometimes I have to do a group of these Event IDs, and the second line takes almost 30 seconds to run. I figured it would work the same way with looking up the primary key. Is there a reason why it's so much slower?

DECLARE @EventIds TABLE(Id INT NOT NULL);

    WHERE 
        (@EventId IS NULL OR (ev.Id = @EventId)) AND
        (NOT EXISTS(SELECT 1 FROM @EventIds) OR ev.Id IN (SELECT * FROM @EventIds))
Solution

  • There's no real good reason to have the expression

    NOT EXISTS(SELECT 1 FROM @EventIds) OR ev.Id IN (SELECT * FROM @EventIds)

    The first expression, even if true, doesn't preclude the evaluation of the second expression because SQL Server doesn't shortcut boolean expressions.

    Second, as table variables have been known to cause bad execution plans due to incorrect statistics and row count. Please refer to this essay on the difference between table variables and temporary tables, topics: Cardinality, and No column statistics.

    It might help to add the following query hint at the end of the query:

    OPTION(RECOMPILE);

    Yes this recompiles the plan each time, but if you're getting horrible performance the small additional compile time doesn't matter that much.

    This query hint is also recommended if you have optional filters as you have with @EventId.

    It may also help to have a primary key on Id defined on the @EventIds table variable. This would allow an index seek instead of a table scan.