用事件查看器查看了执行效率
发现这句话占用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
2005这里可以用排名函数
(
select
count(b.userid)+1 us
from
cm_users_tbl b WHERE CU.uservotes<b.uservotes ) 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不知道有没有理解错,你那个子查询太晕了[/code]
这个reads是什么意思呢?
是多少个人查看吗?
inner join cm_users_tbl d on e.userid=d.userid where d.userid=cu.UserID这句,生成一个静态的排名表,定期刷新。
如果要求实时排名敏感那很难搞了,你看CSDN的排名都是定时刷新的,不会实时给你显示排名。
运行速度快了20倍左右
请问下改动了哪里呢?
以后写sql语句有什么要注意的地方呢?
看一下sql语句也可以看出在干什么
今天看下了下服务器的cpu
每次都是90%在波动= =!好高!
有两个表一个是SH_Brand_tbl一个是SH_Gallery_Id
一个品牌可以发多个产品
然后我写了SELECT * FROM SH_Gallery_Tbl g inner join SH_Brand_Tbl b ON g.Brand_Id=b.Brand_Id
WHERE g.Gall_Id =
(SELECT MAX(Gall_Id)
FROM SH_Gallery_Tbl gg
WHERE gg.Brand_Id = g.Brand_Id) and cate_id=2为什么不对呢?
出来的行和我算的行不对!
要想知道写什么语句效率高该看什么sql的书?
可以推荐下不!