sql - 优化 SQL Server WHERE 子句条件中的 CASE WHEN 语句

Question

我正在重写我的 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 ); 

执行计划:-

更多详情:-

InputTable(466K 条记录)共有四个字段参与 JOIN 逻辑，共有 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(45k 记录)将与 InputTable 一起参与 JOIN 逻辑，根据上述标准生成结果集，生成大约 3.51 亿行。

我的输入数据目前只满足两种情况。

输入表:-

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

如有任何意见，我们将不胜感激。谢谢。

Answer 1

简单规则:永远不要在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)。