Shulou(Shulou.com)05/31 Report--

This article mainly introduces how SQLServer uses UNION instead of OR to improve query performance. It is very detailed and has a certain reference value. Interested friends must read it!

In the process of SQLServer database query, the performance of SQL query is improved by optimizing SQL statements. The following editor will explain how SQLServer uses UNION instead of OR to improve query performance.

How to use UNION instead of OR to improve query performance in SQLServer

SQL > settimingonSQL > setautotraceonSQL > selectcount (*) rowcount_lhy2fromswgl_ddjbxxt3wheret. Fzgs_dm='001085'4and (t. Lrr_dm='e90e3fe4237c4af988477329c7f2059e'orexists5 (selecty. Kh_id6fromkhgl_khywdlxxy7wherey. Kh_id=t. Kh_id8andy. Sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') or9t. Kpr_dm='e90e3fe4237c4af988477329c7f2059e') 10andt. Xjbz='9999'11andt. FROMNBGL1='0';SQL > setline300SQL > / ROWCOUNT_LHY-60 elapsed time: 00:00:20 53 execution plan-- 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) Statistics-- 0recursivecalls0dbblockgets804560consistentgets71127physicalreads0redosize516bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts (memory) 0sorts (disk) 1rowsprocessed

The code optimized with UNION instead of OR is as follows:

SQL > selectcount (*) 2from (select*3fromswgl_ddjbxxt4wheret. Lrr_dm='e90e3fe4237c4af988477329c7f2059e'5andt. Fzgs_dm='001085'6andt. Xjbz='9999'7andt. FROMNBGL1='0'8union9select*10fromswgl_ddjbxxt11wheret. Kpr_dm='e90e3fe4237c4af988477329c7f2059e'12andt. Fzgs_dm='001085'13andt. Xjbz='9999'14andt. FROMNBGL1='0'15union16select*17fromswgl_ddjbxxt18whereexists19 (selecty. Kh_id20fromkhgl_khywdlxxy21wherey. Kh_id=t. Kh_id22andy. Sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') 23andt Fzgs_dm='001085'24andt. Xjbz='9999'25andt. FROMNBGL1='0'); COUNT (*)-60 elapsed time: 00:00:06 89 implementation plan-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 | * 5 | 5927 | 2216K | | 22527 (1) | 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 | | 111O | 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 ") Statistics-1recursivecalls0dbblockgets128422consistentgets10308physicalreads0redosize512bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient2sorts (memory) 0sorts (disk) 1rowsprocessed

After SQL rewriting, the execution time is reduced from 20 seconds to 6 seconds, and the logical read is reduced from 804560 to 128422. The performance has been greatly improved. At this point, the optimization is not finished, so you can create a composite index for further optimization.

Createindexidxonswgl_ddjbxx (fzgs_dm,xjbz,FROMNBGL1)

How to use UNION instead of OR to improve query performance in SQLServer

After the index is created, the original SQL execution time, execution plan, and statistics are as follows:

SQL > selectcount (*) rowcount_lhy2fromswgl_ddjbxxt3wheret. Fzgs_dm='001085'4and (t. Lrr_dm='e90e3fe4237c4af988477329c7f2059e'orexists5 (selecty. Kh_id6fromkhgl_khywdlxxy7wherey. Kh_id=t. Kh_id8andy. Sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') or9t. Kpr_dm='e90e3fe4237c4af988477329c7f2059e') 10andt. Xjbz='9999'11andt. FROMNBGL1='0';ROWCOUNT_LHY-60 used time: 00:00:02. 96 implementation plan-Planhashvalue:3049366449 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -- | 0 | SELECTSTATEMENT | 1 | 86 | 506 | 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) Statistics-1recursivecalls0dbblockgets702767consistentgets0physicalreads0redosize516bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts (memory) 0sorts (disk) 1rowsprocessed

Rewritten SQL:

SQL > selectcount (*) 2from (select*3fromswgl_ddjbxxt4wheret. Lrr_dm='e90e3fe4237c4af988477329c7f2059e'5andt. Fzgs_dm='001085'6andt. Xjbz='9999'7andt. FROMNBGL1='0'8union9select*10fromswgl_ddjbxxt11wheret. Kpr_dm='e90e3fe4237c4af988477329c7f2059e'12andt. Fzgs_dm='001085'13andt. Xjbz='9999'14andt. FROMNBGL1='0'15union16select*17fromswgl_ddjbxxt18whereexists19 (selecty. Kh_id20fromkhgl_khywdlxxy21wherey. Kh_id=t. Kh_id22andy. Sskhjl_dm='e90e3fe4237c4af988477329c7f2059e') 23andt Fzgs_dm='001085'24andt. Xjbz='9999'25andt. FROMNBGL1='0'); COUNT (*)-60 elapsed time: 00:00:00 53 implementation plan-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 | 5926 | 2423 (1) | 00:00:30 | 15 | TABLEACCESSBYINDEXROWID | KHGL_KHYWDLXX | 10165 | 565K | 1916 (1) | 00:00:23 | 17 TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | 17 TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | * 18 | INDEXRANGESCAN IDX | 2370 | | 12 (0) | 10165 | 565K | | 1916 (1) | | 17 TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | * 18 | INDEXRANGESCAN IDX | 2370 | | 12 (0) | 10165 | 565K | | 1916 (1) | | 17 | TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | | * 18 | INDEXRANGESCAN IDX | 2370 | | 12 (0) | 10165 | 565K | | 1916 (1) | | 17 | TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | | * 18 | INDEXRANGESCAN | IDX | 2370 | | 12 (0) | 10165 | 565k | | 1916 (1) | | 17 | TABLEACCESSBYINDEXROWID | SWGL_DDJBXX | 5926 | 188K | | 506 (0) | 00:00:07 | | * 18 | INDEXRANGESCAN | IDX | 2370 | | 12 (0) | 10165 | 565K | | -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') Statistics-1recursivecalls0dbblockgets25628consistentgets0physicalreads0redosize512bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts (memory) 0sorts (disk) 1rowsprocessed.

These are all the contents of the article "how SQLServer uses UNION instead of OR to improve query performance". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.