本文介绍了Where子句导致减速的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞!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 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 designSELECT 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 dutiesSELECT a.*FROM @tbl1 AS a INNER JOIN@tbl2 AS b ON (a.City = b.City)UNION ALLSELECT a.*FROM @tbl1 AS a INNER JOIN@tbl2 AS b ON (a.City IS NULL AND b.City IS NULL); 这篇关于Where子句导致减速的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
05-27 04:48
查看更多