用事件查看器查看了执行效率
发现这句话占用cpu特别多!有时飙到500多!
准备放到视图里面去,可是视图里又不能传参数。
大家对这句sql有什么优化的地方呢?
大家帮帮忙select top 4 cu.UserBBName,cu.UserPhoto,cu.UserVotes,(select e.us from (select a.userid,count(b.userid)+1 us from cm_users_tbl a left join cm_users_tbl b on a.uservotes<b.uservotes group by a.userid) e inner join cm_users_tbl d on e.userid=d.userid where d.userid=cu.UserID) as rank,gender=case cmu.gender when 1 then '男' else '女' end, Convert(varchar(20),datediff(Month,Birthday,getdate())/12) +'岁'+ Convert(varchar(20),datediff(Month,Birthday,getdate())%12) + '个月' as age,cv.UserId,convert(varchar(5),VisitAddtime,110)+' '+convert(varchar(5),VisitAddtime,114) as VisitAddtime,VisitUserName,VisitUserId,VisitPhoto from CM_Visitors_Tbl cv inner join cm_userinfo_tbl cmu on cmu.userid=cv.VisitUserId inner join cm_users_tbl cu on cu.userid=cv.VisitUserId where cv.UserID=1972 order by VisitAddtime desc
发现这句话占用cpu特别多!有时飙到500多!
准备放到视图里面去,可是视图里又不能传参数。
大家对这句sql有什么优化的地方呢?
大家帮帮忙select top 4 cu.UserBBName,cu.UserPhoto,cu.UserVotes,(select e.us from (select a.userid,count(b.userid)+1 us from cm_users_tbl a left join cm_users_tbl b on a.uservotes<b.uservotes group by a.userid) e inner join cm_users_tbl d on e.userid=d.userid where d.userid=cu.UserID) as rank,gender=case cmu.gender when 1 then '男' else '女' end, Convert(varchar(20),datediff(Month,Birthday,getdate())/12) +'岁'+ Convert(varchar(20),datediff(Month,Birthday,getdate())%12) + '个月' as age,cv.UserId,convert(varchar(5),VisitAddtime,110)+' '+convert(varchar(5),VisitAddtime,114) as VisitAddtime,VisitUserName,VisitUserId,VisitPhoto from CM_Visitors_Tbl cv inner join cm_userinfo_tbl cmu on cmu.userid=cv.VisitUserId inner join cm_users_tbl cu on cu.userid=cv.VisitUserId where cv.UserID=1972 order by VisitAddtime desc
select top 4 cu.UserBBName,cu.UserPhoto,cu.UserVotes,(select e.us from (select
a.userid,count(b.userid)+1 us from cm_users_tbl a left join cm_users_tbl b on
a.uservotes<b.uservotes group by a.userid) e inner join cm_users_tbl d on
e.userid=d.userid where d.userid=cu.UserID) as rank,gender=case cmu.gender when 1
then '男' else '女' end, Convert(varchar(20),datediff(Month,Birthday,getdate())/12)
+'岁'+ Convert(varchar(20),datediff(Month,Birthday,getdate())%12) + '个月' as
age,cv.UserId,convert(varchar(5),VisitAddtime,110)+' '+convert(varchar
(5),VisitAddtime,114) as VisitAddtime,VisitUserName,VisitUserId,VisitPhoto from
CM_Visitors_Tbl cv inner join cm_userinfo_tbl cmu on cmu.userid=cv.VisitUserId
inner join cm_users_tbl cu on cu.userid=cv.VisitUserId where cv.UserID=1972 order
by VisitAddtime desc
换行一下比较容易看!
加上Nolock试试
请楼主试试!给个回信!谢谢!