提高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_noand 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%