這篇文章主要介紹SQLServer如何使用UNION代替OR提升查詢性能,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!
成都創(chuàng)新互聯(lián)是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷策劃、微信平臺(tái)小程序開發(fā)、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立十年以來,已經(jīng)為上1000+成都餐廳設(shè)計(jì)各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)?,F(xiàn)在,服務(wù)的上1000+客戶與我們一路同行,見證我們的成長(zhǎng);未來,我們一起分享成功的喜悅。SQLServer數(shù)據(jù)庫查詢的過程中,通過對(duì)SQL語句的優(yōu)化來提高SQL查詢的性能。下面創(chuàng)新互聯(lián)網(wǎng)站建設(shè)公司,小編來講解下SQLServer怎么使用UNION代替OR提升查詢性能?
SQLServer怎么使用UNION代替OR提升查詢性能
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已用時(shí)間:00:00:20。53執(zhí)行計(jì)劃——————————————————————————————————————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)統(tǒng)計(jì)信息——————————————————————————————————————0recursivecalls0dbblockgets804560consistentgets71127physicalreads0redosize516bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed
用UNION代替OR對(duì)其進(jìn)行優(yōu)化后的代碼如下:
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已用時(shí)間:00:00:06。89執(zhí)行計(jì)劃——————————————————————————————————————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")統(tǒng)計(jì)信息----------------------------------------------------------1recursivecalls0dbblockgets128422consistentgets10308physicalreads0redosize512bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient2sorts(memory)0sorts(disk)1rowsprocessed
SQL改寫之后,執(zhí)行時(shí)間由原來的20秒下降到6秒,邏輯讀由804560降低到128422,性能還是有很大提升的,到了這里優(yōu)化還沒完,可以創(chuàng)建一個(gè)組合索引進(jìn)一步優(yōu)化。
createindexidxonswgl_ddjbxx(fzgs_dm,xjbz,F(xiàn)ROMNBGL1);
SQLServer怎么使用UNION代替OR提升查詢性能
創(chuàng)建索引之后,原始的SQL執(zhí)行時(shí)間,執(zhí)行計(jì)劃,統(tǒng)計(jì)信息如下:
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已用時(shí)間:00:00:02。96執(zhí)行計(jì)劃----------------------------------------------------------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)統(tǒng)計(jì)信息----------------------------------------------------------1recursivecalls0dbblockgets702767consistentgets0physicalreads0redosize516bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed
改寫的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已用時(shí)間:00:00:00。53執(zhí)行計(jì)劃----------------------------------------------------------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')統(tǒng)計(jì)信息---------------------------------------------------------1recursivecalls0dbblockgets25628consistentgets0physicalreads0redosize512bytessentviaSQL*Nettoclient469bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)1rowsprocessed。
以上是“SQLServer如何使用UNION代替OR提升查詢性能”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對(duì)大家有幫助,更多相關(guān)知識(shí),歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道!
網(wǎng)站欄目:SQLServer如何使用UNION代替OR提升查詢性能-創(chuàng)新互聯(lián)
文章URL:http://www.rwnh.cn/article20/dgspco.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供營(yíng)銷型網(wǎng)站建設(shè)、面包屑導(dǎo)航、電子商務(wù)、網(wǎng)站營(yíng)銷、網(wǎng)站改版、企業(yè)建站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容