SQL 查询超时，怎样优化？
SQL如下：
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),DATEADD(m,-1,@dt),3)+'- '+CONVERT(CHAR(12),(@dt-DAY(@dt)),3) AS Date,
(SELECT SUM(e.Confirmed_Hours) FROM dbo.Attendance_Reports e
WHERE a.Staff_ID=e.Staff_ID
AND e.EffectiveDate>=DATEADD(m,-1,@dt) AND e.EffectiveDate< @dt-DAY(@dt)+1) AS Hours,
(SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=DATEADD(m,-1,@dt) AND c.EffectiveDate< @dt-DAY(@dt)+1) AS Leave,
'PT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT TOP 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1
---------01/09-30/09 Date---------
UNION ALL
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),(@dt-DAY(@dt)+1),3)+'- '+CONVERT(CHAR(12),DATEADD(m,1,(@dt-DAY(@dt))),3) AS Date,
(SELECT FTCHTotal+PTCH0120 FROM dbo.SMW_Consolidate
WHERE [Month]=MONTH(@dt) AND [Year]=YEAR(@dt)
AND Staff_no=a.Staff_ID
AND Remark<>'Part Time') AS Hours,
CAST((SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type NOT LIKE 'H%' AND c.Day_Type<>'ND')+
(SELECT COUNT(c.Day_Type)*0.5 FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type LIKE 'H%') AS INT) AS Leave,
'FT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT top 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1
？谁知道怎么优化？详情？
[解决办法]
查一下是哪句超时了
[解决办法]
执行计划 i/o开销统计什么的都拿出来 还有就是该建的索引什么的 有木有？？
[解决办法]
一般情况下索引就可以解决问题
[解决办法]
这种你看计划都没用了，首先你这个代码都是一团乱，怎么看。
[解决办法]
一般复杂的查询性能不会好到哪里去。而且瞄了一下你的查询也太复杂了吧？貌似你的要求不应该有那么多代码吧？有些地方太多余了。你先简化查询吧。从集合的思路去考虑。然后再按照需要排序的那列搞个聚集索引。减少排序的开始。
