Where子句导致减速 [英] Where clause is causing a slowdown

问题描述

I have a statement that is being slowed down by a where clause.  What can I do to work around this? 

SELECT AL1 。 *

FROM znrmrd_mft 。 i_ua2_gen2sro_ALL_CLEAN < span style ="color：＃800000"> AL1

INNER JOIN dbo 。 i_ua2_gen2sro_MINSORT AL2

ON AL1 。 POS = AL2 。 POS AND AL1 。 ORIG_MFT = AL2 。 ORIG_MFT

ONAL1.POS=AL2.POS AND AL1.ORIG_MFT=AL2.ORIG_MFT

AND AL1 。 DEST_MFT = AL2 。 DEST_MFT

AND AL1 。 SESN_STRT_DT = AL2 。 SESN_STRT_DT

AND AL1 。 SESN_STOP_DT = AL2 。 SESN_STOP_DT

WHERE （ AL1 。 ITEM_DETAIL = AL2 。 ITEM_DETAIL OR （ AL1 。 ITEM_DETAIL IS NULL AND AL2 。 ITEM_DETAIL IS NULL ））

WHERE (AL1.ITEM_DETAIL=AL2.ITEM_DETAILOR (AL1.ITEM_DETAIL ISNULL AND AL2.ITEM_DETAIL ISNULL))

推荐答案

Hi soldierfc，

Hi soldierfc,

WHERE子句中的逻辑OR通常是罪魁祸首。

The logical OR in the WHERE clause is usually a culprit.

您可以尝试将WHERE子句拆分为UNION ALL两个SELECT语句。

You can try to split the WHERE clause as UNION ALL of two SELECT statements.

-- original design
SELECT a.*
FROM @tbl1 AS a INNER JOIN
	@tbl2 AS b ON (a.City = b.City)
		OR (a.City IS NULL AND b.City IS NULL);

- 职责分离
SELECT a。*
FROM @ tbl1 AS INNER JOIN
@ tbl2 AS b ON（ a.City = b.City ）
UNION ALL
SELECT a。*
FROM @ tbl1 AS INNER JOIN
@ tbl2 AS b ON（ a.City IS NULL且b.City IS NULL ）;

-- separation of duties SELECT a.* FROM @tbl1 AS a INNER JOIN @tbl2 AS b ON (a.City = b.City) UNION ALL SELECT a.* FROM @tbl1 AS a INNER JOIN @tbl2 AS b ON (a.City IS NULL AND b.City IS NULL);

这篇关于Where子句导致减速的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！