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)