求高效分页方案

cdhome 2009-07-30 10:45:54
用的MSSQL2000 数据表数据是700000(会员发布的供求数据),需要分页并做复杂查询

分页方案分二种情况

当浏览第一页,分组并读取15条

SELECT top 15 a.tradeid From buyorsell a inner join (select max(tradeid) as tradeid from buyorsell where group by publicname ) b on a.tradeid=b.tradeid order by a.vip_typeA , a.PublicDate desc

当浏览第二页的时候

SELECT top 15 a.TradeID from BuyOrSell a
Where a.TradeID not in( SELECT top 15 lxla.TradeID From BuyOrSell lxla
Where tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by lxla.vip_typeA , lxla.PublicDate desc) and a.tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by a.vip_typeA , a.PublicDate desc


最开始在publicdate上建立了聚集索引,order by a.PublicDate desc能走到索引上, 但由改成了order by a.vip_typeA , a.PublicDate desc 就走不到索引上去了,性能下降了很多.大家进来看看,有什么方案解决这种分页问题.

条件是按order by a.vip_typeA , a.PublicDate desc排序,并分组读取(一个用户读取一条),一个表里有700000(会员发布的供求数据),(就像阿里巴巴的商机信息一样,每个会员只读取一条,并按时间和VIP会员排序,不知道他的数据分页方案是怎么样的?)
...全文
95 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
Q315054403 2009-08-01
  • 打赏
  • 举报
 回复
有偿解决，提供后续支持
zjybushiren88888 2009-07-31
  • 打赏
  • 举报
 回复 
/*分页存储过程

Descript:分页存储过程

Author:Blue.Dream

Date:2004-8-18 21:01

*/

Create  PROCEDURE ListPage(

@tblName     nvarchar(200),                 ----要显示的表或多个表的连接

@fldName     nvarchar(200) = '*',             ----要显示的字段列表

@pageSize    int = 10,                          ----每页显示的记录个数

@page        int = 1,                 ----要显示那一页的记录

@pageCount    int = 1 output,             ----查询结果分页后的总页数

@Counts    int = 1 output,                 ----查询到的记录数

@fldSort    nvarchar(100) = null,             ----排序字段列表或条件

@Sort        bit = 0,                 ----排序方法，0为升序，1为降序

@strCondition    nvarchar(200) = null,    ----查询条件,不需where

@ID        nvarchar(50)        ----主表的主键

)

AS

SET NOCOUNT ON

Declare @sqlTmp nvarchar(1000)        ----存放动态生成的SQL语句

Declare @strTmp nvarchar(1000)        ----存放取得查询结果总数的查询语句

Declare @strID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句

Declare @sqlSort nvarchar(200)        ----存放临时生成的排序条件

Declare @intCounts int            ----要移动的记录数

Declare @BeginID int            ----开始的ID

Declare @EndID   int            ----结束的ID



--------首先生成排序方法---------

if @Sort=0        --升序

begin

      if not(@fldSort is null)

        set @sqlSort = ' Order by ' + @fldSort    

      else

        set @sqlSort = ' Order by ' + @ID  

end

else            --降序

begin

    if not(@fldSort is null)

        set @sqlSort = ' Order by ' + @fldSort + ' DESC'        

    else

        set @sqlSort = ' Order by ' + @ID + ' DESC '

end





--------生成查询语句--------

--此处@strTmp为取得查询结果数量的语句

if @strCondition is null    --没有设置显示条件

    begin

    set @sqlTmp =  @fldName + ' From ' + @tblName

    set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName

    set @strID = ' From ' + @tblName

    end

else

    begin

    set @sqlTmp = + @fldName + 'From ' + @tblName + ' where ' + @strCondition

    set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition

    set @strID = ' From ' + @tblName + ' where ' + @strCondition

    end

    





----取得查询结果总数量-----

exec sp_executesql @strTmp,N'@Counts int out ',@Counts out



--取得分页总数

if @Counts <= @pageSize

    set @pageCount = 1

else

    set @pageCount = (@Counts / @pageSize) + 1





--计算要移动的记录数

if @page = 1 

    set @intCounts = @pageSize

else

begin

    set @intCounts = (@page-1) * @pageSize + 1

end



-----取得分页后此页的第一条记录的ID

set @strID = 'select @BeginID=' + @ID + ' ' + @strID



set @intCounts = @intCounts - @pageSize + 1

set rowcount  @intCounts

exec sp_executesql @strID,N'@BeginID int out ',@BeginID out



-----取得分页后此页的最后一条记录的ID

set @intCounts = @intCounts + @pageSize - 1

print @intCounts

set rowcount  @intCounts

exec sp_executesql @strID,N'@BeginID int out ',@EndID out





------恢复系统设置-----

set rowcount  0

SET NOCOUNT OFF



------返回查询结果-----

if @strCondition is null

    set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID + ' between ' + str(@BeginID) + ' and ' + str(@EndID)

else

    set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID +' (between ' + str(@BeginID) + ' and ' + str(@EndID) + ')  and ' + @strCondition



if not(@sqlSort is null)

set @strTmp = @strTmp + @sqlSort

exec sp_executesql @strTmp



GO
cdhome 2009-07-30
  • 打赏
  • 举报
 回复
这个好像没有分组显示功能
feixianxxx 2009-07-30
  • 打赏
  • 举报
 回复 
CREATE PROC sp_PageView

@tbname     sysname,             --要分页显示的表名

@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1,              --要显示的页码

@PageSize   int=10,              --每页的大小(记录数)

@FieldShow  nvarchar(1000)='',   --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder  nvarchar(1000)='',  --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序

@Where     nvarchar(1000)='',    --查询条件

@PageCount  int OUTPUT           --总页数

AS

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

	RAISERROR(N'对象"%s"不存在',1,16,@tbname)

	RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

	RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

	RETURN

END



--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

	RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

	RETURN

END



--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

	SET @FieldOrder=N''

ELSE

	SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

	SET @Where=N''

ELSE

	SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

	DECLARE @sql nvarchar(4000)

	SET @sql=N'SELECT @PageCount=COUNT(*)'

		+N' FROM '+@tbname

		+N' '+@Where

	EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

	SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END



--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

	@TopN1=@PageCurrent*@PageSize



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	--生成主键(惟一键)处理条件

	DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)

	SELECT @Where1=N'',@s=@FieldKey

	WHILE CHARINDEX(N',',@s)>0

		SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

			@Where1=@Where1

				+N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)

				+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)

	SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),

		@TopN=@TopN1-@PageSize



	--执行查询

	EXEC(N'SET ROWCOUNT '+@TopN1

		+N' SELECT '+@FieldKey

		+N' INTO # FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder

		+N' SET ROWCOUNT '+@TopN

		+N' DELETE FROM #'

		+N' SELECT '+@FieldShow

		+N' FROM '+@tbname

		+N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1

		+N') '+@FieldOrder)

END
feixianxxx 2009-07-30
  • 打赏
  • 举报
 回复 
CREATE PROC sp_PageView

@tbname     sysname,           	--要分页显示的表名

@FieldKey   sysname,           	--用于定位记录的主键(惟一键)字段,只能是单个字段

@PageCurrent int=1,             	--要显示的页码

@PageSize   int=10,            	--每页的大小(记录数)

@FieldShow  nvarchar(1000)='',  	--以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder  nvarchar(1000)='', 	--以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

                                          用于指定排序顺序

@Where     nvarchar(1000)='',  	--查询条件

@PageCount  int OUTPUT        	--总页数

AS

DECLARE @sql nvarchar(4000)

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

	RAISERROR(N'对象"%s"不存在',1,16,@tbname)

	RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

	RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

	RETURN

END



--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

	RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

	RETURN

END



--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

	SET @FieldOrder=N''

ELSE

	SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

	SET @Where=N''

ELSE

	SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

	SET @sql=N'SELECT @PageCount=COUNT(*)'

		+N' FROM '+@tbname

		+N' '+@Where

	EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

	SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END



--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

	@TopN1=@PageCurrent*@PageSize	



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	SELECT @PageCurrent=@TopN1,

		@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN

			+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey

			+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname

			+N' '+@Where

			+N' '+@FieldOrder

	SET ROWCOUNT @PageCurrent

	EXEC sp_executesql @sql,

		N'@n int,@s nvarchar(4000) OUTPUT',

		@PageCurrent,@sql OUTPUT

	SET ROWCOUNT 0

	IF @sql=N''

		EXEC(N'SELECT TOP 0'

			+N' '+@FieldShow

			+N' FROM '+@tbname)

	ELSE

	BEGIN

		SET @sql=STUFF(@sql,1,1,N'')		

		--执行查询

		EXEC(N'SELECT TOP '+@TopN

			+N' '+@FieldShow

			+N' FROM '+@tbname

			+N' WHERE '+@FieldKey

			+N' IN('+@sql

			+N') '+@FieldOrder)

	END

END
feixianxxx 2009-07-30
  • 打赏
  • 举报
 回复 
CREATE PROC sp_PageView

@tbname     sysname,           	--要分页显示的表名

@FieldKey   nvarchar(1000),  	--用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1,           	--要显示的页码

@PageSize   int=10,            	--每页的大小(记录数)

@FieldShow nvarchar(1000)='',  	--以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(1000)='',  	--以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

                                          用于指定排序顺序

@Where    nvarchar(1000)='', 	--查询条件

@PageCount int OUTPUT         	--总页数

AS

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

	RAISERROR(N'对象"%s"不存在',1,16,@tbname)

	RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

	RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

	RETURN

END



--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

	RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

	RETURN

END



--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

	SET @FieldOrder=N''

ELSE

	SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

	SET @Where=N''

ELSE

	SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

	DECLARE @sql nvarchar(4000)

	SET @sql=N'SELECT @PageCount=COUNT(*)'

		+N' FROM '+@tbname

		+N' '+@Where

	EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

	SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END



--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

	@TopN1=(@PageCurrent-1)*@PageSize



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	--处理别名

	IF @FieldShow=N'*'

		SET @FieldShow=N'a.*'



	--生成主键(惟一键)处理条件

	DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),

		@s nvarchar(1000),@Field sysname

	SELECT @Where1=N'',@Where2=N'',@s=@FieldKey

	WHILE CHARINDEX(N',',@s)>0

		SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),

			@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

			@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,

			@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',

			@Where=REPLACE(@Where,@Field,N'a.'+@Field),

			@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),

			@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)

	SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),

		@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),

		@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),

		@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),	

		@Where2=CASE

			WHEN @Where='' THEN N'WHERE ('

			ELSE @Where+N' AND ('

			END+N'b.'+@s+N' IS NULL'+@Where2+N')'



	--执行查询

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' a LEFT JOIN(SELECT TOP '+@TopN1

		+N' '+@FieldKey

		+N' FROM '+@tbname

		+N' a '+@Where

		+N' '+@FieldOrder

		+N')b ON '+@Where1

		+N' '+@Where2

		+N' '+@FieldOrder)

END
MySQL高效分页解决方案集分享
介绍了MySQL高效分页解决方案集，有需要的朋友可以参考一下
真正高效的SQLSERVER分页查询(多种方案)
Sqlserver数据库分页查询一直是Sqlserver的短板,分页查询每页30条，查询第1500页(即第45001-45030条数据)进行测试，分析出最高效的查询，感兴趣的朋友可以参考下哈
JFinal专题之分页原理与解决方案
JFinal分页原理视频教程，该课程详细介绍数据库分页原理，自己动手封装前端分页组件，然后介绍第三方的js分页组件，集成jquery.Pagination插件。
Jsp.rar_jsp 分页_jsp分页_索引 JavaScript
高效的jsp分页查询，方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 建议优化的时候，加上主键和索引，查询效率会提高。
PagingSampleProject：示例项目，以显示如何为Asp.Net Core Web项目构建高效的分页系统
PagingSampleProject 示例项目，说明如何为Asp.Net Core Web项目构建高效的分页系统 如何 该项目已准备就绪，可以运行，无需其他步骤即可运行该示例。 只需打开解决方案，构建并运行即可。 参考 该项目使用进行分页控制。 初学者逐步说明
新技术前沿

6,129

社区成员

4,460

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 新技术前沿
社区管理员
  • 新技术前沿社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章