更改WHERE中的大小写?

Question

我想找到一种方法来改变下面的语句在一些更明智的性能:

DECLARE @ID_1 int = -1,     @ID_2 int = 123, @ID_3 int = -1
SELECT ...
FROM...
where tblA.ID1 = case when @ID_1 = -1 THEN tblA.ID1 ELSE @ID_1 end
AND tblB.ID2 = case when @ID_2 = -1 THEN tblB.ID2 ELSE @ID_2 END
AND tblC.ID3 = case when @ID_3 = -1 THEN tblB.ID3 ELSE @ID_3 END
AND ....

这是有效的，但性能有时很糟糕。

谢谢!

Answer 1

不要在列上使用CASE，它不是SARGable。使用AND和OR逻辑:

SELECT ...
FROM ...
WHERE (tblA.ID1 = @ID_1 OR @ID_1 = -1) --I would recommend uses NULL for your "catch-all"
AND (tblB.ID2 = @ID_2 OR @ID_2 = -1) 
AND (tblC.ID3 = @ID_3 OR @ID_3 = -1) 

由于"catch-allness";或者"厨房水槽";在查询中，您可能还希望将RECOMPILE添加到查询的OPTION子句中，以避免重用与参数值无关的缓存计划。

或者，可以使用动态SQL，并且只包含参数值所需的子句。我假设在这里，你的参数中至少有一个必须有-1以外的值(否则会出错)，你是在SQL Server 2017+(因此可以访问CONCAT_WS)。

DECLARE @ID_1 int = -1,
@ID_2 int = 123,
@ID_3 int = -1;
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT ...' + @CRLF +
N'FROM ...' + @CRLF + 
N'WHERE ' + CONCAT_WS(@CRLF + N'  AND ',
CASE @ID_1 WHEN -1 THEN NULL ELSE N'tblA.ID1 = @ID_1' END,
CASE @ID_2 WHEN -1 THEN NULL ELSE N'tblB.ID2 = @ID_2' END,
CASE @ID_3 WHEN -1 THEN NULL ELSE N'tblC.ID3 = @ID_3' END) + N';';
--PRINT @SQL; --Your best friend
EXEC sp_executesql @SQL, N'@ID_1 int, @ID_2 int, @ID_3 int', @ID_1, @ID_2, @ID_3;

这样做的好处是您可以在这里使用缓存计划，并且RDBMS将可能能够根据正在运行的查询做出更明智的选择，但是，它们更难维护(特别是如果您不了解动态SQL如何开始工作)。