分页存储过程 前面10页还好,到了100页 非常慢,超时。郁闷。怎么解决了?各位!附录:
CREATE procedure GetShopsByCity
(@Type TinyInt,
@CityId SmallInt,
@pagesize int,
@pageindex 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 order by avgrate desc
select O.Id,O.[Name],O.Phone,O.Address,O.CommentNumber,O.ClickTimes, Rate
from ShopsList O,@indextable t where O.Id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
CREATE procedure GetShopsByCity
(@Type TinyInt,
@CityId SmallInt,
@pagesize int,
@pageindex 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 order by avgrate desc
select O.Id,O.[Name],O.Phone,O.Address,O.CommentNumber,O.ClickTimes, Rate
from ShopsList O,@indextable t where O.Id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
pageindex:页码这个的效率还可以,你试试
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
'select b.id,c.name,c.phone,c.address,c.commentnumber,c.clicktimes,c.rate from
(select top ' + @pagesize + ' id from
(
select top ' convert(varchar,@pageindex * @pagesize) + ' id,avgrate from shopslist
where type=@type and cityid=@city
order by avgrate desc
)a
order by a.avgrate asc
)b inner join shopslist c on b.id=c.id
order by b.avgrate desc 'exec sp_executesql @sql,N'@type int,@cityid int',@type,@cityid试试看,我一直这么用,100w数据如果只显示id,第1w页大概要2秒左右,当然还要看你的索引建的是否合适。