SQL查询速度很慢，找不到原因解决方案

SQL查询速度很慢，找不到原因
declare @tyear as int,@tmonth as int
set @tyear=2011
set @tmonth=10
select distinct left(convert(varchar,c.checktime,120),10) as mydate,u.userid,u.name,(select substring(CONVERT(varchar, min(checktime), 120 ),12,5) from checkinout where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as mintime,(select substring(CONVERT(varchar, max(checktime), 120 ),12,5) from checkinout where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as maxtime
from checkinout as c,userinfo as u where year(c.checktime)=@tyear and month(c.checktime)=@tmonth order by u.userid,mydate

查询速度很慢，请指正

[解决办法]
你自己写的这个排版自己愿意看吗？
[解决办法]

SQL code

declare @tyear as int,@tmonth as intset @tyear=2011set @tmonth=10select distinct left(convert(varchar,c.checktime,120),10) as mydate, u.userid,u.name, (select    substring(CONVERT(varchar, min(checktime), 120 ),12,5)   from    checkinout   where    left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10)   and    userid=u.userid) as mintime, (select    substring(CONVERT(varchar, max(checktime), 120 ),12,5)   from    checkinout where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10)    and userid=u.userid) as maxtimefrom   checkinout as c,userinfo as u where  year(c.checktime)=@tyear and  month(c.checktime)=@tmonth order by   u.userid,mydate
[解决办法]
建议：把子查询放临时表处理，你这里函数用得太多 即使加索引都失效。
不用DISTINCT 改成GROUP BY
[解决办法]
SQL code
--试试是不是这个结果和你的一样不select u.userid,u.name,convert(varchar(10),c.checktime,120) as mydate,CONVERT(varchar(5),min(c.checktime),108) as mintime,CONVERT(varchar(5),max(c.checktime),108) as mintimefrom checkinout c join userinfo u on (c.userid=u.userid) where c.checktime>=@tyear+@tmonth+'01' and c.checktime<dateadd(month,1,@tyear+@tmonth+'01')group by u.userid,u.name,convert(varchar(10),c.checktime,120)order by 1,3
[解决办法]
select u.userid,u.name,
convert(varchar(10),c.checktime,120) as mydate,
CONVERT(varchar(5),min(c.checktime),108) as mintime,
CONVERT(varchar(5),max(c.checktime),108) as mintime
from checkinout c join userinfo u on (c.userid=u.userid)  
where c.checktime>=@tyear+@tmonth+'01' and c.checktime<dateadd(month,1,@tyear+@tmonth+'01')
group by u.userid,u.name,convert(varchar(10),c.checktime,120)
order by 1,3

看看
[解决办法]
SQL code
declare @tyear as int ,@tmonth as intselect @tyear = 2011,@tmonth = 10select distinct        left(convert(varchar, c.checktime, 120), 10) as mydate ,        u.userid ,        u.name ,        ( select    substring(convert(varchar, min(checktime), 120), 12, 5)          from      checkinout          where     left(convert(varchar, checktime, 120), 10) = left(convert(varchar, c.checktime, 120),                                                              10)                    and userid = u.userid        ) as mintime ,        ( select    substring(convert(varchar, max(checktime), 120), 12, 5)          from      checkinout          where     left(convert(varchar, checktime, 120), 10) = left(convert(varchar, c.checktime, 120),                                                              10)                    and userid = u.userid        ) as maxtimefrom    checkinout as c ,userinfo as uwhere   year(c.checktime) = @tyear and month(c.checktime) = @tmonthorder by u.userid ,mydate/*left(convert(varchar, c.checktime, 120), 10)--等价于convert(varchar(10), c.checktime, 120)*/ 
 
[解决办法]
left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10)  


这个完全可以改成

convert(varchar(10),checktime,120)=convert(varchar(10),c.checktime,120)

甚至

checktime<dateadd(dd,1,c.checktime) and checktime>=c.checktime
[解决办法]
这个语句查得.........

你这儿
from checkinout as c,
userinfo as u  
where year(c.checktime)=@tyear and month(c.checktime)=@tmonth  

c,u 这两个表是什么关系?

如果没有确定关系,那就是全连接,既然是全连接,你上面 select 里要用 子查询干嘛,直接
select distinct  
left(convert(varchar,c.checktime,120),10) as mydate,
u.userid,u.name,
substring(CONVERT(varchar, min(checktime), 120 ),12,5) as mintime,
substring(CONVERT(varchar, max(checktime), 120 ),12,5) as maxtime

不就行了!
[解决办法]
SQL code
substring(convert(varchar, min(checktime), 120), 12, 5)--等价于select convert(varchar(5), min(checktime), 108)