时间戳

首页 > 解决方案 > 如何使带有多值参数的查询运行得更快？

sql-server - 如何使带有多值参数的查询运行得更快？

问题描述

我有以下运行非常缓慢的查询：

SELECT 
    DISTINCT a.Role as Role
FROM 
    [Table_A] a 
JOIN 
    [Table_B] b ON (a.Key = b.Key)
WHERE 
    b.Date BETWEEN @StartDate AND @EndDate
    AND ISNULL(a.ID, -1) IN (@People)

变量的值和@StartDate来自SSRS 报告中的参数。日期参数只是日期。该参数是一个多值参数。@EndDate@People@People

问题是@People包含超过 3000 个值。所以查询必须使用该IN子句来完成所有操作。在 SSRS 中运行它时，这真的会减慢我的查询速度。

我想使用 exists 子句来替换该IN子句，但我似乎无法让它在这种情况下工作。我需要以某种方式从子句中的@People变量中选择值EXISTS并将其连接回第一个表，但我什至不知道这是否可能。

也许我在尝试EXISTS在这种情况下使用时走错了方向。但我仍然需要修复查询，以便它运行得更快。

有人能帮忙吗？

标签： sql-serveroptimizationssrs-2008

解决方案

ISNULL(a.ID, -1)将使查询成为非 SARGable。最好使用(a.ID IN (@People) OR a.ID IS NULL)，但是，IN具有这么多参数的 an 不太可能运行良好。

我在这里在内存上运行（我家里没有 SSRS）但是如果我记得 SSRS 用多值参数做了一些“魔术”并且IN不能很好地扩展。也许您最好尝试使用一个EXISTS和一个分离器（例如DelimitedSplit8k）。此特定示例依赖于@People少于 8000 个字符。

SELECT DISTINCT a.Role
FROM [Table_A] a 
     JOIN [Table_B] b ON a.Key = b.Key
WHERE b.Date BETWEEN @StartDate AND @EndDate
  AND (EXISTS (SELECT 1
               FROM dbo.DelimitedSplit8K(@People,',') DS
               WHERE DS.Item = a.ID)
   OR  a.ID IS NULL);

然而，考虑到序数位置在这里并不重要，那么其他拆分器是可用的。例如XML 拆分器。

为了完整起见，一个快速编写的 XML 拆分器函数：

CREATE FUNCTION dbo.XMLSplitter (@DelimitedString varchar(MAX))
RETURNS TABLE AS RETURN

    SELECT n.d.value('.','varchar(MAX)') AS Item
    FROM (VALUES(CONVERT(xml,'<d>'+ REPLACE(@DelimitedString,',','</d><d>') + '</d>'))) V(X)
         CROSS APPLY V.X.nodes('d') n(d);

GO

添加了一个没有函数的完整示例：

SELECT DISTINCT a.Role
FROM [Table_A] a 
        JOIN [Table_B] b ON a.Key = b.Key
WHERE b.Date BETWEEN @StartDate AND @EndDate
    AND (EXISTS (SELECT 1
                 FROM (VALUES(CONVERT(xml,'<d>'+ REPLACE(@DelimitedString,',','</d><d>') + '</d>'))) V(X)
                       CROSS APPLY V.X.nodes('d') n(d)
                 WHERE n.d.value('.','varchar(MAX)') = a.ID)
    OR  a.ID IS NULL);

推荐阅读