针对sql 2005优化的高性能分页存储过程

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了针对sql 2005优化的高性能分页存储过程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6297字，纯文字阅读大概需要9分钟。

内容图文

原文链接：http://www.cnblogs.com/xiao_haun/archive/2011/01/23/1942580.html 存储过程代码如下： ALTER?PROCEDURE?[dbo].[Pg_Paging]
@Tables?varchar(1000),?--表名,多红表是请使用?tA?a?inner?join?tB?b?On?a.AID?=?b.AID
@PK?varchar(100),????--主键，可以带表头?a.AID
@Sort?varchar(200)?=?'',?--排序字段
@PageNumber?int?=?1,????--开始页码
@PageSize?int?=?10,????????--页大小
@Fields?varchar(1000)?=?'*',--读取字段
@Filter?varchar(1000)?=?NULL,--Where条件
@Group?varchar(1000)?=?NULL,??--分组
@isCount?bit?=?0?????--1????--是否获得总记录数
AS
--
--select?*?from?GL_NEWS?order?by?GN_UPDATE_DATE?DESC
--exec?Pg_Paging?@Tables?=?'tb_NewsInfo',?@PK?=?'News_ID',?@Sort?=?'News_ID?DESC',?@PageNumber?=?2,?@PageSize?=?15,@Fields?=?'*',?@Group?=?'',?@isCount?=?0
DECLARE?@strFilter?varchar(2000)
declare?@sql?varchar(8000)
IF?@Filter?IS?NOT?NULL?AND?@Filter?!=?''
??BEGIN
???SET?@strFilter?=?'?WHERE?'?+?@Filter?+?'?'
??END
ELSE
??BEGIN
???SET?@strFilter?=?''
??END

if?@isCount?=?1?--只获得记录条数
????begin
????????set?@sql?=?'SELECT??Count(*)?FROM?'?+?@Tables?+?@strFilter??
????end
else
begin
if?@Sort?=?''
??set?@Sort?=?@PK?+?'?DESC?'

IF?@PageNumber?<?1
??SET?@PageNumber?=?1

if?@PageNumber?=?1?--第一页提高性能
begin?
??set?@sql?=?'select?top?'?+?str(@PageSize)?+'?'+@Fields+?'??from?'?+?@Tables?+?'?'?+?@strFilter?+?'?ORDER?BY??'+?@Sort
end?
else
??begin
??/**//**//**//*Execute?dynamic?query*/????
???DECLARE?@START_ID?varchar(50)
DECLARE?@END_ID?varchar(50)
SET?@START_ID?=?convert(varchar(50),(@PageNumber?-?1)?*?@PageSize?+?1)
SET?@END_ID?=?convert(varchar(50),@PageNumber?*?@PageSize)
????set?@sql?=??'?SELECT?'+@Fields+?'
???FROM?(SELECT?ROW_NUMBER()?OVER(ORDER?BY?'+@Sort+')?AS?rownum,?
?????'+@Fields+?'
??????FROM?' ' +@strFilter++@Tables+'')?AS?D
???WHERE?rownum?BETWEEN?'+@START_ID+'?AND?'?+@END_ID?+'?ORDER?BY?'+@Sort
??END

END
--print?@sql

EXEC(@sql)
可以通过封装一个静态函数来执行：（EnterpriseLibrary3。1）

using?System;
using?System.Data;
using?System.Data.Common;
using?System.Globalization;
using?System.Xml;
using?Microsoft.Practices.EnterpriseLibrary.Data;
using?Microsoft.Practices.EnterpriseLibrary.Data.Sql;
namespace?Glenet.EjiaShop.SqlData
{
????///?<summary>
????///??Pageing
????///?</summary>
????public?class?Pageing
????{
????????public?Pageing()
????????{
????????????//
????????????//?TODO:?在此处添加构造函数逻辑
????????????//
????????}

????????#region?Pg_Paging
????????///?<summary>
????????///?Pg_Paging
????????///?</summary>
????????///?<param?name="Tables"></param>
????????///?<param?name="PK"></param>
????????///?<param?name="Filter"></param>
????????///?<returns></returns>
????????public?static?int?Pg_PageCount(string?Tables,string?PK,string?Filter)
????????{
????????????//创建数据库实例
????????????Database?db?=?DatabaseFactory.CreateDatabase();
????????????//获得命令
????????????string?sqlCommand?=?"Pg_Paging";
????????????DbCommand?dbCommand?=?db.GetStoredProcCommand(sqlCommand);
????????????//设置参数
????????????db.AddInParameter(dbCommand,?"Tables",?DbType.String,?Tables);
????????????db.AddInParameter(dbCommand,?"PK",?DbType.String,?PK);
????????????db.AddInParameter(dbCommand,?"Sort",?DbType.String,?"");
????????????db.AddInParameter(dbCommand,?"PageNumber",?DbType.Double,?0);
????????????db.AddInParameter(dbCommand,?"PageSize",?DbType.Double,?0);
????????????db.AddInParameter(dbCommand,?"Fields",?DbType.String,?"*");
????????????db.AddInParameter(dbCommand,?"Filter",?DbType.String,?Filter);
????????????db.AddInParameter(dbCommand,?"Group",?DbType.String,?"");
????????????db.AddInParameter(dbCommand,?"isCount",?DbType.Boolean,?1);

????????????//执行
????????????return?int.Parse(db.ExecuteScalar(dbCommand).ToString());????
????????????//取得输出参数


????????}
????????#endregion

????????#region?Pg_Paging
????????///?<summary>
????????///?Pg_Paging
????????///?</summary>
????????///?<param?name="Tables"></param>
????????///?<param?name="PK"></param>
????????///?<param?name="Sort"></param>
????????///?<param?name="PageNumber"></param>
????????///?<param?name="PageSize"></param>
????????///?<param?name="Fields"></param>
????????///?<param?name="Filter"></param>
????????///?<param?name="Group"></param>
????????///?<returns></returns>
????????public?static?DataSet?Pg_Paging(string?Tables,string?PK,string?Sort,int?PageNumber,int?PageSize,string?Fields,string?Filter,string?Group)
????????{
????????????//创建数据库实例
????????????Database?db?=?DatabaseFactory.CreateDatabase();
????????????//获得命令
????????????string?sqlCommand?=?"Pg_Paging";
????????????DbCommand?dbCommand?=?db.GetStoredProcCommand(sqlCommand);
????????????//设置参数????????????
????????????db.AddInParameter(dbCommand,?"Tables",?DbType.String,?Tables);
????????????db.AddInParameter(dbCommand,?"PK",?DbType.String,?PK);
????????????db.AddInParameter(dbCommand,?"Sort",?DbType.String,?Sort);
????????????db.AddInParameter(dbCommand,?"PageNumber",?DbType.Double,?PageNumber);
????????????db.AddInParameter(dbCommand,?"PageSize",?DbType.Double,?PageSize);
????????????db.AddInParameter(dbCommand,?"Fields",?DbType.String,?Fields);
????????????db.AddInParameter(dbCommand,?"Filter",?DbType.String,?Filter);
????????????db.AddInParameter(dbCommand,?"Group",?DbType.String,?Group);
????????????db.AddInParameter(dbCommand,?"isCount",?DbType.Boolean,?0);

????????????//执行
????????????return?db.ExecuteDataSet(dbCommand);????
????????}
????????#endregion


????}

}
前台：调用如下：
string?filter?=?"??1?=?1?";

????????AspNetPager1.RecordCount?=?Glenet.EjiaShop.SqlData.Pageing.Pg_PageCount("tb_NewsInfo",?"News_ID",?filter);
????????using?(DataSet?ds?=?Glenet.EjiaShop.SqlData.Pageing.Pg_Paging("tb_NewsInfo",?"News_ID",?"News_AddTime?DESC",?AspNetPager1.CurrentPageIndex,?AspNetPager1.PageSize,?"*",?filter,?""))
????????{
????????????this.Re_ContentList.DataSource?=?ds.Tables[0].DefaultView;
????????????this.Re_ContentList.DataBind();

????????????AspNetPager1.CustomInfoText?=?"记录总数：<font?color=\"#00007f\"><b>"?+?AspNetPager1.RecordCount.ToString()?+?"</b></font>";
????????????AspNetPager1.CustomInfoText?+=?"?总页数：<font?color=\"#00007f\"><b>"?+?AspNetPager1.PageCount.ToString()?+?"</b></font>";
????????????AspNetPager1.CustomInfoText?+=?"?当前页：<font?color=\"red\"><b>"?+?AspNetPager1.CurrentPageIndex.ToString()?+?"</b></font>";
????????} 相当方便。 文章来自：http://www.cnblogs.com/edobnet/archive/2008/01/23/1049985.html

转载于:https://www.cnblogs.com/xiao_haun/archive/2011/01/23/1942580.html

内容总结

以上是互联网集市为您收集整理的针对sql 2005优化的高性能分页存储过程全部内容，希望文章能够帮你解决针对sql 2005优化的高性能分页存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---