用的aspnetpager分页存储过程，20000行，速度慢？而且无法 Sort?
选择某一城市下，某一种类 ，在某种排序下的 商家列表
总攻有20000行数据
每页 20行
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
insert into @indextable(nid) select Id from ShopInfo where Type=@Type AND City=@CityId
if(@SortId=1)
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 O.Rate desc
else if(@SortId=2)
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 CreateTime desc
else if(@SortId=3)
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 CommentNumber desc
else
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 ClickTimes desc
end
set nocount off
几个 order by 都不能排序 ？？？
[解决办法]
我试过了。这个存储过程性能不强。
大部分时间花在了 insert into @indextable(nid)上面了。
[解决办法]
create procedure GetShopsByCity
@Type tinyint,
@CityId smallint,
@SortId int,
@pageindex int,
@pagesize int
as
BEGIN
WITH ShopsListSplitPage(PageNumber,Id,[Name],Logo,Address,CommentNumber,FavoriteNumber,RecentId,RecentHead,RecentContent,Rate)
AS
(
SELECT
CEILING((ROW_NUMBER() OVER (ORDER BY SaleDate ASC))/@pagesize) AS PageNumber,
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
)
SELECT Id,[Name],Logo,Address,CommentNumber,FavoriteNumber,RecentId,RecentHead,RecentContent,Rate
FROM ShopsListSplitPage
WHERE PageNumber = @pageindex
END
