下面的 old 是原来的查询, 大概花了 58 秒, 下面经过优化后的, 执行了 22 秒.
核心思想: 先缩小数据范围, 然后再和其他的表做 关联查询.
- ----new 2018-07-03
- insert into @tab_xmxkxx
- select DISTINCT CONVERT(NVARCHAR(50),GETDATE(),112) AS jhrq,
- tab_sgxkdwxx.wsslbh as wsslbh,
- tab_xmxkxx.xmmc as xmmc,
- tab_sgxkdwxx.dwmc AS zbdwmc,
- CONVERT(NVARCHAR(50),tab_xmxkxx.sdrq,112) AS hfxkzrq,
- tab_xmxkxx.fzdwmc AS hfxkzbm
- from
- (select bjbh,bdh,wsslbh,dwmc FROM [SgXk].[tab_zbb_xmxkxx_dwxx] AS sgxkdwxx where ISNULL(sgxkdwxx.wsslbh,'') <>'' AND sgxkdwxx.dwlb = 'sg') tab_sgxkdwxx,
- (select bjbh,bdh,xmmc,fzdwmc,sdrq from sgxk.tab_zbb_xmxkxx xmxkxx where xmxkxx.state='SdApproved' and xmxkxx.fzrq BETWEEN @startTime AND @endTime) tab_xmxkxx,
- @tab_htxx as tab_htxx
- where tab_sgxkdwxx.bjbh = tab_xmxkxx.bjbh COLLATE Chinese_PRC_CS_AS and tab_sgxkdwxx.bdh = tab_xmxkxx.bdh COLLATE Chinese_PRC_CS_AS
- and tab_sgxkdwxx.wsslbh = tab_htxx.wsslbh;
- -----------------------------------------------------------------------------------------------------------------------------------
- /*
- -- old 2018-06-12
- insert into @tab_xmxkxx
- SELECT DISTINCT CONVERT(NVARCHAR(50),GETDATE(),112) AS jhrq,
- sgxkdwxx.wsslbh AS wsslbh,
- xmxkxx.xmmc AS xmmc,
- sgxkdwxx.dwmc AS zbdwmc,
- CONVERT(NVARCHAR(50),xmxkxx.sdrq,112) 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 Chinese_PRC_CS_AS AND sgxkdwxx.bdh COLLATE Chinese_PRC_CS_AS = xmxkxx.bdh COLLATE Chinese_PRC_CS_AS
- WHERE ISNULL(sgxkdwxx.wsslbh,'') <>'' AND xmxkxx.state='SdApproved' AND sgxkdwxx.dwlb = 'sg'
- AND sgxkdwxx.wsslbh IN (SELECT wsslbh FROM @tab_htxx)
- */
- -------------------------------------------------------------------------------------
来源: http://www.bubuko.com/infodetail-2669417.html