数据差不多1000条左右 但是执行一次基本要5分钟 怎么优化啊·····select e.SubDeptName SubDept,e.[Name] UserName,
cast (convert(varchar(10),ISNULL(Enter,''),8)+'-'+convert(varchar(10),ISNULL(Leave,''),8) as nvarchar(20)) DayTime,
day(AtdDate) as M_Day from dbo.T_AttendData t
join HR_SERVER.hr_base.dbo.Employees e on t.EM_ID=e.EM_ID
where year(AtdDate)='2013' and MONTH(AtdDate)='1' and e.[DeptName] like '生产部'
执行计划 截图:
cast (convert(varchar(10),ISNULL(Enter,''),8)+'-'+convert(varchar(10),ISNULL(Leave,''),8) as nvarchar(20)) DayTime,
day(AtdDate) as M_Day from dbo.T_AttendData t
join HR_SERVER.hr_base.dbo.Employees e on t.EM_ID=e.EM_ID
where year(AtdDate)='2013' and MONTH(AtdDate)='1' and e.[DeptName] like '生产部'
执行计划 截图:
SELECT e.SubDeptName SubDept ,
e.[Name] UserName ,
CAST (CONVERT(VARCHAR(10), ISNULL(Enter, ''), 8) + '-'
+ CONVERT(VARCHAR(10), ISNULL(Leave, ''), 8) AS NVARCHAR(20)) DayTime ,
DAY(AtdDate) AS M_Day
FROM dbo.T_AttendData t --建一个 em_id,enter,leave,atddate的覆盖索引
JOIN HR_SERVER.hr_base.dbo.Employees e ON t.EM_ID = e.EM_ID
WHERE YEAR(AtdDate) = '2013'
AND MONTH(AtdDate) = '1' --同楼上,截取atddate '2013-01' 这样判断
AND e.[DeptName] LIKE '生产部' --这个地方用 = 替代like吧
AND MONTH(AtdDate) = '1' --同楼上,截取atddate '2013-01' 这样判断
AND e.[DeptName] LIKE '生产部' 显然需要改成:AtdDate > ='2013-01-01' and AtdDate <'2013-02-01' AND e.[DeptName] LIKE '生产部' 另外AtdDate要有索引。
但是差不到数据了···难道我写错了?···
DATEDIFF(yy,AtdDate,'2013-01-01')=0
改成这样试试看?另外你说的要运行5分钟,期间有没有BLOCKING?