在sql 2005中对大表进行分页

Question

我有一个包含 15 列和 650 万条记录的表。 我需要在分页的帮助下从 C# 端访问该表。 我写了一个SP，但是检索数据需要大约1.30分钟。 这是我的存储过程 -

Create Proc demo
(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows

IF @startRowIndex = 0 
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = RecordID FROM edd_business_listings_05282009 ORDER BY RecordID

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT * FROM edd_business_listings_05282009 WHERE 
RecordID >= @first_id 
ORDER BY RecordID

SET ROWCOUNT 0

有谁知道如何使其运行得更快。

Answer 1

您的应用程序可以发送最后一个 RecordID 吗？

让前端更加努力。

创建 Proc 演示 ( @startRowID int, @maximumRows int ) AS

SET ROWCOUNT @maximumRows

SELECT * FROM edd_business_listings_05282009 WHERE RecordID > @startRowID ORDER BY RecordID

SET ROWCOUNT 0

Answer 2

尝试在 SQL 2005 中使用 ROW_NUMBER： https://web.archive.org/web/20210512232142/http://www.4guysfromrolla.com/webtech/010406-1.shtml

这样的过程会有所帮助：

CREATE PROCEDURE dbo.GetListingPaged
(
    @StartRowIndex int,
    @MaximumRows int
)
AS
    SELECT
        RecordID,
        Field2 -- Not *
    FROM
    (
    SELECT
        RecordID,
        Field2 -- Not *
        ROW_NUMBER() OVER (ORDER BY RecordID) AS RowRank
    FROM edd_business_listings_05282009
    ) AS ListingWithRowNumbers
    WHERE
        RowRank > @StartRowIndex
    AND
        RowRank <= (@StartRowIndex + @MaximumRows)

GO

Answer 3

好吧，当然，这也是我的猜测：

Create Proc demo ( @startRowIndex int, @maximumRows int ) AS
DECLARE @first_id int, @startRow int
SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0 SET @startRowIndex = 1

SELECT TOP (@maximuRows)
 {'all columns except N'}
 FROM (
    Select *, ROW_NUMBER() Over(Order by RecordID) as N
     from edd_business_listings_05282009
    ) As t
 WHERE N >= @startRowIndex
 ORDER BY RecordID

Answer 4

最好的解决方案将在很大程度上取决于

1. 数据更改的频率

2. 调用存储过程的频率以及用户通常页面的深度以及

3. 在排序过程中您可以接受多少延迟（如果有） -迄今为止。

通常在网站后端，您的用户只使用前几个页面，但谷歌机器人可以猛烈攻击您的深层页面，从而猛烈攻击您的CPU。 通常，只在某个点上支持这种实时排序（例如，对前几百或几千行使用 row_number() ），然后切换到在某个时间间隔（可能每小时）刷新的非规范化排序列表。

Answer 5

尝试在 RecordId 列上放置索引。 我认为正在发生的事情是在行计数到位之前进行整个表扫描，以便 Sql 可以对所有内容进行排序。 如果您已经有了索引，那么问题就出在其他地方。 我对记录数量是两倍的表执行了相同的查询，并且执行时间从未超过 2 秒。

使用 ROWCOUNT 或 Row_Number() 应该在技术上完成相同的性能明智的事情，但我会使用 Row_Number() 因为它是一种更现代的方法，并且设置 rowcount 比 Row_Number() 更复杂，我不会进入。

Answer 6

如果你使用SQL Server 2005，那么你可以尝试

SELECT  field1, field2, fieldN
FROM     (SELECT ROW_NUMBER() OVER (ORDER BY RecordID) AS Row,
             field1, field2, fieldN FROM edd_business_listings_05282009)
            AS ListingsWithRowNumbers
WHERE  Row >= @startRowIndex AND Row <= @startRowIndex + @maximumRows

但是无论如何，尝试重新思考这个架构。 在 UI 中显示数百万条记录（甚至分页）有什么用？ 您可以尝试限制记录数量并最初只查询一个子集......