在 SQL Server 2000 中对大量数据进行分页的最有效方法是什么？

发布于 2024-10-11 02:37:35 字数 288 浏览 2 评论 0原文

如果我有一个包含大量信息的查询（比如几个视图，每个视图都访问几个表，其中许多表有数万行），并且我只需要从中获取 10 条记录即可显示到用户，在性能方面，检索这些记录同时仍支持 SQL Server 2000 的最佳方法是什么？一旦我可以使用 SQL Server 2005，ROW_NUMBER 似乎是显而易见的选择（如果我错了，请纠正我），但是 2000 年该怎么办？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

望喜 2024-10-18 02:37:35

Greg Hamilton 有一篇文章使用 SET ROWCOUNT 和 < code>SELECT 到变量中以避免引用不需要的行，从而获得一些非常引人注目的性能结果。但是，MSDN 说

如果在选择列表中引用变量，则应为其分配标量值，否则 SELECT 语句应仅返回一行。

但接着又说

请注意，只有在作业之间存在引用时，效果才可见。
如果 SELECT 语句返回多行并且变量引用非标量表达式，则该变量将设置为结果集最后一行中表达式返回的值。

表明在这种情况下确实没问题（对吗？）

Greg 最终得到了这样的结果：

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
    @startRowIndex int,
    @maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName 
FROM employees e
   INNER JOIN Departments D ON
       e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

此方法假设您有一个唯一的 ID 来排序，我不认为您可以在排序时按原样使用此方法，比如说，一个非唯一的 DateTime 列。

Greg Hamilton has an article which uses SET ROWCOUNT and SELECTing into a variable to avoid having to reference rows that aren't needed, with some pretty compelling performance results. However, MSDN says

If a variable is referenced in a select list, it should be assigned a scalar value or the SELECT statement should only return one row.

But then it goes on to say

Note that effects are only visible if there are references among the assignments.
If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set.

Indicating that it's really okay in this instance (right?)

Greg ends up with this:

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
    @startRowIndex int,
    @maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName 
FROM employees e
   INNER JOIN Departments D ON
       e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

This method assumes that you have a unique ID to order by, I don't think that you can use this method as-is when sorting on, say, a non-unique DateTime column.

回复收藏 0 原文

~没有更多了~