最快的SQL 2005选择ASP .NET表分页查询？

What is the fastest way to select a range of rows, let's say from 4.200.000 to 4.200.050, using SQL 2005? Suppose that I have 10 millions of rows.

选择一系列行的最快方法是什么,比如说从4.200.000到4.200.050,使用SQL 2005?假设我有1000万行。

On my own projects I use the following approach, but I'm not sure if this is the best practice

在我自己的项目中,我使用以下方法,但我不确定这是否是最佳实践

select * from
(
    select 
        Column1, Column2, Column3
        RowNumber = row_number() over (order by ID asc) 
    from 
        tblLogs
    where
        Column4 = @Column4 and Column5 = @Column5
    ) as tempTable
where tempTable.RowNumber >= @StartIndex and tempTable.RowNumber <= @EndIndex

With the code above I am tempted to say that tempTable will be a big table with one column containing all my IDs.

使用上面的代码,我很想说tempTable将是一个包含所有ID的列的大表。

Is there anything faster ?

有什么更快的吗?

Don't think to make some workarounds using the ID column, this won't work, I delete rows from that table, so my IDs are not successive numbers.

不要以为使用ID列做一些变通方法,这不起作用,我从该表中删除行,所以我的ID不是连续的数字。

4 个解决方案

#1

This article over at SQLServerCentral is excellent:
SQL Server 2005 Paging – The Holy Grail

这篇文章在SQLServerCentral上非常出色:SQL Server 2005 Paging - The Holy Grail

#2

I noticed that you have a lot of rows, adding indexes on Column4 and Column5 would increases performances dramatically if not already added.

我注意到你有很多行,在Column4和Column5上添加索引会增加性能,如果还没有添加的话。

I found the following article interesting: Ranking Functions and Performance in SQL Server 2005

我发现以下文章很有趣:SQL Server 2005中的功能和性能排名

I will let you figure out how to improved it according to the article if possible. I tested their solutions myself and it works.

如果可能的话,我会让你弄清楚如何根据文章改进它。我自己测试了他们的解决方案并且有效。

If you're looking forward paging in ASP.NET, I also found the following article by Scott Mitchell very interesting: Custom Paging in ASP.NET 2.0 with SQL Server 2005

如果您期待在ASP.NET中进行分页,我还发现Scott Mitchell的以下文章非常有趣:使用SQL Server 2005在ASP.NET 2.0中进行自定义分页

It used their method in my code and it works just great. Here is a sample of TSQL code:

它在我的代码中使用了他们的方法,它工作得很好。以下是TSQL代码的示例:

    SELECT ROWNUM, COLUMN1, COLUMN2, COLUMN3
    FROM (
    SELECT COLUMN1, COLUMN2, COLUMN3,
    ROW_NUMBER() OVER(ORDER BY ID) AS ROWNUM
            FROM TABLE1
    WHERE COLUMN4 = @X AND COLUMN5 = @Y
    ) AS TABLE2
WHERE ROWNUM BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

I suggest reading the 4guysfromrolla's article for further information.

我建议阅读4guysfromrolla的文章以获取更多信息。

Good Luck

#3

if you are paging, you can pass in the first & last key for the current page, and limit your derived "tempTable" using those to make it return fewer rows and thus faster.

如果您正在进行分页,则可以传入当前页面的第一个和最后一个键,并使用这些键限制派生的“tempTable”,以使其返回更少的行,从而更快。

#4

Ok. This is my final thought about this problem.

好。这是我对这个问题的最终想法。

For big projects, with tables containing 10 millions of rows or more, I will use this approach:

对于大型项目,包含1000万行或更多行的表,我将使用这种方法:

  select * from
  (
        select 
        myTable.*, 
        RowNumber = row_number() over (order by myTable.ID asc) 
    from 
        myTable
        where
                myCondition
  ) as tempTable
  where tempTable.RowNumber >= @StartIndex and tempTable.RowNumber <= @EndIndex

for ASP .NET paging I'll use the SELECT below, wich works very fast for first 100.000 rows, 10.000 pages with 10 rows / page, but from page 10.000 to Infinity the query will work slower and slower, to very slower. No one will want to browse the page 10.001 !!

对于ASP .NET分页,我将使用下面的SELECT,对于前100,000行,10.000页,10行/页,非常快,但从页面10.000到无穷大,查询将运行得越来越慢,越来越慢。没有人会想要浏览页面10.001 !!
For counting the number of pages and number of rows that fulfill myCondition from the SELECT above, I'll make a special TABLE that will have only one row and one column, on this column I will store the number of rows. Every time I add, modify or delete a row from myTable I will update this colon based on myCondition by adding or decreasing it with value 1. The purpose of making this is to fast select the number of rows that fulfill myCondition and show to my users how many pages I have.

为了计算从上面的SELECT中满足myCondition的页数和行数,我将创建一个只有一行和一列的特殊TABLE,在此列中我将存储行数。每次我在myTable中添加,修改或删除一行时,我都会根据myCondition通过添加或减少值1来更新此冒号。这样做的目的是快速选择满足myCondition并向我的用户显示的行数我有多少页。

#1