提高sql查询速率

Question

现在一个sql查询出来得要31秒，求问怎么提高效率，语句如下：

select e.emp_no,e.emp_cname,e.emp_ename,e.dept_code,b.[name], ISNULL(convert(nvarchar(20),EventsDateTime,120),'') cardtime
 from  [benq].Sky2004.dbo.AccessEvents a,employee e,[benq].Sky2004.dbo.VIEW_DeviceReader b where
a.deviceid=b.recnum and b.[name] like '%考勤%'
and a.personno=e.emp_no
and a.personno in(select c.emp_no from employee c where c.dept_code='2000' and c.active='Y')
AND (convert(nvarchar(20),EventsDateTime,112) BETWEEN '2012-08-01' AND '2012-08-21')
and e.active='Y'
order by  convert(nvarchar(10),EventsDateTime,112) desc,e.emp_no,EventsDateTime

Answer 1

创建索引，注意like的不要筹建索引

Answer 2

select  e.emp_no,e.emp_cname,e.emp_ename,e.dept_code,b.[name], ISNULL(convert(nvarchar(20),EventsDateTime,120),'') cardtime from   [benq].Sky2004.dbo.AccessEvents a,employee            [benq].Sky2004.dbo.VIEW_DeviceReader b where a.personno not exists (select 1  from employee c where c.dept_code='2000' and c.active='Y') AND (convert(nvarchar(20),EventsDateTime,112) BETWEEN '2012-08-01' AND '2012-08-21') and e.active='Y' and  b.[name] like '%考勤%'   and a.deviceid=b.recnum  and a.personno=e.emp_no order by convert(nvarchar(10),EventsDateTime,112) desc,e.emp_no,EventsDateTime  

Answer 3

创建在哪些列中好一些呢

Answer 4

@MikeAndy:  再试下 select  e.emp_no,e.emp_cname,e.emp_ename,e.dept_code,b.[name],ISNULL(convert(nvarchar(20),EventsDateTime,120),'') cardtime from   [benq].Sky2004.dbo.AccessEvents a,employee            [benq].Sky2004.dbo.VIEW_DeviceReader b where a.personno exists (select c.personno  from employee c where c.dept_code='2000' and c.active='Y') AND (convert(nvarchar(20),EventsDateTime,112) BETWEEN '2012-08-01' AND '2012-08-21') and e.active='Y' and  b.[name] like '%考勤%'   and a.deviceid=b.recnum  and a.personno=e.emp_no order by convert(nvarchar(10),EventsDateTime,112) desc,e.emp_no,EventsDateTime  

Answer 5

朋友,上面那个试下?我也学习下

Answer 6

有语法错误，等会哈

Answer 7

我也觉得没问题的，但是查询分析器一直报exists和 AND附近存在语法错误，在帮我看看吧，本人实在是小白

Answer 8

增加索引 然后不要 like %xx%