优化SQL Server WHERE子句中的CASE WHEN语句

Question

优化SQL Server WHERE子句中的CASE WHEN语句

3

我正在重写我的SQL以减少执行成本，想知道是否有一种高效的方法来编写以下用于WHERE条件中的CASE WHEN语句：

SELECT l.*,tg.*
FROM  RefTable tg, 
      InputTbl l
WHERE tg.areascheme = l.areascheme
  AND tg.countrycode = l.strareabriefnamel1  
  AND ( CASE WHEN l.strareabriefnamel2 IS NULL THEN '' ELSE tg.areacode END ) = COALESCE( l.strareabriefnamel2,'' )
  AND ( CASE WHEN l.strareabriefnamel3 IS NULL THEN '' ELSE tg.subareaname END ) = COALESCE( l.strareabriefnamel3,'' )
  AND ( CASE WHEN l.strareabriefnamel4 IS NULL THEN '' ELSE tg.postalname END ) = COALESCE( l.strareabriefnamel4,'' )
  option( MAXDOP 0 );

执行计划：

更多细节：

输入表（466K条记录）有四个字段参与连接逻辑，并且有16种可能的（NULL，NOT NULL）组合。

L1,  L2,  L3,  L4
NULL,NULL,NULL,NULL
NULL,NULL,NULL,NOT NULL
NULL,NULL,NOT NULL, NULL
NULL,NULL,NOT NULL,NOT NULL
NULL,NOT NULL,NULL,NULL
NULL,NOT NULL,NULL, NOT NULL
NULL,NOT NULL, NOT NULL,NULL
NULL,NOT NULL,NOT NULL,NOT NULL
NOT NULL,NULL,NULL,NULL
NOT NULL,NULL,NULL,NOT NULL
NOT NULL,NULL,NOT NULL,NULL
NOT NULL,NULL,NOT NULL,NOT NULL
NOT NULL,NOT NULL,NULL,NULL
NOT NULL,NOT NULL,NULL,NOT NULL
NOT NULL,NOT NULL,NOT NULL,NULL
NOT NULL,NOT NULL,NOT NULL,NOT NULL

RefTable( 45000 条记录 ) 将与 InputTable 参与 JOIN 逻辑，根据以上标准生成结果集，产生约 3.51 亿行。

我的输入数据目前只符合两种情况。

InputTable：

NULL,NULL,NULL,NULL - 225776 rows
NOT NULL, NOT NULL, NULL, NULL - 240360 rows

非常感谢您的参与和支持。如果您有任何意见或建议，请不要犹豫，随时提出。谢谢。

- Teja

1

尽管我不喜欢在 WHERE 子句中使用 CASE，但是 IIF() 更糟糕，因为它不是标准 SQL。 - Gordon Linoff

不，如果strareabriefnamel2为空，我实际上是在两侧连接空字符串，但是当strareabriefnamel2不为空时，两侧将有两个不同的非空值... - Teja

这里唯一可能的情况是对两个表进行扫描。请注意，您没有任何预定义的过滤器。但是，是的，where子句对性能非常不利。 - Ivan Starostin

我已经在所有连接列上创建了聚集索引... - Teja

这是一个case表达式，而不是case语句。（返回一个值，而不是在存储过程中执行条件代码。） - jarlh

显示剩余6条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

简单的规则：在FROM子句中永远不要使用逗号，而应该始终使用明确、正确的JOIN语法。

这可能不会改变查询的性能，但这是一种更典型的写法。我相信意图是：

SELECT l.*, tg.*
FROM RefTable tg JOIN
     InputTbl l
     ON tg.areascheme = l.areascheme AND tg.countrycode = l.strareabriefnamel1  
WHERE (l.strareabriefnamel2 IS NULL OR tg.areacode = l.strareabriefnamel2) AND
      (l.strareabriefnamel3 IS NULL OR tg.subareaname  = l.strareabriefnamel3) AND
      (l.strareabriefnamel4 IS NULL OR tg.postalname = l.strareabriefnamel4)
  option( MAXDOP 0 );

优化此查询的起点是索引。我建议使用：RefTable(areascheme, countrycode) 和 InputTbl(areascheme, strareabriefnamel1)。