T-SQL Where子句大小写语句优化(StoredProc的可选参数) [英] T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

问题描述

我已经和这个战斗了一段时间了.我有一个存储的proc，它接受3个用于过滤的参数.如果传入一个特定的值，我想对此进行过滤.如果-1被传递，给我全部.

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all.

我已经尝试了以下两种方法:

I've tried it the following two ways:

第一种方式:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
parm1 = CASE WHEN @PARM1= -1 THEN parm1  ELSE @PARM1 END  
AND parm2 = CASE WHEN @PARM2 = -1 THEN parm2  ELSE @PARM2 END  
AND parm3 = CASE WHEN @PARM3 = -1 THEN parm3  ELSE @PARM3 END

第二种方式:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
(@PARM1 = -1 OR parm1 = @PARM1)  
AND (@PARM2 = -1 OR parm2 = @PARM2)  
AND (@PARM3 = -1 OR parm3 = @PARM3)  

我在某处读到，第二种方法会短路，如果为真，则从不评估第二部分.我的DBA表示会强制进行表扫描.我尚未验证这一点，但是在某些情况下，它的运行速度似乎会变慢.

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan. I have not verified this, but it seems to run slower on some cases.

此视图从中选择的主表大约有150万条记录，该视图继续加入约15个其他表以收集一堆其他信息.

The main table that this view selects from has somewhere around 1.5 million records, and the view proceeds to join on about 15 other tables to gather a bunch of other information.

这两种方法都很慢...将我从瞬间带到2-40秒之间，这在我的情况下是完全不能接受的.

Both of these methods are slow...taking me from instant to anywhere from 2-40 seconds, which in my situation is completely unacceptable.

有没有一种更好的方法，不涉及将它分解为特定vs -1的每个单独案例?

Is there a better way that doesn't involve breaking it down into each separate case of specific vs -1 ?

感谢您的帮助.谢谢.

推荐答案

我在某处读到，第二种方法会短路，如果为真，则从不评估第二部分.我的DBA表示会强制进行表扫描.

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan.

您读错了；它不会短路.您的DBA是正确的；它在查询优化器中不能很好地发挥作用，并且可能会强制进行表扫描.

You read wrong; it will not short circuit. Your DBA is right; it will not play well with the query optimizer and likely force a table scan.

第一种选择是尽其所能.您可以选择的改进方法是动态sql或包含过滤器列的每种可能组合的长存储过程，因此您可以获得独立的查询计划.您也可以尝试使用"WITH RECOMPILE"选项，但我认为这不会帮助您.

The first option is about as good as it gets. Your options to improve things are dynamic sql or a long stored procedure with every possible combination of filter columns so you get independent query plans. You might also try using the "WITH RECOMPILE" option, but I don't think it will help you.

这篇关于T-SQL Where子句大小写语句优化(StoredProc的可选参数)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！