利用自定义分页技术提高数据库性能[转]

转自：http://dotnet.aspx.cc/article/108b1516-53ce-4357-b061-17295af9689f/read.aspx

 

     Web应用程序是显示数据库中数据的一个非常好的方法，通过它，你可以把业务复杂，并有访问和安全规则的数据库数据以一种简单、直观的方式向用户提供查询和更新的功能。用户判断数据库应用程序一个很常用的标准就是处理数据的快慢。许多Web页面都向用户提供了多种可搜索的列表显示来有效地定位记录的位置，一个比较简单而且常用的例子就是在线图书查询系统，它允许用户可以按作者，按书名或者按主题来检索图书信息。
      ASP.NET提供了一个DataGrid控件可以比以前的ASP方便地创建创建数据列表，DataGrid控件除了内建的数据表现和方法之外，还允许用户自己定义表现形式。分页技术为用户可管理的数据查找提供方便。DataGrid内建的分页技术很容易实现，但数据量很大时，它的方便性是以牺牲性能为代价的。下面，我们就看看如何通过自定义的分页方法来实现快速处理大量数据的结果集的办法。我们这里讨论的方法比DataGrid的默认分页方法是快速和更加有效的，这是因为每次请求不需要把全部的数据结果发送到Web服务器。相反，它只需要发送每个页面需要的那些数据集。例如：如果一个用户只要求100个页面中每页显示25条记录的第4页的结果集，服务器只需要发送第75－100行的数据即可，而不是1－1000行的完全数据。默认的传送方式如图1所示：

      从图中可以看出，DataGrid的内建分页方法是效率不高的，每次请求都必须把整个查询结果发送给Web服务器，Web服务器再把数据分成相应的页面。利用DataGrid的内建的分页方法尽管是很简单的，但是，由于Web应用的无序性特征，一个用户每次从一个页面转向另外一个页面时，DataGrid对象都被销毁并重新创建，这就意味着数据库服务器每次都必须发送全部的结果集。
      自定义的分页方法只返回所要检索的那些结果集，如下图2所示：

      从上面的图中可以看到，数据库每次只需要返回所要显示的数据记录。首先，我们在数据库中建立一个存储过程，并有两个输入参数，分别是要返回数据的第一条记录数和最后一条记录数，在SQL Server7.0以上的版本中，都有一个top关键字限制返回到结果集中的前多少条记录数，然而不幸的是，没有一个方法可以返回中间一部分的数据，例如第75条记录到100条记录的数据。Oracle中有一个rownum()的扩展函数可以返回中间的记录，比如："select * form Authors where Author_Last_Name = 'Anderson' and rownum() >=75 and rownum() <= 100"。然而，由于Oracle是在排序之前指定rownum的值，因此，这样的查询"select * from Authors where rownum <= 25 order by Author_Last_Name"将得不到我们期望的结果。我们下面所讲的方法是针对SQL Server的，但这里的概念对适合Oracle开发人员也是适用的。
      要创建一个返回指定条记录结果的存储过程，首先必须指定返回结果集的条记录数，可以用临时表，也可以用table变量（SQL Server 2000），两个在性能上没有太大的差别，但是，table变量是存储在内存中的，如果你的服务器内存不多的话，可以考虑用临时表，临时表使用硬盘存储结果，临时表需要手工释放对象，而table变量在存储过程结束后自动释放。

      下面就是我们要创建的存储过程：

create proc GetAuthors
@Author_Last_Name as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS

---- 建立有标识符列的table变量
declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[Author_Last_Name] [varchar] (40) ,
[Author_First_Name] [varchar] (20) ,
[phone] [char] (12) ,
[address] [varchar] (40) ,
[city] [varchar] (20) ,
[state] [char] (2) ,
[zip] [char] (5)
)

---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow

---- 插入到table变量中
insert @t_table
(
[Author_Last_Name],[Author_First_Name],[phone],[address],[city],[state],[zip]
)
SELECT [Author_Last_Name],[Author_First_Name],[phone],[address],[city],[state],[zip]

FROM authors
WHERE Author_Last_Name like '%' + @Author_Last_Name + '%'
ORDER BY Author_Last_Name

---- 返回到正确的结果
SELECT * FROM @t_table WHERE rownum >= @StartRow
ORDER BY rownum

GO

      参数@StartRow和@StopRow接收整数值，代表要返回的开始记录和结束记录，如果要在一个25条记录的页面中返回第4页，我们就可以设置@StartRow为76，@StopRow为100。我们在table变量@t_table中定义了一个叫rownum的整数类型的列，并指定为标识符列，这个列在我们这里介绍的分页技术中是很重要的，当我们插入数据时，这个列自动增加，它将在插入数据时起排序作用。SET ROWCOUNT语句对优化性能很关键，它告诉SQL Server进行限制要插入的数据，如果我们要76-100条记录之间的数据，那么就可以不必插入大于100条记录的数据。最后的SQL语句从@t_table的table变量选择rownum大于或者等于@StartRow的那些数据集，然后把它们返回到Web服务器，由Web服务器绑定到DataGrid对象。值得注意的是：如果要得到76到100条记录的数据，我们必须往table变量中插入100条记录的数据，这意味着：如果浏览者请求的页数越来越大，页面性能也会有所下降的。例如：要显示第100页的数据（从第2451条记录到第2500条记录），我们必须先向table变量或者临时表填充2500条记录，因此，性能依赖于你计算机的硬件和你要返回的记录数，有测试表明，在SQL Server 2000中使用这样的存储过程平均在200-250毫秒内返回第100页，而返回第一页只需要4毫秒。即使返回第500页的数据（从第12451到12500条记录）也可以在650到750毫秒内完成。应该说这种情况是很少见到的。 但为了减轻数据库和网络传输的压力，设计合理的查询结果页数是很见效的。

      现在，我们写好了一个存储过程来做分页的工作，而不是用Web服务器来做这个事情，我们接下来要做的就是为DataGrid对象编写代码来使用我们的分页技巧。DataGrid的AllowPaging、AllowCustomPaging、PageStyle属性有助于避免我们编写自己的代码来跟踪记录浏览者目前在哪一个页面访问和都请求过哪些页面。我们应当设定AllowCustomPaging为True，否则，在你使用DataReader或者SQLDataReader绑定到DataGrid对象会遇到麻烦。在任何可能的情况下，应当尽量使用SQLDataReader而不要使用DataSet来装载DataGrid对象。据性能测试表明：在构建列表显示数据时，使用SQLDataReader比使用DataSet要快两倍以上。不要设定AllowPaging和PageStyle的值，这是因为，如果使用这两个属性，你必须在viewstate中维护DataGrid，但为了追求性能最佳化，我们必须设定DataGrid的EnableViewState属性为false，尽管这样我们自己必须编写一点代码来实现我们的分页，但是性能会有所提高的，因为在每次与Web服务器打交道时不必再在viewstate中存储内容了。

      一旦我们关闭了DataGrid自己在viewstate中保存的能力，我们就必须自己编写代码来实现用户从一页导航到另一页。DataGrid如果自己不在viewstate中进行保存，那么它也不再跟踪记录“前一页”和“下一页”是哪些页面了。我们自己添加导航按钮来帮助浏览者进行导航。最简单的办法是在页面上增加两个按钮：“上一个”和“下一页”。要进入到下一页，我们在“下一页”按钮上增加click事件，通过我们的自定义分页存储过程请求相应的记录。例如：如果第一页由第1条到第25条记录组成，那么要导航到第二页，我们就向存储过程的@StartRow传递参数26，向@StopRow传递参数50即可，要返回到第一页，@StartRow和@StopRow分别为1和25。

       下面是使用VB.NET编写的“下一页”事件的例子：

Private Sub ButtonNext_Click (ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ButtonNext.Click

viewstate("StartRow") = viewstate("StartRow") + dgrid.PageSize
viewstate("StopRow") = viewstate("StartRow") + dgrid.PageSize

'运行存储过程，返回SQLDataReader
dgrid.DataSource = RunSprocReturnDR (textAu_lname.Text, _
textAu_fname.Text, viewstate("StartRow"),viewstate("StopRow"))
dgrid.DataBind()

End Sub

       从上面的例子可以看出，我们在viewstate中保存的只是@StartRow和@StopRow的信息，这比在viewstate中保存整个DataGrid对象高效的多。“下一页”和“上一页”按钮提供的只是简单的导航，要实现更详细的导航信息，比如：共多少页、自定义页面记录数等，也是可以的，但要记住不要使用DataGrid内建的PagingStyle属性。根据测试表明，不保存DataGrid会提高性能到54％。
      列表显示信息的性能对浏览者的访问是很重要的，设计不好的列表显示会大大降低应用程序的性能，不管它的后端数据库是多么快速。使用自定义分页技术，我们可以避免DataGrid默认分页机制带来的缺陷，如果要实现可搜索的列表显示，让你的用户感到你的应用程序快速和可扩展，还要编写更多的代码，相信各位会编写出更加优秀的程序的。