代码之家 › 专栏 › 技术社区 › Zolt

如何使多值参数查询运行更快?

ssrs-2008 optimization sql-server

Zolt · 技术社区 · 6 年前

我有以下查询运行非常慢:

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 和 @EndDate 和 @People 来自SSRS报告中的参数。日期参数只是日期。这个 @人 参数是多值参数。

问题是 @人 包含超过3000个值。所以查询必须使用 IN 条款。当在SSR中运行查询时,这确实会减慢我的查询速度。

我想用exists子句替换 在 但在这种情况下,我似乎无法让它发挥作用。我需要从 @人 变量在 EXISTS 子句并将其连接回第一个表,但我甚至不知道这是否可行。

也许我想用这个错误的方向 存在 在这种情况下。但我仍然需要修复查询,使其运行更快。

有人能帮忙吗?

1 回复 | 直到 6 年前

Larnu 6 年前

ISNULL(a.ID, -1) 将使查询不可分析。你最好用 (a.ID IN (@People) OR a.ID IS NULL) 然而,一个 IN 有这么多的论据不太可能奏效。

我在这里运行内存(家里没有SSR),但如果我记得SSR在多值参数和 在 这个比例不太合适。也许你最好使用 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 Splitter

为了完整性,一个快速编写的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);