下班前再问个语句的优化
这个语句感觉已经很简单了。 但是要40秒以上，哪里可以优化？？
BA_GDSBARCODE e(index idx_BA_GDSBARCODE_1)
这个强制索引应该不是问题，如果不走这个索引，数据直接就出不来
应用 性能优化
set statistics io on
set statistics time on
select distinct a.gdsid,a.spm,a.ggxh,e.barcode,0,'',a.pp,c.ppmc,c.parentid,c.parentmc,a.tjlb,d.tjlbm,e.gys,'',
b.prop1,b.prop2,b.prop3,b.prop4,b.prop5,b.prop6,b.cd,b.grade,b.size,b.unit,b.pricer,'','',
b.prop7,b.prop8,b.prop9,b.prop10,'N',0,0,0,
case when len(a.barcode) > 9 and (substring(a.barcode,1,1)<>'S') then a.barcode else '/' end
from BA_GDSBARCODE e(index idx_BA_GDSBARCODE_1),
BA_GDS a,
BA_GDSPROP_APP b,
BA_BRAND c,
BA_STATCLS d,
BA_SUPPLIER f
where a.gdsid=b.gdsid
and a.pp=c.pp
and a.gdsid = e.gdsid
and a.tjlb = d.tjlb
and e.gys = f.supid
and b.orgid = '2704'
and c.parentid='00007'
-- IO消耗
Table: BA_GDSBARCODE scan count 1774, logical reads: (regular=260727 apf=0 total=260727), physical reads: (regular=272 apf=42 total=314), apf IOs used=42
Table: BA_GDS scan count 11032, logical reads: (regular=21544 apf=0 total=21544), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BA_GDSPROP_APP scan count 1, logical reads: (regular=855 apf=0 total=855), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BA_BRAND scan count 11032, logical reads: (regular=33114 apf=0 total=33114), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BA_STATCLS scan count 1774, logical reads: (regular=3559 apf=0 total=3559), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BA_SUPPLIER scan count 405184, logical reads: (regular=1215552 apf=0 total=1215552), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=509883 apf=0 total=509883), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
Execution Time 181.
SQL Server cpu time: 18100 ms. SQL Server elapsed time: 41333 ms.
--表数据量
sp_spaceused BA_GDSBARCODE --14752741
sp_spaceused BA_GDS --2562998
sp_spaceused BA_GDSPROP_APP --258539
sp_spaceused BA_BRAND --48609
sp_spaceused BA_STATCLS --8714
sp_spaceused BA_SUPPLIER --171818
--执行计划
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for DISTINCT.
FROM TABLE
BA_GDSPROP_APP
b
Nested iteration.
Using Clustered Index.
Index : PK_BA_GDSPROP_APP
Forward scan.
Positioning by key.
Keys are:
orgid ASC
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
BA_GDS
a
Nested iteration.
Using Clustered Index.
Index : PK_BA_GDS
Forward scan.
Positioning by key.
Keys are:
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.
FROM TABLE
BA_BRAND
c
Nested iteration.
Using Clustered Index.
Index : PK_BA_BRAND
Forward scan.
Positioning by key.
Keys are:
pp ASC
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
BA_STATCLS
d
Nested iteration.
Using Clustered Index.
Index : PK_BA_STATCLS
Forward scan.
Positioning by key.
Keys are:
tjlb ASC
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
BA_GDSBARCODE
e
Nested iteration.
Index : idx_BA_GDSBARCODE_1
Forward scan.
Positioning by key.
Keys are:
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.
FROM TABLE
BA_SUPPLIER
f
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : PK_BA_SUPPLIER
Forward scan.
Positioning by key.
Keys are:
supid ASC
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
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.
