有没有一种更快的方法来运行SQL Where Case

Question

我有以下存储过程(在MS SQL中(：

ALTER PROCEDURE [dbo].[proc_GetWorksWithEngineerVisits3]
@sTextSearch nvarchar(255) = NULL,
@bCompleteFlag bit = NULL,
@dExpectedStartDateTime datetime = NULL,
@dExpectedEndDateTime datetime = NULL,
@sResponsible_UserIDs nvarchar(255) = NULL,
@bEnableTextSearchFilter bit = false,
@bEnableCompleteFlagFilter bit = false,
@bEnableExpectedDateTimeRangeFilter bit = false,
@bEnableResponsible_UserIDFilter bit = false
AS
SELECT *
FROM dbo.vwWorksWithEngineerVisits
WHERE
--TextSearch Filter Start
(sCustomer LIKE CASE
WHEN @bEnableTextSearchFilter = 1
THEN '%' + @sTextSearch + '%'
ELSE sCustomer
END
OR
sSite LIKE CASE
WHEN @bEnableTextSearchFilter = 1
THEN '%' + @sTextSearch + '%'
ELSE sSite
END
OR
sCallID LIKE CASE
WHEN @bEnableTextSearchFilter = 1
THEN '%' + @sTextSearch + '%'
ELSE sCallID
END)
--TextSearch Filter End
AND
--Complete Filter Start
bIsComplete = CASE 
WHEN @bEnableCompleteFlagFilter = 1
THEN @bCompleteFlag
ELSE bIsComplete
END
--Complete Filter End
AND
--Expected DateTime Range Filter Start
dExpectedStartDateTime >= CASE 
WHEN @bEnableExpectedDateTimeRangeFilter = 1
THEN @dExpectedStartDateTime
ELSE dExpectedStartDateTime
END
AND
dExpectedEndDateTime <= 
CASE 
WHEN @bEnableExpectedDateTimeRangeFilter = 1
THEN @dExpectedEndDateTime
ELSE dExpectedEndDateTime
END
----Expected DateTime Range Filter End
AND
--Responsible_UserID Filter Start
lResponsible_UserID in (
CASE 
WHEN @bEnableResponsible_UserIDFilter = 0
THEN lResponsible_UserID
ELSE (SELECT Value FROM dbo.CSVToList(@sResponsible_UserIDs) AS CSVToList_1) 
END
)
--Responsible_UserID Filter End
ORDER BY dExpectedEndDateTime

输出很好，但非常慢(只有5000行需要15秒(。对于相同的数字，执行dbo.vwWorksWithEngineerVisits直接需要1秒。执行SP时，我将所有启用标志设置为0。

DECLARE @return_value int
EXEC    @return_value = [dbo].[proc_GetWorksWithEngineerVisits3]
@sTextSearch = NULL,
@bCompleteFlag = False,
@dExpectedStartDateTime = N'01/01/1969',
@dExpectedEndDateTime = N'01/01/2021',
@sResponsible_UserIDs = NULL,
@bEnableTextSearchFilter = 0,
@bEnableCompleteFlagFilter = 0,
@bEnableExpectedDateTimeRangeFilter = 0,
@bEnableResponsible_UserIDFilter = 0
SELECT  'Return Value' = @return_value

如果设置了相应的标志，我希望只能过滤一列。我可能只需要检查主参数中的NULL并减少参数，但我认为这不会改变我的问题。

前4个Case过滤器非常基本，当我评论最后3个时，性能/结果是即时的。只要我把最后三个中的一个重新加入到混合物中，事情就会像上面一样慢下来。这些不同之处在于>"或"；在"；，而不仅仅是一个"="或"；如"；。我注意到的另一件事是，当我更改以下内容时：

lResponsible_UserID in (
CASE 
WHEN @bEnableResponsible_UserIDFilter = 0
THEN lResponsible_UserID
ELSE (SELECT Value FROM dbo.CSVToList(@sResponsible_UserIDs) AS CSVToList_1) 
END

至

lResponsible_UserID in (
CASE 
WHEN @bEnableResponsible_UserIDFilter = 0
THEN lResponsible_UserID
ELSE lResponsible_UserID
END

这也将事情加速到1秒。当标志总是0，所以永远不应该运行时，更改语句的其他部分会有什么不同，这是怎么回事？

我需要这些过滤器，我需要它们是动态的。有多种运算符类型(包括以函数为目标的IN(。有没有一种方法可以重构这个存储过程以获得相同的结果(它确实有效(，但以一种更可选的方式？

如果我在帖子中遗漏了什么，我深表歉意，如果有人指出，我会编辑。

感谢

Answer 1

这是一个很大的查询！

SQL Server在您定义sp时会针对sp中的查询运行编译器。然后它会使用编译后的过程，轻松地忽略可能来自特定参数值的任何优化。本页解释：

SQL Server执行过程时，过程在编译时使用的任何参数值都将作为生成查询计划的一部分。如果这些值代表随后调用过程的典型值，那么过程每次编译和执行时都会从查询计划中受益。如果过程中的参数值经常是非典型的，则强制重新编译过程和基于不同参数值的新计划可以提高性能。

在您的情况下，您的参数设置极大地简化了您想要的搜索。但编译后的sp并不知道这一点，所以它使用了一个过于笼统的搜索计划。

试着将其附加到SP中的查询中(在ORDERBY子句之后(，以强制生成一个新的、希望更具体的执行计划。

OPTION (RECOMPILE)

此外，您可以整理您的筛选子句，使它们不那么粗糙。

尝试一下你的文本搜索案例：更改

sCustomer LIKE CASE
WHEN @bEnableTextSearchFilter = 1
THEN '%' + @sTextSearch + '%'
ELSE sCustomer
END

至

(@bEnableTextSearchFilter <> 1 OR sCustomer LIKE '%' + @sTextSearch + '%')

当您的过滤器被禁用时，这将避免说column LIKE column，并可能节省一些时间。

您也可以将相同的原则应用于CASE语句的其余部分。

注意：滤波器模式column LIKE '%value%'固有地慢；它不能在CCD_ 3上使用索引范围扫描，因为文本匹配没有锚定在模式的开头。相反，它必须扫描所有值。