先说说情况，一个网站，一台服务器，一天流量差不多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 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
------解决方案--------------------