请教一个语句的优化

华夏小卒 2013-08-19 04:44:35
哪位帮看看，给点建议





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.
...全文
312 23 打赏 收藏 转发到动态 举报
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
依然不逆 2013-08-20
  • 打赏
  • 举报
 回复
union上下分别返回了多少行数据？ 那几个表中的索引怎么建的？
皇后陛下 2013-08-20
  • 打赏
  • 举报
 回复
哈哈 方法
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
引用 20 楼 js_szy 的回复:
[quote=引用 19 楼 DBA_Huangzj 的回复:]
那结贴吧，不想看到你这头像，


这。。。。你嫉妒，小心晚上堵车堵到你家楼下[/quote]已到家，谢谢关心，吃饭去
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 19 楼 DBA_Huangzj 的回复:
那结贴吧，不想看到你这头像，
这。。。。你嫉妒，小心晚上堵车堵到你家楼下
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
那结贴吧，不想看到你这头像，
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 17 楼 DBA_Huangzj 的回复:
试过了，我这个就是gif，但是不行，所以才问你
额，那我也不清楚了。哈哈
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
试过了，我这个就是gif，但是不行，所以才问你
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 15 楼 DBA_Huangzj 的回复:
话说你那动态头像怎么搞的，一直搞不出来
可以用的头像测试下呢。应该gif 会动的都可以吧
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
话说你那动态头像怎么搞的，一直搞不出来
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 12 楼 DBA_Huangzj 的回复:
sybase不知道有没有对这方面做优化，一般来说not exists会导致表扫描
对，not exists 会导致表扫描。
引用 13 楼 wwwwgou 的回复:
try it. 

......
--szy_ok表加复合索引(gdsid,gys,fmid)
这位兄弟最后一句提醒了我。 我加了索引以后，已经有所改观了。 szy_ok 有了索引，逻辑读大幅下降，执行时间也大大减少 

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=301748 apf=0 total=301748), physical reads: (regular=0 apf=0 total=0), apf IOs used=0Table: Worktable1  scan count 0, logical reads: (regular=188099 apf=0 total=188099), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
 
Execution Time 162.
SQL Server cpu time: 16200 ms.  SQL Server elapsed time: 33166 ms.
Shawn 2013-08-19
  • 打赏
  • 举报
 回复
try it. 
insert szy_ok
select distinct * from
(
	select a.gdsid,a.gdsmc,a.xh,a.gys,b.fmid
	from CITY b,(select distinct gdsid,gdsmc,xh,gys from szy_jg1) a  --city表建立复合索引(gdsid,gys,city,fmid,flg1)
	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'      
	union all	--这儿想办法用union all,是哪个表的数据导致了重复？先把那张表DISTINCT后再JOIN(如果是szy_jg1就好了)
	select a.gdsid,a.gdsmc,a.xh,a.gys,b.fmid
	from LABEL b,(select distinct gdsid,gdsmc,xh,gys from 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'  
) t
where not exists (select 1 from szy_ok c where c.gdsid = t.gdsid and c.gys = t.gys and c.rtptype = t.fmid)   --szy_ok表加复合索引(gdsid,gys,fmid)
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
sybase不知道有没有对这方面做优化，一般来说not exists会导致表扫描
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
有了那个not exists ， szy_ok 逻辑读会变的超大。千万级了。cpu执行时间也是N倍的增加了 Table: szy_ok scan count 149415, logical reads: (regular=23128183 apf=0 total=23128183), physical reads: (reg
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
如果没有丢失索引的话，试下用join的形式替代
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 8 楼 DBA_Huangzj 的回复:
看不懂...依照你的看法，瓶颈在哪里？
通过实际数据测试。 我用union 上面的一半语句测试，感觉主要问题出在not exists 问题上。 



--语句1：
set statistics io on
set statistics time on

select distinct a.gdsid,a.gdsmc,a.xh,a.barcode,b.xsj,b.fmid,b.lastmodified,a.pp,a.ppmc,a.syb,a.sybmc,a.tjlb,a.spzmc,a.gys,a.gysmc, a.prop1,a.prop2,a.prop3,a.prop4,a.prop5,a.prop6,a.cd,a.grade,a.size,a.unit,a.pricer,a.prop7,a.prop8,a.prop9,a.prop10,'N' ,0,0,0
  from mkt.dbo.BA_GDSRTP_CITY b,szy_jg1 a   
 where a.gdsid=b.gdsid AND a.gys=b.gys      
   and b.city =  '9000000'
   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)    
   
   
 
   
   
   Table: BA_GDSRTP_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: Worktable1  scan count 0, logical reads: (regular=188099 apf=0 total=188099), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
 
Execution Time 2012.
SQL Server cpu time: 201200 ms.  SQL Server elapsed time: 210696 ms.


--语句2：
set statistics io on
set statistics time on

select distinct a.gdsid,a.gdsmc,a.xh,a.barcode,b.xsj,b.fmid,b.lastmodified,a.pp,a.ppmc,a.syb,a.sybmc,a.tjlb,a.spzmc,a.gys,a.gysmc, a.prop1,a.prop2,a.prop3,a.prop4,a.prop5,a.prop6,a.cd,a.grade,a.size,a.unit,a.pricer,a.prop7,a.prop8,a.prop9,a.prop10,'N' ,0,0,0
  from mkt.dbo.BA_GDSRTP_CITY b,szy_jg1 a   
 where a.gdsid=b.gdsid AND a.gys=b.gys      
   and b.city =  '9000000'
   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)    
   
   

Table: BA_GDSRTP_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: Worktable1  scan count 0, logical reads: (regular=188603 apf=0 total=188603), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
 
Execution Time 130.
SQL Server cpu time: 13000 ms.  SQL Server elapsed time: 32836 ms.
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
看不懂...依照你的看法，瓶颈在哪里？
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 5 楼 DBA_Huangzj 的回复:
SQL Server cpu time: 201200 ms. SQL Server elapsed time: 207066 ms.那为啥有这个？
这个sybase ase 也是有的。在IO的最后显示实际的cpu执行时间。
--小F-- 2013-08-19
  • 打赏
  • 举报
 回复
应该是执行计划吧。
發糞塗牆 2013-08-19
  • 打赏
  • 举报
 回复
SQL Server cpu time: 201200 ms. SQL Server elapsed time: 207066 ms.那为啥有这个？
华夏小卒 2013-08-19
  • 打赏
  • 举报
 回复
引用 2 楼 DBA_Huangzj 的回复:
你给个图形化的来看吧，这文本的看的纠结
我这是sybase ase的，没有像sql server 那样图形化的执行计划。 sybase那边人气太低了。几天都没人回帖。
加载更多回复（3）
一条sql语句完成MySQL去重留一
查了资料，请教了大佬之后得出了一个很便利的sql语句，这里分享下这段sql语句和思路。 需求分析 数据库中存在重复记录，删除保留其中一条（是否重复判断基准为多个字段） 解决方案 碰到这个需求的时候，心里大概是有...
oracle如何加快语句执行,一条sql语句，执行太慢了，请问下如何优化
Oracle开发»一条sql语句，执行太慢了，请问下如何优化一条sql语句，执行太慢了，请问下如何优化www.myexceptions.net网友分享于：2013-02-24浏览：76次一条sql语句，执行太慢了，请教下怎么优化一条sql语句，执行太...
全面深入Mysql数据库优化
本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识，并通过综合案例，对课程中的知识进行一个整合...
sql语句优化之SQL Server(优化方案大全)
MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。...10、查询语句不好，没有优化可以
SQL server 语句优化合集
这篇文章主要介绍了sql语句优化之SQL Server篇，整理的比较详细，推荐收藏 MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷...
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章