如何提高 ASP.Net 动态数据分页性能
我正在使用 ASP.Net 动态数据（.Net Framework 版本 4.0）和 SQL Server 2008 显示一组表中的数据。数据库中有一个主表，包含 83 列、854581 行数据，并且预计还会增长。
我对默认模板做了很少的修改，大多数操作对于商业网站来说已经足够快了，包括过滤、外键显示等。但事实证明，分页器是一个主要的性能问题。
在我使用数据库优化顾问进行任何优化之前，主表列表页面甚至不会显示为简单的计数查询超时。经过所有优化后，前几百页将毫无问题地显示，但超过 1000 页的任何内容都会慢得难以忍受，并且最后一页会超时。
请注意，我已经使用数据库优化顾问优化了最后一页的查询，这根本没有给我任何建议。
我尝试使用 row_number() 运行一个非常简单的查询来查看最后一页，类似于下面的查询：
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
上面的查询第一次执行大约需要 20 秒才能完成，而 SQL服务器服务 sqlservr.exe 占用了 1,700 KiB 内存（我使用的是 32 位 Windows，没有特殊的内核开关，因此，每个进程最多有 2 GiB 的地址空间）。
所以，我的问题是，是否有比使用 row_number() 更有效的方法在 ASP.Net 动态数据中进行分页以及如何实现它。
我想到了两种可能性：
- 有一些神奇的 T-SQL 语言构造具有更好的性能，例如，类似于
LIMITin MySQL。, - 我在表中添加了一个序列号列，并且在没有过滤或排序的情况下根据该数字进行分页。
也许更大的问题是如何在 ASP.Net 动态数据中实现这种自定义分页。任何帮助都是值得赞赏的，即使分页示例并不是为了提高性能，只是为了给我一个想法。
注意：我无法更改我使用的数据库引擎，因此不推荐 MySQL 或 PostgreSQL 或类似的引擎。
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 problem, 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:
- There is some magical T-SQL language construct with better performance, for instance, something like
LIMIT <From row number>, <To row number>in MySQL. - 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
即使对于 100 万行，20 秒也很长 - 检查您的 SQL Server 索引！
还要确保您仅检索列表视图中所需的数据列。 （您提到了 83 列......您没有在列表视图中显示所有这些列。
我能想到的另一种方法是使用带有 Skip() 和 Take() 的 Linq 查询表达式在分页时一次获取一页当然，这意味着您将查询更少的数据、更频繁地查询。
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.