用的aspnetpager分页存储过程，20000行，速度慢？而且无法 Sort?

Question

选择某一城市下，某一种类 ，在某种排序下的 商家列表
总攻有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 都不能排序 ？？？

Answer 1

//此方法原理需要哪部分记录则取得哪部分记录
ALTER PROCEDURE dbo.Pagination
(

@Currentpage int,//当前页
@Pagesize int,//页大小..即每页显示多少数据
@Totalrecords int output//你所有记录的总数
)
AS
create table #temp//创建临时表,以防止你表内标识列ID不连续,即PID可以标识
(
PID int identity(1,1),
ProductID int,
ModelName varchar(50),
UnitCost money
)

insert into #temp(ProductID,ModelName,UnitCost)
select ProductID,ModelName,UnitCost
from Products//把你表内的数据插入到临时表

declare @StartID int//定义起始ID
set @StartID=(@Currentpage-1)*@Pagesize+1

declare @EndID int//定义终止ID
set @EndID=@Currentpage*@Pagesize

select * from #temp where PID>=@StartID and PID<=@EndID//取得部分ID的数据,即你想所取的部分记录的ID


set @Totalrecords=(select count(*) from #temp)//输出记录总数

Answer 2

我试过了。这个存储过程性能不强。
大部分时间花在了 insert into @indextable(nid)上面了。

Answer 3

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

Answer 4

这一行得改改
CEILING((ROW_NUMBER() OVER (ORDER BY Rate ASC))/@pagesize) AS PageNumber,

Answer 5

关键是 8000行商家信息数据， 然后还有一张表 用户打分表，给每个商家打分，然后作了一张试图， 存储了每个商家的平均打分，现在是要取出 某一城市下，某一种类 ，按照平均打分排序 的 商家列表以前在 insert into @indextable(nid) select Id from ShopInfo where Type=@Type AND City=@CityId     order by rate desc 非常慢，现在改成我上面发的，但是发现，只能每页里面有排序，但是不是整个按打分排序，看来还要全部排序先，需要在试图上建立索引马？ 还是 修改本存储过程？

Answer 6

http://blog.csdn.net/jyk/archive/2007/03/01/1518354.aspx这里有一个算法你可以参考一下。

Answer 7

那个字段 现在是在 视图里面
  (SELECT AVG(Rate*1.0) FROM ShopComment WHERE ShopId=ShopInfo.Id) AS Rate,
怎么在商家表里面加呢？

Answer 8

根据这个排序  (SELECT AVG(Rate*1.0) FROM ShopComment WHERE ShopId=ShopInfo.Id) AS Rate到底怎么提高性能阿？

Answer 9

20000小意思啊，我测试了500000行都很快的。其实B/S的程序，SQL语句最好能做到能多简单就多简单。