请教一个语句的优化
哪位帮看看，给点建议
insert szy_ok
select distinct a.gdsid,a.gdsmc,a.xh,a.gys,fmid
from CITY b,szy_jg1 a
where a.gdsid=b.gdsid AND a.gys=b.gys
and b.city = '900'
and b.fmid in ('0001','0002','0003') and b.flg1 <>'X'
and not exists (select 1 from szy_ok c where c.gdsid = b.gdsid and c.gys = b.gys and c.rtptype = b.fmid)
union
select distinct a.gdsid,a.gdsmc,a.xh,a.gys,fmid
from LABEL b,szy_jg1 a
where a.gdsid=b.gdsid AND a.gys=b.gys
and b.city = '900'
and b.fmid in ('0001','0002','0003') and b.flg1 <>'X'
and not exists (select 1 from szy_ok c where c.gdsid = b.gdsid and c.gys = b.gys and c.rtptype = b.fmid)
--IO消耗
Table: CITY scan count 403720, logical reads: (regular=1697756 apf=0 total=1697756), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: szy_jg1 scan count 1, logical reads: (regular=27183 apf=0 total=27183), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: szy_ok scan count 149415, logical reads: (regular=23128183 apf=0 total=23128183), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: LABEL scan count 1, logical reads: (regular=279 apf=4 total=283), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: szy_jg1 scan count 5117, logical reads: (regular=15998 apf=0 total=15998), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: szy_ok scan count 5117, logical reads: (regular=793135 apf=0 total=793135), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
SQL Server cpu time: 201200 ms. SQL Server elapsed time: 207066 ms.
--表数据
sp_spaceused CITY --4824054
sp_spaceused szy_jg1 --403720
sp_spaceused szy_ok --2379
sp_spaceused LABEL --7232
--执行计划
QUERY PLAN FOR STATEMENT 1 (at line 2).
STEP 1
The type of query is INSERT.
The update mode is direct.
FROM TABLE
szy_jg1
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
mkt.dbo.CITY
b
Nested iteration.
Index : BA_GDSRTP_CITY
Forward scan.
Positioning by key.
Keys are:
city ASC
gys ASC
gdsid ASC
Run subquery 1 (at nesting level 1).
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 1
The type of query is INSERT.
The update mode is direct.
FROM TABLE
mkt.dbo.LABEL
b
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Run subquery 1 (at nesting level 1).
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
szy_jg1
a
Nested iteration.
Index : szy_jg
Forward scan.
Positioning by key.
Keys are:
gys ASC
gdsid ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 1
The type of query is SELECT.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 7).
Correlated Subquery.
Subquery under an EXISTS predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
FROM TABLE
szy_ok
c
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
END OF QUERY PLAN FOR SUBQUERY 1.
STEP 2
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 14).
Correlated Subquery.
Subquery under an EXISTS predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
FROM TABLE
szy_ok
c
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
END OF QUERY PLAN FOR SUBQUERY 1.
