【续】用ObjectDataSource实现自定义分页的心得总结,几种分页方法的性能对比
Posted on 2007-06-12 15:22 漂泊雪狼 阅读(3963) 评论(9) 编辑 收藏 举报
在《用ObjectDataSource实现自定义分页的心得总结》中,在SQL Server中使用双Top查询,后来用“事件探查器”进行性能对比,发现在SQL Server 2005中使用RowNumber函数的方法更有效;在SQL Server 2000中无法使用RowNumber函数,但是数据量较大时,双Top查询速度很慢,排序的字段若没有建立索引后果更加糟糕,其实在SQL Server 2000中,可以巧妙的使用SET ROWCOUNT和表变量来实现自定义分页,如下是一个参考示例:
利用表变量分页
在上面的代码中,如果EmployeeID是唯一的(一般在数据库设计的时候推荐在表中加一个自增列的字段来取代它),可以不用表变量切性能可进一步得到优化,具体代码如下:
优化的分页程序
在SQL Server 2005和Oracle 9i后的版本中可以分别使用ROWNUMBER和ROWNUM来进行分页,并能获得较好的性能,以SQL Server 2005为例,一般的做法如下:
参考文章:http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
在上面的代码中,如果EmployeeID是唯一的(一般在数据库设计的时候推荐在表中加一个自增列的字段来取代它),可以不用表变量切性能可进一步得到优化,具体代码如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
在SQL Server 2005和Oracle 9i后的版本中可以分别使用ROWNUMBER和ROWNUM来进行分页,并能获得较好的性能,以SQL Server 2005为例,一般的做法如下:
1
SELECT ![](https://www.cnblogs.com/Images/dot.gif)
2
FROM
3
(SELECT
4
ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
5
FROM Employees e
6
) as DerivedTableName
7
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
但是有一个小问题,如果最里面的一层查询有重复值,上面的排序将会出现问题,最好将代码优化成如下形式:![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
SELECT
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
12
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
之所以要这么做是为了是DISTINCT发挥作用,因为在SQL Server2005中DISTINCT和ROW_NUMBER()一起使用,DISTINCT将不能过滤掉重复的记录,在Oracle中DISTINCT和ROWNUM也会同样出现这样的问题,这些都是本人在实际项目中遇到的问题并总结出来的,或许你有更好的方法,可以在我的博客中留言,谢谢!![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
参考文章:http://aspnet.4guysfromrolla.com/articles/031506-1.aspx