在sql 2005中对大表进行分页

I have a table with 15 columns and 6.5 MILLION records. I need to access this table from the C# side with help of paging. I have written an SP but it takes about 1.30 mins to retrieve the data. here's my Stored Proc -

我有一个有15栏和650万记录的表格。我需要在分页的帮助下从c#端访问这个表。我已经写了一个SP，但是检索数据需要大约1.30分钟。这是我的存储Proc -

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

Does anyone knows a way of making this run faster.

有没有人知道怎样才能跑得更快?

6 个解决方案

#1

Can your application send in the last RecordID?

您的应用程序可以在上一个记录中发送吗?

Make the front-end work harder.

让前端更加努力。

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

创建Proc demo (@startRowID int， @maximumRows int) AS

SET ROWCOUNT @maximumRows

设置ROWCOUNT @maximumRows

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

从edd_business_listings_05282009中选择*，其中RecordID > @startRowID订单被RecordID

SET ROWCOUNT 0

设置ROWCOUNT 0

#2

Try using ROW_NUMBER in SQL 2005: http://www.4guysfromrolla.com/webtech/010406-1.shtml

尝试在SQL 2005中使用ROW_NUMBER: http://www.4guysfromrolla.com/webtech/010406-1.shtml

Procedure such as this would help:

这样的程序将会有所帮助:

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

#3

OK, sure, here's my guess too:

好吧，当然，我也这么想:

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

#4

The best solution is going to depend heavily on

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

1.how often the data changes

1。数据多长时间变化一次

2.how often the sproc is called and how deep a user will typicall page and

2。sproc多长时间被调用一次，用户一般会调用多少次

3.how much latency (if any) you can accept in the ordering being up-to-date.

3所示。您可以接受在最新的订单中接受多少延迟(如果有的话)。

Often in a website backend your users only use the first few pages, but google bot can slam your deep pages and therefore slam your cpu. It is usually fine to support this live ordering only up to a certain point (e.g. use row_number() for first few hundred or thousand rows) and then switch to a denormalized list of ordering that is refreshed over some interval (perhaps hourly).

通常在一个网站后端，你的用户只使用前几页，但是谷歌机器人可以猛击你的深度页面，因此猛击你的cpu。通常情况下，只支持某个点的实时排序是可以的(例如，对前几百行或千行使用row_number())，然后切换到一个经过一定间隔(可能是每小时)刷新的非规范化排序列表。

#5

Try putting an Index on the RecordId column. What I think is happening is your doing an entire table scan before the rowcount is in place so Sql can order everything. If you do already have an index than something else is the problem. I've done this same query on tables with twice the number of records and my execution time never went above 2 seconds.

尝试在RecordId列上放置一个索引。我认为正在发生的是在rowcount之前做一个完整的表扫描，这样Sql就可以命令所有的事情了。如果你已经有了索引，那么问题就来了。我在表上做了相同的查询，有两倍的记录，我的执行时间从来没有超过2秒。

Using ROWCOUNT or Row_Number() should technically accomplish the same thing performance wise but I'd use Row_Number() as it is a more modern way of doing this and setting rowcount comes with a lot more complexities than Row_Number() that I won't get into.

使用ROWCOUNT或Row_Number()在技术上应该实现同样的性能，但我应该使用Row_Number()，因为这是一种更现代的方法，而且设置ROWCOUNT比我不会涉及的Row_Number()复杂得多。

#6

If you use SQL Server 2005, then you can try

如果您使用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

But anyway, try to rethink this architecture. What use is to display millions of records (even paged) in UI? You could try to limit the number of records and query only a subset initially...

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

#1