使用CHARINDEX而不是IN子句来加速SSRS报表筛选

Question

在某些情况下，我们在SSRS报告的过滤器中使用In子句。它们中的许多由于在IN子句中使用数百个项而导致性能问题。如:

WHERE TableA.School IN (@School)

有时候，多值参数确实很难处理，您可能需要在RDL中执行=Join(Mypara.Value，"，")，并编写SQL函数将它们转换为一组SQL数据，以便能够为SQL SP提供数据(特别是一些旧版本的SSRS)。

供参考:用于将逗号分隔符字符串分隔成记录集的函数:

CREATE function [dbo].[fnSpark_BreakUpList] (
    @List VARCHAR(MAX)
)
RETURNS @csvlist TABLE (Item VARCHAR(MAX))
AS
BEGIN
    DECLARE @Item VARCHAR(MAX)

    -- Loop through each item in the comma delimited list
    WHILE (LEN(@List) > 0)
    BEGIN
        IF CHARINDEX(',',@list) > 0
        BEGIN
            SET @Item = SUBSTRING(@List,1,(CHARINDEX(',', @List)-1))
            SET @List = SUBSTRING(@List,(CHARINDEX(',', @List) + DATALENGTH(',')),DATALENGTH(@List))
        END
        ELSE
        BEGIN
            SET @Item = @List
            SET @List = NULL
        END
        -- Insert each  item into the csvlist table
        INSERT into @csvlist (Item) VALUES (@Item)
    END
    RETURN
END
GO 

我将很快发布答案，展示如何通过使用CHARINDEX来提高性能。(所以你不需要像上面那样....)

Answer 1

如果您实际上不想从LONG分隔的字符串中检索条目，而只想对其进行过滤，那么CHARINDEX是更好的方法。

不使用IN子句，你可以直接使用:

WHERE CHARINDEX(','+TableA.School+',',','​+@School+',') > 0 

注意:1. 我在目标字符串'TableA '的末尾加了一个逗号。以避免如果一个大字符串包含与过滤项相同的子字符串的情况。(比如我们有一所学校叫'AB'，另一所学校叫'ABC'，我们不希望'ABC'在我们定位'AB'时被选中....)

我在资源字符串'@School'的末尾添加了一个额外的逗号，以确保当我们定位它们时，单个项目/最后一个项目(它们将不以逗号结束)将被拾取。

我在目标字符串'TableA '的开头添加了一个额外的逗号。以避免如果一个大字符串包含与过滤项相同的子字符串的情况。(比如我们有一所学校叫'AB'，另一所学校叫'CAB'，我们不希望'CAB'在我们瞄准'AB'时被选中....)

的例子:

我正在使用:

WHERE
CHARINDEX(','+CAST(DENTIST4.wStudentYear AS VARCHAR(10))+',',','+@StudentYear+',') > 0 

替换:

WHERE
DENTIST4.wStudentYear IN (@StudentYear) 

对于我正在做的一份报告，这使得4000多个页面的渲染从大约10分钟改善到大型数据库(11g)的1分钟以下。

重要的笔记:请确保传递到数据集的筛选报告参数使用JOIN子句。

=Join(Parameters!MyParameter.Value,",")​

希望对您有所帮助....