千万级数据分页存储过程 asp.net页面调用分页全过程
下方有调用过程
从程序中考虑：比如用数据缓冲和连接池技术
从数据库服务器端考虑：比如csdn只选出前300行纪录，因为用户不需要那么多纪录。你也不必把1000万条全部分页。你也不必把纪录放在一个表中，一年（太多就一个月）的数据放一张表或定期把历史数据导出到数据数据仓库中，不要用一张表装1000万条记录。做好索引。优化查询！尽量少用模糊查询，避免表扫描。不能让客户端连续多次搜索（csdn就是）
千万级的数据库,有分页的意义吗?对于用户来说就是信息的海洋了,去看看Google怎么做的吧,
根本就不存在如何分页的问题,如果一个结果有千万条,那跟没有结果一样是没有一样.应该是统计条数,取出,前N条进行分页,如果到地还在准备分页的话,再次取结果n,进行分页.并且在其间推荐用户进行递进式搜索,来降低信息量,才式正确的
我有一个方法：（对于喜欢用select * 的同行可能有用）
先检索出来，符合条件的所有 id （唯一标识）放在内存里
然后根据当前页数和每页多少行纪录 去内存中取 id号的当前集
根据当前集 中的某一条 去数据库中检索相应的数据行
这里建议使用编译预处理PrepareStatement
将结果放进数据类 (如果你的数据只有两列当然可以省略这一步)
然后把数据类添加进 ArrayList 或者HashMap
然后 把载页面上输出 ArrayList 或者HashMap 的数据
这样做，是因为只检索一列数据 要比检索多类要快得多 占用更少的内存。
一次只精确定位一条纪录 要比一次取出多条要快，这里的瓶颈在于频繁的与数据库交互，但是我们使用编译预处理可以很大程度上提高性能。
千万数量级分页存储过程(可支持多表查询，任意排序)
功能不错的分页存储过程，可支持多表查询，任意排序
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200 ),--表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100 ),--主键，可以为空，但 @Order为空时该值不能为空
@Fields VARCHAR(200 ),--要取出的字段，可以是多个表的字段，可以为空，为空表示 select*
@PageSize INT,-- 每页记录数
@CurrentPage INT,-- 当前页，表示第页
@Filter VARCHAR(200 )='', --条件，可以为空，不用填 Where
@Group VARCHAR(200 )='', --分组依据，可以为空，不用填 groupby
@Order VARCHAR(200 )='', --排序，可以为空，为空默认按主键升序排列，不用填 orderby
@RecordCount int = 0 output
AS
BEGIN
DECLARE @SortColumn VARCHAR( 200)
DECLARE @Operator CHAR( 2)
DECLARE @SortTable VARCHAR( 200)
DECLARE @SortName VARCHAR( 200)
IF @Fields =''
SET @Fields ='*'
IF @Filter =''
SET @Filter ='Where 1=1'
ELSE
SET @Filter ='Where '+ @Filter
IF @Group <>''
SET @Group ='GROUPBY'+ @Group
IF @Order <>''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order =REPLACE( REPLACE( @Order,'asc' ,'ASC'), 'desc','DESC' )
IF CHARINDEX ('DESC', @Order)> 0
IF CHARINDEX ('ASC', @Order)> 0
BEGIN
IF CHARINDEX ('DESC', @Order)< CHARINDEX('ASC' , @Order )
SET @Operator ='<='
ELSE
SET @Operator ='>='
END
ELSE
SET @Operator ='<='
ELSE
SET @Operator ='>='
SET @SortColumn =REPLACE( REPLACE(REPLACE ( @Order,'ASC' ,''), 'DESC','' ),'', '')
SET @pos1 =CHARINDEX( ',', @SortColumn)
IF @pos1 >0
SET @SortColumn =SUBSTRING( @SortColumn, 1, @pos1-1 )
SET @pos2 =CHARINDEX( '.', @SortColumn)
IF @pos2 >0
BEGIN
SET @SortTable =SUBSTRING( @SortColumn, 1, @pos2-1 )
IF @pos1 >0
SET @SortName =SUBSTRING( @SortColumn, @pos2+ 1, @pos1- @pos2-1 )
ELSE
SET @SortName =SUBSTRING( @SortColumn, @pos2+ 1,LEN ( @SortColumn )- @pos2 )
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator ='>='
END
DECLARE @type VARCHAR( 50)
DECLARE @prec INT
Select @type =t. name, @prec=c .prec
FROM sysobjects o
JOIN syscolumns c on o .id= c.id
JOIN systypes t on c .xusertype= t.xusertype
Where o .name= @SortTable AND c .name= @SortName
IF CHARINDEX ('char', @type)> 0
SET @type = @type +'('+ CAST( @prec AS VARCHAR)+ ')'
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage +1
---查询某页数据
EXEC('DECLARE @SortColumnBegin ' + @type +' SET ROWCOUNT '+ @TopRows+ ' Select @SortColumnBegin='+ @SortColumn+ ' FROM '+ @TableNames+' ' + @Filter+' ' + @Group +' orDER BY '+ @Order+ ' SET ROWCOUNT '+ @PageSize+' Select '+ @Fields +' FROM '+ @TableNames+ ' '+ @Filter+' AND ' + @SortColumn+' ' + @Operator +' @SortColumnBegin '+ @Group+ ' orDER BY '+ @Order+'' )
---获取查询记录数
DECLARE @str_Count_SQL nvarchar( 500)
SET @str_Count_SQL = 'SELECT @TotalCount=count(*) FROM ' + @TableNames + ' '+ @Filter
EXEC sp_executesql @str_Count_SQL, N'@TotalCount int=0 output',@RecordCount output
END
GO
--调用例子：
--1.单表/单排序
EXECusp_PagingLarge'bigtable','d_id','d_id,d_title,d_c on tent,d_time',20,1,'','','d_iddesc'
--2.单表/多排序
EXECusp_PagingLarge'bigtable','d_id','*',20,0,'','','d_timeasc,d_iddesc'
--3.多表/单排序
EXEC usp_PagingLarge'bigtableleftJOIN bigtable_author on bigtable.d_id=bigtable_author.BigTable_id','bigtable.d_id','bigtable.d_id,bigtable.d_title,bigtable.d_c on tent,bigtable.d_time,bigtable_author.d_author',20,0,'','','bigtable.d_idasc'
--4.多表/多排序
EXEC usp_PagingLarge'bigtableleftJOIN bigtable_author on bigtable.d_id=bigtable_author.BigTable_id','bigtable.d_id','bigtable.d_id,bigtable.d_title,bigtable.d_c on tent,bigtable.d_time,bigtable_author.d_author',20,0,'','','bigtable.d_timeasc,bigtable.d_iddesc'
前台：
添加AspNetPager DLL文件
引用<% @ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<tfoot >
<tr>
<td>
<webdiyer: AspNetPager ID ="apList" runat ="server" PageSize ="25" CssClass ="pages" CurrentPageButtonClass ="cur"
OnPageChanging="apList_PageChanging" ShowCustomInfoSection="Left" CustomInfoHTML="%CurrentPageIndex%/%PageCount% ?%RecordCount%">
</webdiyer: AspNetPager>
</td>
</tr>
</tfoot>
后台调用：int rowCount = 0;
DataTable dt = _PageHelperBLL.GetListOrder( "v_Line_EventData", "ID" , "ID, TBID,TBName,C_QQ, CityID, CityName, LineName, MemberLevel, State, TeHuiAddTime, TeHuiType, AuditState", apList.PageSize, apList.CurrentPageIndex-1, sb.ToString(), "" , "TeHuiAddTime DESC", out rowCount);
apList.RecordCount = rowCount;
this.apList.CustomInfoHTML = string .Format("当???前??第???{0}/{1}页?3 共2{2}条??记?录? 每?页?3{3}条??", new object [] { this.apList.CurrentPageIndex, this.apList.PageCount, this.apList.RecordCount, this .apList.PageSize });
/// <summary>
/// 通???用??分??页?3存??储???过y程??(带??事??务?)可??排?序??
/// </summary>
/// <param name="tableName"> 表???名?</param>
/// <param name="pk"> 主??键??</param>
/// <param name="fields"> 显?示??字??段? </param>
/// <param name="pageSize"> 每?页?3数?y量?? </param>
/// <param name="pageIndex"> 当???前??页?3</param>
/// <param name="where"> WHERE语??句?</param>
/// <param name="group"> GROUP语??句?</param>
/// <param name="sort"> 排?列?D字??段?，??如??果?多??表???需??要?a指?定??表???名? </param>
/// <param name="recordCount"> 返???还1的??总???记?录?数?y </param>
/// <returns></returns>
public DataTable GetListSort(string tableName, string pk, string fields, int pageSize, int pageIndex, string where, string group, string sort, IDbTransaction trans, out int recordCount)
{
return _PageHelperDAO.GetListSort(tableName, pk, fields, pageSize, pageIndex, where, group, sort, trans, out recordCount);
}
/// <summary>
/// 通???用??分??页?3存??储???过y程??(带??事??务?)可??排?序??
/// </summary>
/// <param name="tableName"> 表???名?</param>
/// <param name="pk"> 主??键??</param>
/// <param name="fields"> 显?示??字??段? </param>
/// <param name="pageSize"> 每?页?3数?y量?? </param>
/// <param name="pageIndex"> 当???前??页?3</param>
/// <param name="where"> WHERE语??句?</param>
/// <param name="group"> GROUP语??句?</param>
/// <param name="sort"> 排?列?D字??段?，??如??果?多??表???需??要?a指?定??表???名? </param>
/// <param name="recordCount"> 返???还1的??总???记?录?数?y </param>
/// <returns></returns>
public DataTable GetListOrder(string tableName, string pk, string fields, int pageSize, int pageIndex, string where, string group, string sort, out int recordCount)
{
return GetListSort(tableName, pk, fields, pageSize, pageIndex, where, group, sort, null, out recordCount);
}
/// <summary>
/// 数?y据Y库a连??接??字??符??串??
/// </summary>
internal string dbConnectionString = Tripc.Common. ConfigHelper.TripcConnectionString;
public DataTable GetListSort(string tableName, string pk, string fields, int pageSize, int pageIndex, string where, string group, string sort, IDbTransaction trans, out int recordCount)
{
recordCount = 0;
string spusp_PagingLarge = "usp_PagingLarge" ;
try
{
SqlParameter[] storedParams = SqlDbServiceParameterCache .GetSpParameterSet(dbConnectionString, spusp_PagingLarge);
storedParams[0].Value = tableName;
storedParams[1].Value = pk;
storedParams[2].Value = fields;
storedParams[3].Value = pageSize;
storedParams[4].Value = pageIndex;
storedParams[5].Value = where;
storedParams[6].Value = group;
storedParams[7].Value = sort;
storedParams[8].Value = recordCount;
DataSet ds = new DataSet();
if (trans == null )
ds = SqlHelper.ExecuteDataset(dbConnectionString, CommandType.StoredProcedure, spusp_PagingLarge, storedParams);
else
ds = SqlHelper.ExecuteDataset((SqlTransaction )trans, CommandType.StoredProcedure, spusp_PagingLarge, storedParams);
if (ds.Tables.Count == 0)
return new DataTable();
recordCount = Utility.ToInt(storedParams[8].Value.ToString(), 0);
return ds.Tables[0];
}
catch (Exception ex)
{
Log.Error(String .Format("通???用??分??布?存??储???过y程??，??参?数?y：?odbConnectionString:{1}、??tableName:{2}、??fields:{3}、??where:{4}、??group:{5}、??pk:{6}，??异???常??：?o{0}" ,
ex.Message, dbConnectionString, tableName, fields, where, group, pk));
return new DataTable();
}
}
