查询速度极慢! 才8000行数据而已!
存储过程如下:
CREATE procedure [GetShopsByCity]
(
@Type tinyint,
@CityId smallint,
@SortId int,
@pageindex int,
@pagesize int
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
if(@SortId=1)
insert into @indextable(nid) select Id from ShopsList where Type=@Type AND City=@CityId order by Rate desc
else if(@SortId=2)
insert into @indextable(nid) select Id from ShopInfo where Type=@Type AND City=@CityId order by CreateTime desc
else if(@SortId=3)
insert into @indextable(nid) select Id from ShopsList where Type=@Type AND City=@CityId order by CommentNumber desc
else
insert into @indextable(nid) select Id from ShopInfo where Type=@Type AND City=@CityId order by ClickTimes desc
select O.Id,O.[Name],O.Logo,O.Address,O.CommentNumber,O.FavoriteNumber,O.RecentId,O.RecentHead,O.RecentContent,
case
when Rate> =1 and Rate <1.25 then '1 '
when Rate> =1.25 and Rate <1.75 then '1.5 '
when Rate> =1.75 and Rate <2.25 then '2 '
when Rate> =2.25 and Rate <2.75 then '2.5 '
when Rate> =2.75 and Rate <3.25 then '3 '
when Rate> =3.25 and Rate <3.75 then '3.5 '
when Rate> =3.75 and Rate <4.25 then '4 '
when Rate> =4.25 and Rate <4.75 then '4.5 '
when Rate> =4.75 and Rate <=5 then '5 '
end as Rate
from ShopsList O,@indextable t where O.id=t.nid
and t.id> @PageLowerBound and t.id <=@PageUpperBound order by t.id
end
set nocount off
是不是这里 很慢了?where Type=@Type AND City=@CityId order by Rate desc
Rate 是在 试图里面的一个字段
[解决办法]
缺少相关的索引?
[解决办法]
有没有高手优化一下..
以供大家学习.````
.....
