SQL分页的存储过程详解

ASP.Net中的DataGrid有内置分页功能,   但是它的默认的分页方式效率是很低的,特别是在数据量很大的时候,用它内置的分页功能几乎是不可能的事,因为它会把所有的数据从数据库读出来再进行分页,   这种只选取了一小部分而丢掉大部分的方法是不可去取的.         在最进的一个项目中因为一个管理页面要管理的数据量非常大,所以必须分页显示,并且不能用DataGrid的内置分页功能,于是自己实现分页.   下面介绍一下我在项目中用到的分页方法.         当然显示控件还是用DataGrid的,   因为数据绑定很方便^_^.           要保证不传输冗余的数据,那么必须在数据库中数据读取时实现分页,   数据库的分页操作可以放在存储过程中.   看了CSDN的一篇Blog中讲了一个百万级数据分页的存储过程的实现(http://blog.csdn.net/wellknow/posts/55167.aspx，他的这个方法可以根据不同情况进行适当的优化),   根据他的方法,这里实现一个简单的SQL语句来实现这里分页需要的存储过程。         create   procedure   ListProduct         (                       @PageIndex   int,   --   分页后需要页的序号                       @PageSize   int,   --   一页的大小                       @ConditionSQL   –   查询条件的SQL语句         )           AS   …   具体代码就不写了(可以参考上面的链接).                   具体的SQL语句如下:         Select   TOP   100   *   FROM   (select   *   from   product where   productid<200000)   T   Where   T.productid   NOT   IN           (Select   TOP   900   productid   FROM   (select   productid from   product   where   productid<200000)   T1   orDER   BY   T1.productid   asc)   orDER   BY   productid   asc           这条语句的   从总的商品(30万)中取出productid<200000(共20万),再按每页100的大小分页,然后取出第10页.                   Public   DataTable   ListProduct(int   pageIndex,   int   pageSize)         {                       //ADO.net从数据库中取出数据的代码就略过^_^.         }                             用上面的存储过程读出的数据在DataGrid里面分页,   必须把DataGrid的AllowPaging和AllowCustomPaging设置为true         protected   System.Web.UI.WebControls.DataGrid   ProductGrid;         ProductGrid.AllowPaging   =   true;         ProductGrid.AllowCustomPaging   =   true;                   然后在设置要显示的一页的大小         ProductGrid.PageSize   =   100;   //   在显示的时候依据实际的数据显示。         设置一页大小后,如果要让DataGrid实际分出页数来,还必须设置         ProductGrid.VirtualItemCount   =   GetProductCount()   ;   //   GetProductCount()   的功能是获取满足条件的产品数目,   这里的条件就是productid<200000.   设置这项属性后,那么这个DataGrid的页数就是         VirtualItemCount/PageSize,   也就是PageCount的值.   不能对PageCount直接赋值,因为他是只读的属性.         这些属性设置好后再绑定数据:         ProductGrid.DataSource   =   ListProduct(1,   ProductGrid.PageSize);   //   在Page_Load里面   pageIndex为1，记住判断IsPostBack，在IsPostBack为false时执行这些代码         ProductGrid.DataBind();                   这样数据绑定后就可以看到具有分页模样的页面了.但是还不能真正的分页.要实现真正的分页,还必须实现下面的功能.                   处理DataGrid的PageIndexChanged事件(处理用户新选中某页时的事件)         private   void   ProductGrid_PageIndexChanged(object   source,   System.Web.UI.WebControls.DataGridPageChangedEventArgs   e)         {                   //   如果在存储过程分页功能中用1表示第一页的序号的话那么这里必须用e.NewPageIndex+1作为pageIndex（如果选择了DataGrid上页号为3的页，那么e.NewPageIndex就为2）,   否则的话直接用e.NewPageIndex就可以了                   ProductGrid.DataSource   =   ListProduct(e.NewPageIndex+1,   ProductGrid.PageSize);   //   从数据库中读取新的数据                   ProductGrid.DataBind();                   //   设置当前的Page序号值,   如果不设置的话它是不会变得,   这样会给用户造成误解,以为所有页的数据相同。                   ProductGrid.CurrentPageIndex   =e.NewPageIndex;         }                   如果你处理了DataGrid的ItemCommand的事件的话，必须在ItemCommand事件处理代码前面加上这些代码:         if   (e.Item.ItemType   ==   ListItemType.Pager)         {                   return;         }         因为当PageIndexChanged事件激发，也就是用户选则了另外一页时会先激发ItemCommand事件，如果不这样处理的话，可能会遇到一些意想不到的情况（如果你确实需要的话也可以上面这段代码，不过最好实际测试一下）。                   整个过程完成后，再次浏览页面，觉得速度真是快多了。       在SQL Server中通过SQL语句实现分页查询     建立表： CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 插入数据：(2万条，用更多的数据测试会明显一些) SET IDENTITY_INSERT TestTable ON declare @i int set @i=1 while @i<=20000 begin insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX') set @i=@i+1 end SET IDENTITY_INSERT TestTable OFF ------------------------------------- 分页方案一：(利用Not In和SELECT TOP分页) 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID ------------------------------------- 分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID ------------------------------------- 分页方案三：(利用SQL的游标存储过程分页) create procedure XiaoZhengGe @sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off 其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 建议优化的时候，加上主键和索引，查询效率会提高。 通过SQL 查询分析器，显示比较：我的结论是: 分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句 分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用 在实际情况中，要具体分析。