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

I am rewriting my sql to reduce the cost of execution and wondering if there is an efficient way to write the below CASE WHEN statements used in WHERE condition:

我正在重写sql以降低执行成本，我想知道在WHERE条件下使用语句时，是否有一种有效的方法来编写下面的CASE:

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 );

Execution Plan :-

执行计划:

More Details :-

更多的细节:

InputTable( 466K records ) has total four fields which participate in JOIN logic and there are total 16 possible ( NULL,NOT NULL )combinations.

InputTable(466K记录)总共有4个字段参与连接逻辑，总共有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 records ) which will participate in the JOIN logic with InputTable is generating the resultset based on the above criteria is producing around 351 million rows.

RefTable(45k记录)将与InputTable一起参与连接逻辑，根据上述标准生成的resultset产生了大约3.51亿行。

My input data is currently meeting only two scenarios.

我的输入数据目前只满足两个场景。

InputTable :-

InputTable:-

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

Any inputs would be appreciated. Thanks.

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

1 个解决方案

#1

Simple rule: Never use commas in the FROM clause. Always use explicit, proper JOIN syntax.

简单规则:绝不要在FROM子句中使用逗号。始终使用显式的、适当的连接语法。

This probably doesn't change the performance of the query, but it is a much more typical way to write it. I'm pretty sure the intention is:

这可能不会改变查询的性能，但它是一种更典型的编写方法。我很确定这样做的目的是:

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 );

The place to start with optimizing this query is with indexes. I would suggest: RefTable(areascheme, countrycode) and InputTbl(areascheme, strareabriefnamel1).

开始优化这个查询的地方是索引。我建议:RefTable(areascheme, countrycode)和InputTbl(areascheme, strareabriefnamel1)。

#1