Database execution Stored procedure optimization (1)

Source: Internet
Author: User
Tags getdate

The following old is the original query, it took about 58 seconds, the following optimized, executed for 22 seconds.

Core idea: Narrow the data range first and then make the associated query with other tables.

  ----new 2018-07-03  Insert  into @tab_xmxkxx    Select  DISTINCT CONVERT(NVARCHAR( -),GETDATE(), the) asJHRQ, tab_sgxkdwxx.wsslbh aswsslbh, Tab_xmxkxx.xmmc asXmmc, TAB_SGXKDWXX.DWMC asZBDWMC,CONVERT(NVARCHAR( -), TAB_XMXKXX.SDRQ, the) asHfxkzrq, TAB_XMXKXX.FZDWMC asHFXKZBM from        (SelectBjbh,bdh,wsslbh,dwmc from [sgxk].[tab_zbb_xmxkxx_dwxx]  asSgxkdwxxwhere ISNULL(SGXKDWXX.WSSLBH,"')<> "'   andSgxkdwxx.dwlb= 'SG') Tab_sgxkdwxx, (SelectBjbh,bdh,xmmc,fzdwmc,sdrq fromSgxk.tab_zbb_xmxkxx xmxkxxwhereXmxkxx.state='sdapproved'  andXmxkxx.fzrqbetween @startTime  and @endTime) Tab_xmxkxx,@tab_htxx  astab_htxxwhereTab_sgxkdwxx.bjbh=TAB_XMXKXX.BJBH COLLATE Chinese_prc_cs_as andTab_sgxkdwxx.bdh=TAB_XMXKXX.BDH COLLATE chinese_prc_cs_as andTab_sgxkdwxx.wsslbh=tab_htxx.wsslbh; ------------------------------------------------------------------------------------------------------------- ----------------------   /*--old 2018-06-12insert into @tab_xmxkxx SELECT DISTINCT CONVERT (NVARCHAR (), GETDATE (),) as JHRQ, SGXKDW XX.WSSLBH as WSSLBH, Xmxkxx.xmmc as XMMC, SGXKDWXX.DWMC as ZBDWMC, CONVERT (NVARCHAR), xmxkxx.sdrq,1 As HFXKZRQ, XMXKXX.FZDWMC as HFXKZBM from [Sgxk]. [TAB_ZBB_XMXKXX_DWXX] As Sgxkdwxx left JOIN sgxk.tab_zbb_xmxkxx xmxkxx on xmxkxx.bjbh COLLATE chinese_prc_cs_as = sgxkdwxx.bjbh COLLATE Ch Inese_prc_cs_as and sgxkdwxx.bdh COLLATE chinese_prc_cs_as = xmxkxx.bdh COLLATE chinese_prc_cs_as WHERE ISNULL (sgxkdwx X.WSSLBH, ') <> ' and xmxkxx.state= ' sdapproved ' and sgxkdwxx.dwlb = ' sg ' and SGXKDWXX.WSSLBH in (SELECT WSS LBH from @tab_htxx)*/    -------------------------------------------------------------------------------------

Database execution Stored procedure optimization (1)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.