主页 / dba / 问题 / 326661
Accepted
MichaelD
Asked: 2023-05-02 21:30:10 +0800 CST

是否有选项或提示可以提高“in”子句中具有多个值的查询的性能

  • 772

我们有一张 CustomerNote 表，其中包含 4 列 ID、CustomerID、Note、Date

CustomerID asc 上有一个索引，Date desc

当执行以下查询时

select top 30 
    Date 
from CustomerNote
where CustomerID in (1,5)
order by Date desc

使用了索引，但它仍在为 customerID 的 1 和 5 获取所有 CustomerNotes，然后进行排序/置顶，导致大量 CPU 使用率。 在此处输入图像描述

这是由于“in”子句中的多个值。我知道“in”子句的值永远不会超过 10，因此如果 sql server 迭代 10，每个 customerID 至少获取 30 个，然后进行合并、排序和排序，这将是一个更好的方法。是否有查询提示或选项来实现此目的？

sql-server

3 个回答

  1. 你可能最好手动编写你想要的转换，但本着找到优化器可以为你做的事情的精神，只需最少的更改：

    SELECT TOP (30) 
    CN.[Date] 
FROM dbo.CustomerNote AS CN
WHERE 
    CN.CustomerID IN (SELECT 1 UNION SELECT 5)
ORDER BY 
    CN.[Date] DESC;

    计划

    是的，如果优化器在不更改语法的情况下为您探索这种选项，那就太好了。

    示例表和数据

    CREATE TABLE dbo.CustomerNote
(
    CustomerID integer NOT NULL,
    [Date] datetime NOT NULL
);

    WITH 
    N (n) AS
    (
        SELECT 
            SV.number 
        FROM master.dbo.spt_values AS SV
        WHERE
            SV.[type] = N'P'
            AND SV.number >= 1
    )
INSERT dbo.CustomerNote
    WITH (TABLOCKX)
(
    CustomerID, 
    [Date]
)
SELECT 
    C.n, 
    DATEADD
    (
        MINUTE, 
        -D.n * RAND(CHECKSUM(NEWID())) * 1000,
        GETDATE()
    )
FROM N AS C
CROSS JOIN N AS D
WHERE
    C.n BETWEEN 1 AND 9;

CREATE NONCLUSTERED INDEX 
    [IX dbo.CustomerNote CustomerID, Date-] 
ON dbo.CustomerNote
    (CustomerID, [Date] DESC);

    为 10 个不同的客户加载 2047 个随机日期值：

    SELECT CN.CustomerID, NumRows = COUNT_BIG(*) 
FROM dbo.CustomerNote AS CN
GROUP BY CN.CustomerID
ORDER BY CN.CustomerID;
    客户ID 行数
    1个 2047
    2个 2047
    3个 2047
    4个 2047
    5个 2047
    6个 2047
    7 2047
    8个 2047
    9 2047

    运行解决方案代码会生成一个执行后计划，其中从针对客户 1 的有序索引查找中读取14 行，并从针对客户 5 的类似查找中读取17 行：

    实际计划

    数据库<>小提琴演示

    该计划不会读取每个客户的所有 2047 行。

    另请注意，此解决方案不需要排序运算符。

    该解决方案非常通用，尽管Merge Concatenation需要满足一些排序条件。

    如果要投影不同的列，该列需要成为索引键的一部分（而不是作为包含）以满足排序要求；或者您可以只获取表的一个键，并在找到所需的少量键后作为单独的步骤查找额外的列。

    带有额外列的其他演示：

    • 9
  2. Best Answer

    VALUES加入表值参数、临时表或子句似乎会更好。这意味着每个CustomerID将在关联中单独查询APPLY然后排序。

    select top (30) 
  cn.Date 
from (values
    (1),
    (5)
) v(CustomerID)
cross apply (
    select top (30) cn.*
    from CustomerNote cn
    where v.CustomerID = cn.CustomerID
    order by
      cn.Date desc
) cn
order by
  cn.Date desc;
    • 7

  3. 和总是最后应用top。order by您不能期望优化器根据子句对行进行预过滤order by。

    但是您可以通过使用类似的方法来减少排序记录的数量

    select top 30 
    Date 
from CustomerNote
where CustomerID in (1,5) and Date>datadd(day, -60, getdate())
order by Date desc

    因此，假设您在过去 60 天内有 30 多条记录，您将获取这些记录，对它们进行排序，并丢弃多余的。实际天数，您将不得不根据您的数据进行猜测。

    • -4

相关问题