千万级通用的分页存储过程
2008年11月28日 星期五 14:41
/*
经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：
exec GetRecordFromPage news,newsid,10,100000
news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*/
/*
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
*/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
GO
第二个方法：
/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
*/
/*--调用示例
exec p_show '地区资料'
exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/
/*
因为要顾及通用性,所以对带排序的查询语句有一定要求.如果先排序,再出结果.就是:
exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码','地区名称'
--查询语句加上:top 100 percent //top时
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
程序代码调用
SqlConnection con=com.sqlcon;
con.Open();
SqlCommand comm=new SqlCommand();
comm.CommandType=CommandType.StoredProcedure;
comm.CommandText="p_show";
comm.Connection=con;
comm.Parameters.Add("@QueryStr",System.Data.SqlDbType.VarChar,4000).Value="select * from web where 仓库编号 like '%"+this.DDLType.SelectedValue.Trim()+"%' and 名称 like '%"+this.txtName.Text.Trim()+"%' and 数量>"+Convert.ToInt32(this.txtQty.Text.Trim());
comm.Parameters.Add("@FdShow",System.Data.SqlDbType.VarChar,4000).Value="编号,名称,数量";
comm.Parameters.Add("@PageSize",System.Data.SqlDbType.Int).Value=this.pagesize;
comm.Parameters.Add("@PageCurrent",System.Data.SqlDbType.Int).Value=Convert.ToInt32(ViewState["pageindex"]);
comm.Parameters.Add("@FdOrder",System.Data.SqlDbType.VarChar,1000).Value="";
SqlDataAdapter ada=new SqlDataAdapter(comm);
DataSet ds=new DataSet();
ada.Fill(ds,"temp");
this.DataGrid1.DataSource=ds.Tables["temp"].DefaultView;
this.DataGrid1.DataBind();
con.Close();
相关推荐
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
### 通用分页存储过程知识点解析 #### 一、存储过程概述 在数据库管理系统中，存储过程是一种预先编译并存储在数据库中的SQL程序。它能够接收参数，并根据这些参数执行复杂的逻辑操作，如数据查询、更新等。通过...
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
根据提供的文件信息，我们可以了解到这段代码是用于创建一个SQL Server中的通用分页存储过程，名为`pagination3`。此存储过程旨在提供一种灵活且高效的方式来处理数据库查询中的分页问题，使得开发人员能够轻松地在...
本通用分页存储过程经过我的验证，可以使用，支持联表查询，支持单表查询，效率我没测过太多，应该还可以。有需要的拿去用。 如果测试出效率，我们可以交流交流
SQL 通用 分页 存储 过程 完整代码
知识点：通用分页存储过程（支持多表） 一、存储过程概述 在数据库管理系统中，存储过程是一种预先编译并存储在数据库中的SQL程序。它能够接收参数，执行一系列操作，如查询、更新数据，并返回结果。存储过程提高...
"Oracle通用分页存储过程"就是为了解决这个问题而设计的。分页存储过程通常包含一系列SQL语句和逻辑，用于从数据库中按指定条件获取特定页码的数据。 在Oracle数据库中，实现分页查询通常涉及到`ROWNUM`伪列或者`...
web中通用分页存储过程，是根据PageIndex来取得相应的页数据，并不是上下翻页，对单字段，多字段均适用，并在百万级数据上测试，分页速度较好。
通用分页存储过程 MSSQL通用分页存储过程
### 2分法-通用分页存储过程 #### 概述 本文档旨在解析一个名为“2分法-通用分页存储过程”的SQL脚本，该脚本采用Top Max模式来实现高效的数据分页功能。这种方法特别适用于处理大量数据（如十万条以上记录）时的...
SQL Server 2000下的通用分页存储过程.sql
效率极高的通用分页存储过程，支持夺表关联。
Sql Server BootStrap Table 分页 通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
"SQL通用分页存储过程"就是一种专门用于实现分页查询的存储过程，它可以接收用户输入的参数，灵活地返回指定页码的数据。 首先，我们需要理解分页的基本概念。在数据库查询中，如果一次性获取所有数据可能导致内存...
本压缩包"SQL Server 2005通用分页存储过程.rar"包含了一个名为"SQL Server 2005通用分页存储过程.mht"的文件，很可能是详细讲解如何创建和使用分页存储过程的文档。 首先，我们要理解什么是存储过程。存储过程是预...
以下是一个通用分页存储过程的概述： 1. **参数定义**：存储过程需要接收两个主要参数，一个是当前页数（@CurrentPage），另一个是每页记录数（@PageSize）。有时，还需要一个总记录数参数（@TotalRows），用于返回...
通用分页存储过程`UP_GetRecordByPage`就是为了实现这一目标而设计的。这个存储过程允许开发人员根据需要获取数据库表中的指定页数据，而不必为每个表或每个查询编写特定的分页代码。 存储过程是预编译的SQL语句...