在SQL Server WHERE子句条件中优化CASE WHEN语句 [英] Optimizing CASE WHEN statement in SQL Server WHERE clause condition

问题描述

我正在重写自己的sql以降低执行成本，并想知道是否存在一种有效的方法来编写以下WHERE条件下使用的CASE 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:

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)组合.

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

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

将与InputTable一起参与JOIN逻辑的

RefTable(45k条记录)将基于上述标准生成结果集，并产生约3.51亿行.

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.

我的输入数据目前仅满足两种情况.

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.

推荐答案

简单规则:从不在FROM子句中使用逗号. 始终使用明确的，正确的JOIN语法.

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

这可能不会改变查询的性能，但这是一种更为典型的编写方式.我很确定目的是:

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

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

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

这篇关于在SQL Server WHERE子句条件中优化CASE WHEN语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！