How to improve ASP.Net Dynamic Data pagination performance

I'm using ASP.Net Dynnamic Data (.Net Framework version 4.0) and SQL Server 2008 to display data from a set of tables. In the database there is a certain master table containing 83 columns with 854581 rows of data with is still expected to grow.

I did little modification to the default templates, and most operations are snappy enough for a business website, including filtering, foreign key displaying, etc. Yet the pager proved to be a major performance problem.

Before I did any optimization using the Database Tuning Advisor, the master table list page won't even display as a simple count query times out. After all optimizations, the first few hundred pages will display without a proble开发者_Python百科m, but anything beyond 1000 pages is unbearably slow and the last page times out.

Note that I've optimized the query for the last page using Database Tuning Advisor, which gave me no advice at all.

I tried to run a very simple query using row_number() to see the last page, similar to the query below:

SELECT TOP 10* FROM 
    (SELECT *, row_number() OVER (ORDER BY [Primary Key Column] ASC) AS [rn]
     FROM [Master Table]) AS TB1
WHERE TB1.rn > 854580

The above query took about 20 seconds to complete the first time it was executed, while the SQL Server service sqlservr.exe ate up 1,700 KiB of memory (I'm using 32-bit windows with no special kernel switches, therefore, every process has at most 2 GiB of address space).

So, my question is, is there a more efficient way than using row_number() to do pagination in ASP.Net Dynamic data and how to implement it.

There are two possibilities that I came up with:

1. There is some magical T-SQL language construct with better performance, for instance, something like LIMIT <From row number>, <To row number> in MySQL.
2. I add a sequence number column in the table, and page according to that number when there is no filtering or sorting

Perhaps the greater problem is how to implement this custom paging in ASP.Net Dynamic Data. Any help is appreciated, even pagination samples not intended to improve performance, just to give me an idea.

Note: I can't change the database engine I use, so don't recommend MySQL or PostgreSQL or anything like that.

---

20 seconds is long even for 1 million rows - check your SQL Server indexes!

Also make sure you are retrieving only the data columns required in the List view. (you mentioned 83 columns....you are not showing all those in the List view.

Another approach I can think of is to use the Linq query expressions with Skip() and Take() to get a page at a time on pagination. Of course this means you will be querying less data, more often.