在SQL Server 2000、2005、2008、2012中分页结果的最佳方法(性能方面)是什么?如果你还想获得结果的总数(在分页之前)?
当前回答
令人难以置信的是,没有其他答案提到在所有SQL Server版本中进行分页的最快方法。对于大页码,偏移量可能非常慢。在SQL中有一种完全不同的更快的分页方法。这通常被称为“查找方法”或“键集分页”,如本文所述。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
"seek谓词"
@previousScore和@previousPlayerId值分别是前一页中最后一条记录的值。这允许你获取“下一页”。如果ORDER BY方向为ASC,只需使用>即可。
使用上述方法,如果不先获取之前的40条记录,就不能立即跳转到第4页。但通常情况下,你并不想跳得那么远。相反,您将获得一个更快的查询,可能能够在常数时间内获取数据,这取决于您的索引。另外,你的页面保持“稳定”,无论底层数据是否发生变化(例如,在第1页,而你在第4页)。
例如,当在web应用程序中延迟加载更多数据时,这是实现分页的最佳方法。
注意,“seek方法”也称为键集分页。
分页前的记录总数
COUNT(*) OVER()窗口函数将帮助您计算“分页前”的总记录数量。如果您使用的是SQL Server 2000,则必须对COUNT(*)进行两次查询。
其他回答
好吧,我已经在我的SQL 2000数据库中使用了以下示例查询,它也适用于SQL 2005。它通过使用多个列来动态排序。 我告诉你……这是强大的:)
ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary]
@CompanyID int,
@pageNumber int,
@pageSize int,
@sort varchar(200)
AS
DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)
If(@pageNumber < 0)
SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20))
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For example if pageNumber is 5 pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '
SET @strFilter = ' WHERE
CompanyID = ' + CAST(@CompanyID As varchar(20))
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort
-- Total Rows Count
SET @sql = 'SELECT Count(' + @strID + ') FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql
--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
' WHERE ' + @strID + ' IN ' +
' (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter +
' AND ' + @strID + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') '
+ @SortBy + ') ' + @SortBy
Print @sql
EXEC sp_executesql @sql
最好的部分是sp_executesql缓存以后的调用,只要您传递相同的参数,即生成相同的sql文本。
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx上有关于不同分页技术的很好的概述
我经常在SQL Server 2000中使用ROWCOUNT方法(也可以在2005和2008中使用,只测量与ROW_NUMBER相比的性能),它的速度非常快,但你需要确保排序的列(大多数)具有唯一的值。
用例方面,以下内容似乎易于使用和快速。只需设置页码。
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
同样没有CTE
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
令人难以置信的是,没有其他答案提到在所有SQL Server版本中进行分页的最快方法。对于大页码,偏移量可能非常慢。在SQL中有一种完全不同的更快的分页方法。这通常被称为“查找方法”或“键集分页”,如本文所述。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
"seek谓词"
@previousScore和@previousPlayerId值分别是前一页中最后一条记录的值。这允许你获取“下一页”。如果ORDER BY方向为ASC,只需使用>即可。
使用上述方法,如果不先获取之前的40条记录,就不能立即跳转到第4页。但通常情况下,你并不想跳得那么远。相反,您将获得一个更快的查询,可能能够在常数时间内获取数据,这取决于您的索引。另外,你的页面保持“稳定”,无论底层数据是否发生变化(例如,在第1页,而你在第4页)。
例如,当在web应用程序中延迟加载更多数据时,这是实现分页的最佳方法。
注意,“seek方法”也称为键集分页。
分页前的记录总数
COUNT(*) OVER()窗口函数将帮助您计算“分页前”的总记录数量。如果您使用的是SQL Server 2000,则必须对COUNT(*)进行两次查询。
试试这个方法:
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
推荐文章
- SQL Server数据库备份恢复到低版本
- 在MySQL中检测值是否为number
- 如何有效地从数组列表或字符串数组中删除所有空元素?
- MySQL中两个日期之间的差异
- SQL Server:过滤sp_who2的输出
- 使用SQL查询查找最近的纬度/经度
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- .toArray(new MyClass[0]) or .toArray(new MyClass[myList.size()])?
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 是什么导致JNI调用变慢?
- 如何将SQL Azure数据库复制到本地开发服务器?
- 检查属性是否有属性