求各位大侠帮忙解决上一个瓶颈

求各位大侠帮忙解决下一个瓶颈
为什么这个简单的多表连接耗时14秒了？而且查出来的数据才5000多条，不知道问题出在哪里
CM_Users_Tbl 主键UserID
CM_UserInfo_Tbl 外键UserID---对外键UserID加了索引

SQL code

select cu.flowers,(select  count(b.userid)+1 us  from  cm_users_tbl b WHERE  CU.uservotes<b.uservotes ) as rank,cu.UserID,cu.UserBBName,cu.UserPhoto,cu.UserVotes,gender=case cmi.gender when 1 then '男' else '女' end, Convert(varchar(20),datediff(Month,Birthday,getdate())/12) +'岁'+ Convert(varchar(20),datediff(Month,Birthday,getdate())%12) + '个月' as agefrom CM_Users_Tbl cu inner join CM_UserInfo_Tbl cmi on cmi.userid=cu.useridorder by cu.UserVotes desc

然后我把那两列的计算项去掉，换成在C#中处理，也是要14秒。

SQL code

select cu.flowers,(select  count(b.userid)+1 us  from  cm_users_tbl b WHERE  CU.uservotes<b.uservotes ) as rank,cu.UserID,cu.UserBBName,cu.UserPhoto,cu.UserVotes,cmi.gender Birthdayfrom CM_Users_Tbl cu inner join CM_UserInfo_Tbl cmi on cmi.userid=cu.useridorder by cu.UserVotes desc

不知道瓶颈在哪里，希望各位大侠帮忙下。

[解决办法]
(select count(b.userid)+1 us from cm_users_tbl b WHERE CU.uservotes<b.uservotes ) as rank,

这快应该是慢的原因，会做很多全表扫描
你ctrl + L 查看一下执行计划就知道了
[解决办法]
select cu.flowers,
(select count(b.userid)+1 us from cm_users_tbl b WHERE CU.uservotes<b.uservotes ) as rank,
cu.UserID,
cu.UserBBName,
cu.UserPhoto,
cu.UserVotes,
gender=case cmi.gender when 1 then '男' else '女' end, 
Convert(varchar(20),datediff(Month,Birthday,getdate())/12) +'岁'+ Convert(varchar(20),datediff(Month,Birthday,getdate())%12) + '个月' as age
from CM_Users_Tbl cu inner join CM_UserInfo_Tbl cmi on cmi.userid=cu.userid
order by cu.UserVotes desc




[解决办法]

SQL code

建议 在uservotes 和 userid 字段上创建索引。或者  创建一个包含索引 比如 create index idx_name on CM_Users_Tbl (uservotes desc,userid desc) include (flowers,UserBBName,UserPhoto)
[解决办法]
探讨

(select count(b.userid)+1 us from cm_users_tbl b WHERE CU.uservotes<b.uservotes ) as rank,

这快应该是慢的原因，会做很多全表扫描
你ctrl + L 查看一下执行计划就知道了

[解决办法]
(select count(b.userid)+1 us from cm_users_tbl b WHERE CU.uservotes<b.uservotes ) as rank,
count(b.userid)+1 us 这个中间怎么有个us,啥意思？不懂
[解决办法]
子查询(select count(b.userid)+1 us from cm_users_tbl b WHERE CU.uservotes<b.uservotes ) as rank,
是慢的主要原因
[解决办法]
你这索引假脱机 耗费了45%。
可以试一试 清理缓存，再试一试。
[解决办法]
1. CM_UserInfo_Tbl CM_Users_Tbl 两个表分别有多大数据量？
2.(select  count(b.userid)+1 us  from  cm_users_tbl b WHERE  CU.uservotes<b.uservotes ) as rank
  这个如果只是用来做排序，用row_number() 来实现比较合适
[解决办法]
cu.UserPhoto,
是否图片？

去掉这个字段试下。
 

 

查看更多 下一篇