sql优化，头疼啊初学者虚心请问，求指导

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 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
------解决方案-------------------- 
 
说实话，这样看着也够雷人的
探讨

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……

[解决办法]
o(∩_∩)o 哈哈         刘西北
[解决办法]
原来dba就是干的擦屁股的活

[解决办法]
SQL code
select USERID,count(0) AS piccount INTO #PICSTAfrom #pic WHERE USERid<100GROUP BY useridselect e.id,n.id,n.newname,P.piccount from #user e innerjoin #news n on e.id=n.userid LEFT JOIN #PICSTA P ON E.ID = P.USERIDwhere e.id<100