Geek Answers Handbook

Fastest SQL 2005 select query for ASP.NET swap table?

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

In 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 large single-column table containing all of my identifiers.

Is there anything faster?

Don’t think to make some workarounds using the identifier column, this will not work, I am deleting rows from this table, so my identifiers are not consecutive numbers.

+4
source share
4 answers

Ok This is my last thought on this issue.

For large projects with tables containing 10 million rows or more, I will use this approach:

  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 will use SELECT below, which works very fast for the first 100,000 lines, 10,000 pages with 10 lines / page, but from page 10,000 to infinity, the query will run slower and slower, to a very slow one. Nobody wants to view the page 10.001 !!

  • To count the number of pages and the number of rows that match myCondition from the above SELECT, I will create a special TABLE that will contain only one row and one column, in this column I will store the number of rows. Each time I add, change or delete a line from myTable, I update this colon based on myCondition, adding or decreasing it with a value of 1. The goal of this is to quickly select the number of lines that match myCondition and show my users how many pages I have.

0
source

This SQLServerCentral article is excellent:
SQL Server 2005 Paging - The Holy Grail

+2
source

I noticed that you have a lot of rows by adding indexes to Column4, and Column5 will significantly increase performance if not added.

I found the following article interesting: Features and ranking performance in SQL Server 2005

I will tell you how to improve it in accordance with the article, if possible. I tested their solutions myself, and it works.

If you expect a search call in ASP.NET, I also found the following Scott Mitchell article is very interesting: User paging in ASP.NET 2.0 with SQL Server 2005

He used his method in my code and it works great. Here is an example TSQL code:

  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 article 4guysfromrolla for more information.

Luck

+1
source

if you are paging, you can pass the first and last key for the current page and restrict the derivative to "tempTable" using those that force it to return fewer rows and therefore faster.

0
source

More articles:


All Articles