日期:2014-05-18  浏览次数:20682 次

sql优化,头疼啊,菜鸟虚心请教,求指导!
先说说情况,一个网站,一台服务器,一天流量差不多8000到1万独立ip。
服务器inter双核cpu,4G内存 。
目前服务器cpu使用一直从90%+到100%波动,网站速度很慢。看了下最主要就是两个进程
一个是iis的,一个是sql的,然后老板让处理下。
这网站不是我做的,我过来之前几个人已经开发完毕,然后就走人了,我过来维护。
这个情况朋友告诉我先弄下sql吧,从程序开始优化。用sql跟踪一下发现这样的情况,我不知道cpu reads这些是以什么为单位的,不过数值这么大肯定有问题咯

像这样的sql还有不少,应该都需要优化吧
还有在活动监视器里发现不少sql进程阻塞。。。。
我先贴一个sql出来大家看看
SQL code
select e.EshowName,e.id,e.IsJingTai,e.StartTime,g.ChGName,g.Id as CGId,d.Ding,d.Cai,e.zhangshangadd,(select count(1) from TEshowPic where EshowPicTypeNo=1 and TEshowNo=e.id ) as tucount from TEshow e 
left join TChangGuan g on e.TChangGuanno=g.id left join TDingCai d on d.ParentNo=e.id where e.id in (select e.id from (select e.id from (select top 0 e.id from TEshow e left join TChangGuan g on 
e.TChangGuanno=g.id left join TDingCai d on d.ParentNo=e.id where  e.IsTag=1 and e.DelTag=0 and e.TBigSortNo=1  and (d.TypeNo=1 or d.TypeNo is null) and  e.userno=1  and 
datediff(d,getdate(),StartTime)<=0 and e.SysCityNo=1 and e.TSmallSort like '%装备制造业%'order by e.StartTime desc,e.id desc) as e union all select * from (select top 26 e.id from TEshow e left join 
TChangGuan g on e.TChangGuanno=g.id left join TDingCai d on d.ParentNo=e.id where  e.IsTag=1 and e.DelTag=0 and e.TBigSortNo=1  and (d.TypeNo=1 or d.TypeNo is null) and  e.userno=1  and 
datediff(d,getdate(),StartTime)<=0 and e.SysCityNo=1 and e.TSmallSort like '%装备制造业%'order by e.StartTime desc,e.id desc) as b ) e group by e.id having count(e.id)=1 ) order by e.StartTime desc,e.id 
desc
这句就是图中的那句sql

------解决方案--------------------
SQL code

建议你提供详细的资料:
例如表的结构,表之间的关系,测试数据,相关算法及需要的结果。
这样有助于我们理解你的意思,更主要的是能尽快让你获得答案或解决问题的方法。

------解决方案--------------------
这么多子查询,先把子查询放在临时表里 然后再从临时表里取数据
------解决方案--------------------
首先膜拜一下写这个代码的人
------解决方案--------------------
写这么长的代码的人都是坑爹的
------解决方案--------------------
SQL code
--这样看着累不累??
select
 e.EshowName,e.id,e.IsJingTai,e.StartTime,g.ChGName,g.Id as CGId,d.Ding,d.Cai,e.zhangshangadd,
 (select count(1) from TEshowPic where EshowPicTypeNo=1 and TEshowNo=e.id ) as tucount from TEshow e 
left join
 TChangGuan g on e.TChangGuanno=g.id 
left join
 TDingCai d on d.ParentNo=e.id 
where
 e.id 
in
 (select  
    e.id 
    from 
      (select 
          e.id 
       from (select
                 top 0 e.id 
             from
                 TEshow e left join TChangGuan g on e.TChangGuanno=g.id left join TDingCai d on d.ParentNo=e.id 
             where
                 e.IsTag=1 and e.DelTag=0 and e.TBigSortNo=1  and (d.TypeNo=1 or d.TypeNo is null) and  e.userno=1 
              and 
                datediff(d,getdate(),StartTime)<=0 and e.SysCityNo=1 and e.TSmallSort like '%装备制造业%'order by e.StartTime desc,e.id desc) as e
             union all
             select
                * 
              from
                (select 
                    top 26 e.id 
                 from
                    TEshow e 
                 left join
                    TChangGuan g on e.TChangGuanno=g.id left join TDingCai d on d.ParentNo=e.id 
                 where
                    e.IsTag=1 and e.DelTag=0 and e.TBigSortNo=1  and (d.TypeNo=1 or d.TypeNo is null) and  e.userno=1  
                 and 
                     datediff(d,getdate(),StartTime)<=0 and e.SysCityNo=1 
                 and e.TSmallSort like '%装备制造业%'order by e.StartTime desc,e.id desc) as b ) e 
group by
    e.id 
having
    count(e.id)=1 ) 
order by
    e.StartTime desc,e.id desc

------解决方案--------------------