求高手解决SQL语句优化有关问题

select a.freeze_logId,a.OD_ID,a.SC_ID,a.status,b.AD_ID,b.buy_uid,b.buy_zid,b.create_time,b.oid,b.order_number,b.type,c.ad_title,c.user_id,d.S_DATETIME,e.nick as Buy_Nick,f.nick as TeacheNick,(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10') as waiting,g.Time_zone_difference,g.CountriesOrRegions,(select dateadd(HH,g.Time_zone_difference - 8,getdate())) as localTime  from dbo.Trade_OrderDetail   as a with(nolock) INNER JOIN dbo.Trade_Order as b with(nolock) on  a.OID = b.OID INNER JOIN dbo.pro_advertisement as c with(nolock) on b.AD_ID = c.ad_id INNER JOIN dbo.Trade_SchedulesClock as d with(nolock) on d.SC_ID = a.SC_IDINNER JOIN dbo.uc_User as e with(nolock) on b.buy_uid = e.uidINNER JOIN dbo.uc_User as f with(nolock) on c.user_id = f.uidINNER JOIN dbo.st_zebraTime as g with(nolock) on b.buy_zid = g.ZID

DECLARE waiting INTSET @waiting =(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10')select a.freeze_logId,a.OD_ID,a.SC_ID,a.status,b.AD_ID,b.buy_uid,b.buy_zid,b.create_time,b.oid,b.order_number,b.type,c.ad_title,c.user_id,d.S_DATETIME,e.nick as Buy_Nick,f.nick as TeacheNick,@waiting as waiting,g.Time_zone_difference,g.CountriesOrRegions,dateadd(HH,g.Time_zone_difference - 8,getdate()) as localTime  from dbo.Trade_OrderDetail   as a with(nolock) INNER JOIN dbo.Trade_Order as b with(nolock) on  a.OID = b.OID INNER JOIN dbo.pro_advertisement as c with(nolock) on b.AD_ID = c.ad_id INNER JOIN dbo.Trade_SchedulesClock as d with(nolock) on d.SC_ID = a.SC_IDINNER JOIN dbo.uc_User as e with(nolock) on b.buy_uid = e.uidINNER JOIN dbo.uc_User as f with(nolock) on c.user_id = f.uidINNER JOIN dbo.st_zebraTime as g with(nolock) on b.buy_zid = g.ZID
------解决方案--------------------
楼主的脚本应该有点问题，
一楼已经帮忙改过来了，
脚本写法已经优化的差不多了，
接下来 考虑下索引及其他优化吧
给你个网址你看考下 ..
http://space.itpub.net/11320622/viewspace-675443

探讨


先试下

SQL code
DECLARE waiting INT
SET @waiting =(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10')

select a.freeze_logId,a.OD_ID,a.SC_ID,a.status
,b.AD_ID,b.buy_ui……