T-SQL Where子句Case语句优化(StoredProc的可选参数)怎么处理?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (22)

我一直在争斗这一段时间。我有一个存储过程,需要3个参数用于过滤。如果传入特定值,我想对其进行过滤。如果传入-1,给我所有。

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

第一种方式:

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说它强制进行表扫描。我没有证实这一点,但似乎在某些情况下运行速度较慢。

该视图选择的主表有大约150万条记录,并且该视图继续加入约15个其他表以收集大量其他信息。

这两种方法都很慢......让我从2-40秒的瞬间到任何地方,这在我的情况下是完全不可接受的。

有没有更好的方法,不涉及到每个具体的vs -1的单独案例?

提问于
用户回答回答于

我在某个地方读到第二种方法会导致短路,如果这是真的,那么从来没有评估第二种方法。我的DBA说它强制进行表扫描。

你读错了; 它不会短路。你的DBA是对的; 它在查询优化器中运行得并不顺利,可能会强制进行表扫描。

第一个选择就像它得到的一样好。可以使用动态SQL或长存储过程来改进事物,并且每个过滤器列都有可能的组合,因此可以获得独立的查询计划。也可以尝试使用“WITH RECOMPILE”选项,但我认为它不会有所帮助。

用户回答回答于

如果你正在运行SQL Server 2005或更高版本,则可以使用IF使用适当的WHERE来创建多个版本的查询,以便可以使用索引。每个查询计划将被放置在查询缓存中。

另外,这里是关于这个话题的非常全面的文章:

Erlang Sommarskog在T-SQL中的动态搜索条件

它涵盖了尝试使用多个可选搜索条件编写查询的所有问题和方法

这里是目录:

  介绍
      案例研究:搜索订单
      Northgale数据库
   动态SQL
      介绍
      使用sp_executesql
      使用CLR
      使用EXEC()
      当缓存不是真的想要什么时
   静态SQL
      介绍
      x = @x或@x IS NULL
      使用IF语句
      Umachandar的一揽子技巧
      使用临时表
      x = @x AND @x不是NULL
      处理复杂的条件
   混合解决方案 - 同时使用静态和动态SQL
      使用视图
      使用内联表函数
   结论
   反馈和致谢
   修订记录

扫码关注云+社区