请大侠帮助优化sql

Question

主要功能是行转列，联合显示在Target_exception  的数据，和不在Target_exception 的数据，sql 2000，数据量几十万条，时间> 5分钟，感觉sql 写的不是很优化， 请大侠帮助优化sql， 谢谢！SELECT  * from (
SELECT  sScheduleName, max(t.TARGET_ID) as sTargetID, 0 as sExceptTarget, @Downloaded as iDownloaded,
TARGET_NM as sTitle, TARGET_DISPLAY_ORDER_NBR,
SUM([HOUR_CNT])+max(OVERTIME_CNT) as iTotal, 
MAX(case [HOUR_NBR] when 1 then [HOUR_CNT] else 0 end) as ihour1,
MAX(case [HOUR_NBR] when 2 then [HOUR_CNT] else 0 end) as ihour2,
MAX(case [HOUR_NBR] when 3 then [HOUR_CNT] else 0 end) as ihour3,
MAX(case [HOUR_NBR] when 4 then [HOUR_CNT] else 0 end) as ihour4,
MAX(case [HOUR_NBR] when 5 then [HOUR_CNT] else 0 end) as ihour5,
MAX(case [HOUR_NBR] when 6 then [HOUR_CNT] else 0 end) as ihour6,
MAX(case [HOUR_NBR] when 7 then [HOUR_CNT] else 0 end) as ihour7,
MAX(case [HOUR_NBR] when 8 then [HOUR_CNT] else 0 end) as ihour8,
MAX(case [HOUR_NBR] when 9 then [HOUR_CNT] else 0 end) as ihour9,
MAX(case [HOUR_NBR] when 10 then [HOUR_CNT] else 0 end) as ihour10,
MAX(case [HOUR_NBR] when 11 then [HOUR_CNT] else 0 end) as ihour11,
max(OVERTIME_CNT) as iHourOT , 'E' as sType
FROM SCHEDULE_TARGET t(NoLock)  INNER JOIN Target_exception  te(NoLock)  ON t.Target_ID = te.Target_ID 
join TARGET_HOUR_EXCEPTION th (nolock) on th.TARGET_ID = te.TARGET_ID and th.iShift =te.iShift and th.sProdDate = te.sProdDate
join MFG_CONTXT m on m.MFG_CONTXT_NBR = t.MFG_CONTXT_NBR
WHERE TARGET_ENABLED_FLAG = 1
AND sScheduleName = @sScheduleName
AND te.iShift = @iShift
AND Convert(nvarchar(10),Convert(datetime, th.sProdDate,101),101) = @sProdDate
and m.MFG_CONTXT_ENABLED_FLAG = 1
group by TARGET_NM, sScheduleName, TARGET_DISPLAY_ORDER_NBR
union all
SELECT  sScheduleName, max(t.TARGET_ID) as sTargetID, 1 as sExceptTarget, @Downloaded as iDownloaded,
t.TARGET_NM as sTitle,TARGET_DISPLAY_ORDER_NBR,
SUM([HOUR_CNT])+max(OVERTIME_CNT) as iTotal, 
MAX(case [HOUR_NBR]  when 1 then [HOUR_CNT] else 0 end) as ihour1,
MAX(case [HOUR_NBR] when 2 then [HOUR_CNT] else 0 end) as ihour2,
MAX(case [HOUR_NBR] when 3 then [HOUR_CNT] else 0 end) as ihour3,
MAX(case [HOUR_NBR] when 4 then [HOUR_CNT] else 0 end) as ihour4,
MAX(case [HOUR_NBR] when 5 then [HOUR_CNT] else 0 end) as ihour5,
MAX(case [HOUR_NBR] when 6 then [HOUR_CNT] else 0 end) as ihour6,
MAX(case [HOUR_NBR] when 7 then [HOUR_CNT] else 0 end) as ihour7,
MAX(case [HOUR_NBR] when 8 then [HOUR_CNT] else 0 end) as ihour8,
MAX(case [HOUR_NBR] when 9 then [HOUR_CNT] else 0 end) as ihour9,
MAX(case [HOUR_NBR] when 10 then [HOUR_CNT] else 0 end) as ihour10,
MAX(case [HOUR_NBR] when 11 then [HOUR_CNT] else 0 end) as ihour11,
max(OVERTIME_CNT) as iHourOT , 'L' as sType
FROM Log_Target lt(NoLock) join SCHEDULE_TARGET t ON t.TARGET_ID = lt.TARGET_ID 
join LOG_TARGET_HOUR th on th.LOG_TARGET_ID = lt.LOG_TARGET_ID 
join MFG_CONTXT m on m.MFG_CONTXT_NBR = t.MFG_CONTXT_NBR
     WHERE t.TARGET_ID not in ( select  t.TARGET_ID
FROM  Target_Exception te (NoLock) join SCHEDULE_TARGET t(NoLock)on t.TARGET_ID = te.TARGET_ID
WHERE iShift = @iShift
AND sProdDate = @sProdDate
AND sScheduleName = @sScheduleName )
AND iShift = @iShift
and m.MFG_CONTXT_ENABLED_FLAG = 1
AND TARGET_TYPE = 1   
AND ACTION_TYPE <> 'D'
AND Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101)  = @sProdDate
AND LOG_DT IN (Select Max(LOG_DT) From Log_Target Where sScheduleName = @sScheduleName
                                                               AND TARGET_TYPE = 1   
AND ACTION_TYPE <> 'D'
                                                               AND iShift = @iShift
                                                               AND Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101)  = @sProdDate)
group by t.TARGET_NM, sScheduleName, TARGET_DISPLAY_ORDER_NBR ) g
Order By TARGET_DISPLAY_ORDER_NBR

Answer 1

先帮你去掉一层:
SELECT sScheduleName, max(t.TARGET_ID) as sTargetID, 0 as sExceptTarget, @Downloaded as iDownloaded,
TARGET_NM as sTitle, TARGET_DISPLAY_ORDER_NBR,
SUM([HOUR_CNT])+max(OVERTIME_CNT) as iTotal,  
MAX(case [HOUR_NBR] when 1 then [HOUR_CNT] else 0 end) as ihour1,
MAX(case [HOUR_NBR] when 2 then [HOUR_CNT] else 0 end) as ihour2,
MAX(case [HOUR_NBR] when 3 then [HOUR_CNT] else 0 end) as ihour3,
MAX(case [HOUR_NBR] when 4 then [HOUR_CNT] else 0 end) as ihour4,
MAX(case [HOUR_NBR] when 5 then [HOUR_CNT] else 0 end) as ihour5,
MAX(case [HOUR_NBR] when 6 then [HOUR_CNT] else 0 end) as ihour6,
MAX(case [HOUR_NBR] when 7 then [HOUR_CNT] else 0 end) as ihour7,
MAX(case [HOUR_NBR] when 8 then [HOUR_CNT] else 0 end) as ihour8,
MAX(case [HOUR_NBR] when 9 then [HOUR_CNT] else 0 end) as ihour9,
MAX(case [HOUR_NBR] when 10 then [HOUR_CNT] else 0 end) as ihour10,
MAX(case [HOUR_NBR] when 11 then [HOUR_CNT] else 0 end) as ihour11,
max(OVERTIME_CNT) as iHourOT , 'E' as sType
FROM SCHEDULE_TARGET t(NoLock) INNER JOIN Target_exception te(NoLock) ON t.Target_ID = te.Target_ID 
join TARGET_HOUR_EXCEPTION th (nolock) on th.TARGET_ID = te.TARGET_ID and th.iShift =te.iShift and th.sProdDate = te.sProdDate
join MFG_CONTXT m on m.MFG_CONTXT_NBR = t.MFG_CONTXT_NBR
WHERE TARGET_ENABLED_FLAG = 1
AND sScheduleName = @sScheduleName
AND te.iShift = @iShift
AND Convert(nvarchar(10),Convert(datetime, th.sProdDate,101),101) = @sProdDate
and m.MFG_CONTXT_ENABLED_FLAG = 1 
group by TARGET_NM, sScheduleName, TARGET_DISPLAY_ORDER_NBR
union all
SELECT sScheduleName, max(t.TARGET_ID) as sTargetID, 1 as sExceptTarget, @Downloaded as iDownloaded,
t.TARGET_NM as sTitle,TARGET_DISPLAY_ORDER_NBR,
SUM([HOUR_CNT])+max(OVERTIME_CNT) as iTotal,  
MAX(case [HOUR_NBR] when 1 then [HOUR_CNT] else 0 end) as ihour1,
MAX(case [HOUR_NBR] when 2 then [HOUR_CNT] else 0 end) as ihour2,
MAX(case [HOUR_NBR] when 3 then [HOUR_CNT] else 0 end) as ihour3,
MAX(case [HOUR_NBR] when 4 then [HOUR_CNT] else 0 end) as ihour4,
MAX(case [HOUR_NBR] when 5 then [HOUR_CNT] else 0 end) as ihour5,
MAX(case [HOUR_NBR] when 6 then [HOUR_CNT] else 0 end) as ihour6,
MAX(case [HOUR_NBR] when 7 then [HOUR_CNT] else 0 end) as ihour7,
MAX(case [HOUR_NBR] when 8 then [HOUR_CNT] else 0 end) as ihour8,
MAX(case [HOUR_NBR] when 9 then [HOUR_CNT] else 0 end) as ihour9,
MAX(case [HOUR_NBR] when 10 then [HOUR_CNT] else 0 end) as ihour10,
MAX(case [HOUR_NBR] when 11 then [HOUR_CNT] else 0 end) as ihour11,
max(OVERTIME_CNT) as iHourOT , 'L' as sType
FROM Log_Target lt(NoLock) join SCHEDULE_TARGET t ON t.TARGET_ID = lt.TARGET_ID  
join LOG_TARGET_HOUR th on th.LOG_TARGET_ID = lt.LOG_TARGET_ID  
join MFG_CONTXT m on m.MFG_CONTXT_NBR = t.MFG_CONTXT_NBR
WHERE t.TARGET_ID not in ( select t.TARGET_ID
FROM Target_Exception te (NoLock) join SCHEDULE_TARGET t(NoLock)on t.TARGET_ID = te.TARGET_ID
WHERE iShift = @iShift
AND sProdDate = @sProdDate
AND sScheduleName = @sScheduleName )
AND iShift = @iShift
and m.MFG_CONTXT_ENABLED_FLAG = 1
AND TARGET_TYPE = 1   
AND ACTION_TYPE <> 'D'
AND Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101) = @sProdDate
AND LOG_DT IN (Select Max(LOG_DT) From Log_Target Where sScheduleName = @sScheduleName
AND TARGET_TYPE = 1   
AND ACTION_TYPE <> 'D'
AND iShift = @iShift
AND Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101) = @sProdDate)
group by t.TARGET_NM, sScheduleName, TARGET_DISPLAY_ORDER_NBR
Order By TARGET_DISPLAY_ORDER_NBR

Answer 2

貌似这儿有点毛病:
AND LOG_DT IN (Select Max(LOG_DT) From Log_Target Where sScheduleName = @sScheduleName
AND TARGET_TYPE = 1   
AND ACTION_TYPE <> 'D'
AND iShift = @iShift
AND Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101) = @sProdDate)既然是从同一个表中弄出来的,条件又相同,是不是可以改成:
AND not exists(select 1 from Log_Target where log_dt>lt.log_dt)

Answer 3

qianjin036a 高人啊，谢谢， 现在降到3分钟了还有无建议？ 
临时表我最后再考虑吧，涉及的代码太多了，改起来项目时间不允许

Answer 4

t.TARGET_ID not in ( select t.TARGET_ID
FROM Target_Exception te (NoLock) join SCHEDULE_TARGET t(NoLock)on t.TARGET_ID = te.TARGET_ID
WHERE iShift = @iShift
AND sProdDate = @sProdDate
AND sScheduleName = @sScheduleName )改成
not exists (select 1 from Target_Exception te where t.TARGET_ID=te.target_id
and iShift = @iShift
AND sProdDate = @sProdDate
AND sScheduleName = @sScheduleName )
不知道对不对还有
Convert(nvarchar(10),Convert(datetime, sProdDate ,101),101) = @sProdDate
这个有必要这么转吗？
直接在开头把@sProdDate在开头转成和sProdDate字段一样的格式就行了。