【续】用ObjectDataSource实现自定义分页的心得总结，几种分页方法的性能对比

      在《用ObjectDataSource实现自定义分页的心得总结》中，在SQL Server中使用双Top查询，后来用“事件探查器”进行性能对比，发现在SQL Server 2005中使用RowNumber函数的方法更有效；在SQL Server 2000中无法使用RowNumber函数，但是数据量较大时，双Top查询速度很慢，排序的字段若没有建立索引后果更加糟糕，其实在SQL Server 2000中，可以巧妙的使用SET ROWCOUNT和表变量来实现自定义分页，如下是一个参考示例：
      

利用表变量分页
 1CREATE PROCEDURE usp_PagedResults_New_Rowcount
 2(
 3   @startRowIndex int,
 4   @maximumRows int
 5)
 6AS
 7 
 8DECLARE @TempItems TABLE
 9(
10   ID int IDENTITY,
11   EmployeeID int
12)
13 
14DECLARE @maxRow int
15
16
17SET @maxRow = (@startRowIndex + @maximumRows) - 1 
18
19SET ROWCOUNT @maxRow
20
21INSERT INTO @TempItems (EmployeeID)
22SELECT EmployeeID
23FROM Employees
24ORDER BY EmployeeID
25
26SET ROWCOUNT @maximumRows
27
28SELECT e.*, d.[Name] as DepartmentName
29FROM @TempItems t
30   INNER JOIN Employees e ON
31       e.EmployeeID = t.EmployeeID
32   INNER JOIN Departments d ON
33       d.DepartmentID = e.DepartmentID
34WHERE ID >= @startRowIndex
35
36SET ROWCOUNT 0
37
38GO

在上面的代码中，如果EmployeeID是唯一的(一般在数据库设计的时候推荐在表中加一个自增列的字段来取代它)，可以不用表变量切性能可进一步得到优化，具体代码如下：

优化的分页程序
 1CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
 2(
 3    @startRowIndex int,
 4    @maximumRows int
 5)
 6AS
 7
 8DECLARE @first_id int, @startRow int
 9    
10--得到开始一笔记录的employeeID
11SET ROWCOUNT @startRowIndex
12SELECT @first_id = employeeID FROM employees ORDER BY employeeid
13
14----从开始一笔记录的employeeID取出数量等于每页的记录的结果集
15
16SET ROWCOUNT @maximumRows
17
18SELECT e.*, d.name as DepartmentName 
19FROM employees e
20   INNER JOIN Departments D ON
21       e.DepartmentID = d.DepartmentID
22WHERE employeeid >= @first_id
23ORDER BY e.EmployeeID
24
25SET ROWCOUNT 0
26
27GO 
28
29 
30

在SQL Server 2005和Oracle 9i后的版本中可以分别使用ROWNUMBER和ROWNUM来进行分页，并能获得较好的性能，以SQL Server 2005为例，一般的做法如下：

1SELECT 
2FROM
3   (SELECT  
4         ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
5    FROM Employees e
6   ) as DerivedTableName
7WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
8
9

但是有一个小问题，如果最里面的一层查询有重复值，上面的排序将会出现问题，最好将代码优化成如下形式：

 1SELECT
 2(
 3    SELECT ,
 4    ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
 5    FROM
 6       (SELECT DISTINCT          
 7        FROM Employees e
 8        INNER JOIN f
 9        ON e.id = f.id
10       ) AS DerivedTableName
11) AS Temp
12WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

之所以要这么做是为了是DISTINCT发挥作用，因为在SQL Server2005中DISTINCT和ROW_NUMBER()一起使用，DISTINCT将不能过滤掉重复的记录，在Oracle中DISTINCT和ROWNUM也会同样出现这样的问题，这些都是本人在实际项目中遇到的问题并总结出来的，或许你有更好的方法，可以在我的博客中留言，谢谢！

参考文章：http://aspnet.4guysfromrolla.com/articles/031506-1.aspx