sql - 从表中选择部分或全部记录的有效方法

Question

我遇到查询优化问题。假设有一个表包含所有发票。使用 TVP(表值参数)，我想通过提供 1..n 个 id 来选择几条记录，或者通过提供值为 -1 的单个 id 来返回所有记录。

    DECLARE @InvoiceIdSet AS dbo.TBIGINT;
    INSERT INTO @InvoiceIdSet VALUES (1),(2),(3),(4)
    --INSERT INTO @InvoiceIdSet VALUES (-1)

    SELECT TOP 100
            I.Id ,
            Number ,
            DueDate ,
            IssuedDate ,
            Amount ,
            Test3
    FROM    dbo.Invoices I
    --WHERE   EXISTS ( SELECT NULL
    --                 FROM   @InvoiceIdSet
    --                 WHERE  I.Id = ID
    --                        OR ID = -1 )
    --CROSS APPLY @InvoiceIdSet s WHERE i.Id = s.ID OR s.ID = -1
    JOIN @InvoiceIdSet S ON S.ID = I.Id OR S.ID=-1

无论我使用哪种选择方法，查询的执行效率都非常高，直到我开始使用 OR 运算符，此时它开始花费很长时间才能返回少量记录，但所有记录都会很快返回。

任何指点和建议都将受到高度赞赏。

第一个计划没有 OR，第二个计划有 OR。

更新:在摆弄不同的选项之后，无论参数数量如何，我都得出了该解决方案的性能最快。

首先更改 UserDefinedTableType 以包含主键索引:

CREATE TYPE [dbo].[TBIGINT] AS TABLE(
[ID] [bigint] NOT NULL PRIMARY KEY CLUSTERED
)

选择语句现在看起来像这样:

SELECT TOP 100
    I.Id ,
    Number ,
    DueDate ,
    IssuedDate ,
    Amount ,
    Test3
FROM    dbo.Invoices I
WHERE   I.ID IN ( SELECT    S.ID
              FROM      @InvoiceIdSet S
              WHERE     S.ID <> -1
              UNION ALL
              SELECT    S.ID
              FROM      dbo.Invoices S
              WHERE     EXISTS ( SELECT NULL
                                 FROM   @InvoiceIdSet
                                 WHERE  ID = -1 ) )

计划变得更大，但性能几乎保持不变，在很少(第一个计划)和所有(第二个计划)记录之间。

正如您所看到的，现在的计划是相同的，并且可以在不到一秒的时间内从 100 万行中返回所需的记录。

我很想听听社区对此解决方案的看法。

感谢大家的帮助。

Answer 1

如果添加或S.ID=-1，SQL Server 就知道每行的条件都为真；因此，查询计划将像第二个计划一样使用 Scan 。正如 Martin Smith 在评论中所说，SQL Server 在这种情况下不够聪明。您需要有 2 个查询(如果存在 -1，则执行一个查询；如果仅选择某些行，则执行另一个查询)。这样，SQL Server 可以生成 2 个计划，并且这两个计划对于它们所覆盖的场景都是最佳的。您也可以重新编译(但始终会执行 RECOMPILE，这通常会浪费资源)。或者您可以动态构建查询。动态意味着您只生成 2 个查询，并且它们都将被缓存，因此无需重新编译，但要注意它的编写方式，以免受到 SQL 注入(inject)的攻击。<​​/p>

谢谢