在SQL Server数据库查询的过程中，通过对SQL语句的优化来提高SQL查询的性能。本文我们主要介绍了使用UNION代替OR提升查询性能的一个实例，首先我们先来看一下优化前的代码：
SQL>settimingon SQL>setautotraceon SQL>selectcount(*)rowcount_lhy 2fromswgl_ddjbxxt 3wheret.fzgs_dm='001085' 4and(t.lrr_dm='e90e3fe4237c4af988477329c7f2059e'orexists 5(selecty.kh_id 6fromkhgl_khywdlxxy 7wherey.kh_id=t.kh_id 8andy.sskhjl_dm='e90e3fe4237c4af988477329c7f2059e')or 9t.kpr_dm='e90e3fe4237c4af988477329c7f2059e') 10andt.xjbz='9999' 11andt.FROMNBGL1='0'; SQL>setline300 SQL>/ ROWCOUNT_LHY ------------ 60 已用时间:00:00:20.53 执行计划 ---------------------------------------------------------- Planhashvalue:1217125969 -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|86|28048(1)|00:05:37| |1|SORTAGGREGATE||1|86||| |*2|FILTER|||||| |*3|TABLEACCESSFULL|SWGL_DDJBXX|5926|497K|28048(1)|00:05:37| |*4|TABLEACCESSBYINDEXROWID|KHGL_KHYWDLXX|1|57|5(0)|00:00:01| |*5|INDEXRANGESCAN|IDX_KHGL_KHYWDLXX_KHID|1||3(0)|00:00:01| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 2-filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e'OR "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e'OREXISTS(SELECT0FROM"KHGL_KHYWDLXX""Y" WHERE"Y"."KH_ID"=:B1AND"Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e'))3-filter("T"."FROMNBGL1"='0'AND"T"."XJBZ"='9999'AND"T"."FZGS_DM"='001085') 4-filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 5-access("Y"."KH_ID"=:B1) 统计信息 ---------------------------------------------------------- 0recursivecalls 0dbblockgets 804560consistentgets 71127physicalreads 0redosize 516bytessentviaSQL*Nettoclient 469bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 1rowsprocessed
用UNION代替OR对其进行优化后的代码如下：
SQL>selectcount(*) 2from(select* 3fromswgl_ddjbxxt 4wheret.lrr_dm='e90e3fe4237c4af988477329c7f2059e' 5andt.fzgs_dm='001085' 6andt.xjbz='9999' 7andt.FROMNBGL1='0' 8union 9select* 10fromswgl_ddjbxxt 11wheret.kpr_dm='e90e3fe4237c4af988477329c7f2059e' 12andt.fzgs_dm='001085' 13andt.xjbz='9999' 14andt.FROMNBGL1='0' 15union 16select* 17fromswgl_ddjbxxt 18whereexists 19(selecty.kh_id 20fromkhgl_khywdlxxy 21wherey.kh_id=t.kh_id 22andy.sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') 23andt.fzgs_dm='001085' 24andt.xjbz='9999' 25andt.FROMNBGL1='0'); COUNT(*) ---------- 60 已用时间:00:00:06.89 执行计划 ---------------------------------------------------------- Planhashvalue:3846872744 ----------------------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ----------------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|||52263(1)|00:10:28| |1|SORTAGGREGATE||1||||| |2|VIEW||5996|||52263(1)|00:10:28| |3|SORTUNIQUE||5996|2238K|6344K|52263(47)|00:10:28| |4|UNION-ALL||||||| |*5|TABLEACCESSFULL|SWGL_DDJBXX|59|19234||28037(1)|00:05:37| |*6|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|10|3260||1209(1)|00:00:15| |*7|INDEXRANGESCAN|IDX_SWGL_DDJBXX_KPRDM|4748|||34(0)|00:00:01| |*8|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|1|326||5(0)|00:00:01| |9|NESTEDLOOPS||5927|2216K||22527(1)|00:04:31| |10|SORTUNIQUE||10165|565K||1916(1)|00:00:23| |11|TABLEACCESSBYINDEXROWID|KHGL_KHYWDLXX|10165|565K||1916(1)|00:00:23| |*12|INDEXRANGESCAN|IDX_KHGL_KHYWDLXX_SSKHJL|10165|||111(0)|00:00:02| |*13|INDEXRANGESCAN|IDX_SWGL_DDJBXX_KHID|2|||2(0)|00:00:01| ----------------------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 5-filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e'AND"T"."FROMNBGL1"='0'AND"T"."XJBZ"='9999' AND"T"."FZGS_DM"='001085') 6-filter("T"."FROMNBGL1"='0'AND"T"."XJBZ"='9999'AND"T"."FZGS_DM"='001085') 7-access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e') 8-filter("T"."FROMNBGL1"='0'AND"T"."XJBZ"='9999'AND"T"."FZGS_DM"='001085') 12-access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 13-access("Y"."KH_ID"="T"."KH_ID") 统计信息 ---------------------------------------------------------- 1recursivecalls 0dbblockgets 128422consistentgets 10308physicalreads 0redosize 512bytessentviaSQL*Nettoclient 469bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 2sorts(memory) 0sorts(disk) 1rowsprocessed
SQL改写之后，执行时间由原来的20秒下降到6秒，逻辑读由804560降低到128422，性能还是有很大提升的，到了这里优化还没完，可以创建一个组合索引进一步优化。
create index idx on swgl_ddjbxx(fzgs_dm,xjbz,FROMNBGL1);
创建索引之后，原始的SQL执行时间，执行计划，统计信息如下：
SQL>selectcount(*)rowcount_lhy 2fromswgl_ddjbxxt 3wheret.fzgs_dm='001085' 4and(t.lrr_dm='e90e3fe4237c4af988477329c7f2059e'orexists 5(selecty.kh_id 6fromkhgl_khywdlxxy 7wherey.kh_id=t.kh_id 8andy.sskhjl_dm='e90e3fe4237c4af988477329c7f2059e')or 9t.kpr_dm='e90e3fe4237c4af988477329c7f2059e') 10andt.xjbz='9999' 11andt.FROMNBGL1='0'; ROWCOUNT_LHY ------------ 60 已用时间:00:00:02.96 执行计划 ---------------------------------------------------------- Planhashvalue:3049366449 -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|86|506(0)|00:00:07| |1|SORTAGGREGATE||1|86||| |*2|FILTER|||||| |3|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|5926|497K|506(0)|00:00:07| |*4|INDEXRANGESCAN|IDX|2370||12(0)|00:00:01| |*5|TABLEACCESSBYINDEXROWID|KHGL_KHYWDLXX|1|57|5(0)|00:00:01| |*6|INDEXRANGESCAN|IDX_KHGL_KHYWDLXX_KHID|1||3(0)|00:00:01| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 2-filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e'OR "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e'OREXISTS(SELECT0FROM"KHGL_KHYWDLXX""Y" WHERE"Y"."KH_ID"=:B1AND"Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e'))4-access("T"."FZGS_DM"='001085'AND"T"."XJBZ"='9999'AND"T"."FROMNBGL1"='0') 5-filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 6-access("Y"."KH_ID"=:B1) 统计信息 ---------------------------------------------------------- 1recursivecalls 0dbblockgets 702767consistentgets 0physicalreads 0redosize 516bytessentviaSQL*Nettoclient 469bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 1rowsprocessed
改写的SQL：
SQL>selectcount(*) 2from(select* 3fromswgl_ddjbxxt 4wheret.lrr_dm='e90e3fe4237c4af988477329c7f2059e' 5andt.fzgs_dm='001085' 6andt.xjbz='9999' 7andt.FROMNBGL1='0' 8union 9select* 10fromswgl_ddjbxxt 11wheret.kpr_dm='e90e3fe4237c4af988477329c7f2059e' 12andt.fzgs_dm='001085' 13andt.xjbz='9999' 14andt.FROMNBGL1='0' 15union 16select* 17fromswgl_ddjbxxt 18whereexists 19(selecty.kh_id 20fromkhgl_khywdlxxy 21wherey.kh_id=t.kh_id 22andy.sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') 23andt.fzgs_dm='001085' 24andt.xjbz='9999' 25andt.FROMNBGL1='0'); COUNT(*) ---------- 60 已用时间:00:00:00.53 执行计划 ---------------------------------------------------------- Planhashvalue:2947849958 ------------------------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|||3469(1)|00:00:42| |1|SORTAGGREGATE||1||||| |2|VIEW||5995|||3469(1)|00:00:42| |3|SORTUNIQUE||5995|2238K|4760K|3469(86)|00:00:42| |4|UNION-ALL||||||| |*5|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|59|19234||506(0)|00:00:07| |*6|INDEXRANGESCAN|IDX|2370|||12(0)|00:00:01| |7|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|10|3260||50(0)|00:00:01| |8|BITMAPCONVERSIONTOROWIDS||||||| |9|BITMAPAND||||||| |10|BITMAPCONVERSIONFROMROWIDS||||||| |*11|INDEXRANGESCAN|IDX|2370|||12(0)|00:00:01| |12|BITMAPCONVERSIONFROMROWIDS||||||| |*13|INDEXRANGESCAN|IDX_SWGL_DDJBXX_KPRDM|2370|||34(0)|00:00:01| |*14|HASHJOINRIGHTSEMI||5926|2216K||2423(1)|00:00:30| |15|TABLEACCESSBYINDEXROWID|KHGL_KHYWDLXX|10165|565K||1916(1)|00:00:23| |*16|INDEXRANGESCAN|IDX_KHGL_KHYWDLXX_SSKHJL|10165|||111(0)|00:00:02| |17|TABLEACCESSBYINDEXROWID|SWGL_DDJBXX|5926|1886K||506(0)|00:00:07| |*18|INDEXRANGESCAN|IDX|2370|||12(0)|00:00:01| ------------------------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 5-filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e') 6-access("T"."FZGS_DM"='001085'AND"T"."XJBZ"='9999'AND"T"."FROMNBGL1"='0') 11-access("T"."FZGS_DM"='001085'AND"T"."XJBZ"='9999'AND"T"."FROMNBGL1"='0') filter("T"."FROMNBGL1"='0'AND"T"."XJBZ"='9999'AND"T"."FZGS_DM"='001085') 13-access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e') 14-access("Y"."KH_ID"="T"."KH_ID") 16-access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 18-access("T"."FZGS_DM"='001085'AND"T"."XJBZ"='9999'AND"T"."FROMNBGL1"='0') 统计信息 --------------------------------------------------------- 1recursivecalls 0dbblockgets 25628consistentgets 0physicalreads 0redosize 512bytessentviaSQL*Nettoclient 469bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 1sorts(memory) 0sorts(disk) 1rowsprocessed
以上就是SQL Server使用UNION代替OR提升查询性能的实例，本文我们就介绍到这里了，希望本次的介绍能够对您有所收获！
【编辑推荐】
- JSP链接SQL Server 2005出错的解决方法JDBC调用带输出参数的存储过程的实例解析SQL Server数据库多表关联汇总查询的问题解决设置SQL Server数据库AWE机制使其支持大内存SQL Server 2008数据库被标记为可疑的解决方法