获取 ASP.NET 网格分页数据的性能改进
我在当前UI的开发中遇到了性能问题。但我认为这个问题是普遍存在的。
我有一个带有简单 asp.net 网格的页面。网格将根据某些搜索条件显示表中的数据。此外,网格具有固定的页面大小(例如 10)。底部有寻呼机,可用于导航黑白页面。在后端,每当按下搜索按钮时,都会调用一个存储过程来返回所需的数据。
存储过程具有 currentpageIndex、pagesize、其他搜索条件等参数。这是 sp 的伪代码:
-- SP begins
-- calculate the page index range to return required using current page index and page size
-- query the table in a CTE and do all filtering. Also calculate row numbers so that
-- correct record range can be returned.
-- use the cte to return the correct record based on the row number calculated in CTE
-- SP ends
我在此方法中遇到以下问题/查询
- 当数据库表大小很大(例如 1000 万条记录)时,性能会下降,并且此方法变得不切实际。
- 使用表变量还是临时表更有用?
- 还有其他有效的方法从数据库获取分页数据吗?
嗨,丹,这篇文章为计算总行数提供了新的见解。真的很有帮助。谢谢。
但当数据量很大时,还有比使用 CTE 更好的方法吗?
更新:我发现了一些其他性能 有效获取分页记录的方法。
I am encountering a performance problem in the development of current UI. The problem, I suppose, is however general.
I have a page with a simple asp.net grid. Grid will display data from a table based on certain search criteria. Moreover, grid has fixed page size (say 10). There is pager at the bottom which can be used to navigate b/w pages. On the back end, whenever search button is pressed a stored procedure is called which returns the desired data.
Stored procedure has parameters like currentpageIndex, pagesize, other search criteria, etc. Here is a pseudo code for sp:
-- SP begins
-- calculate the page index range to return required using current page index and page size
-- query the table in a CTE and do all filtering. Also calculate row numbers so that
-- correct record range can be returned.
-- use the cte to return the correct record based on the row number calculated in CTE
-- SP ends
I have following problems/queries in this approach
- When Db table size is large (say 10 million records), performance degrades and this approach becomes impractical.
- Is using table variable or a temporary table more useful?
- Is there any other efficient way to get paged data from database?
Hi Dan, the article provided a new insight for calculation of total rows. Really helpful. Thanks.
But still is there better way than using CTE when data is large?
Update: I have found few other performant approaches for efficiently getting paged records.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
SqlServerCentral 上有一篇很好的文章,名为SQL Server 2005 Paging – The Holy Grail这展示了服务器端分页的一些技术。不过,您需要注册才能查看它。
我知道对于非常大的结果集,像 Google 这样的软件将简单地估计有多少将返回行,从而无需获取所有返回行的计数。
抱歉,如果我不能提供更多帮助。
There's a good article on SqlServerCentral called SQL Server 2005 Paging – The Holy Grail that shows a few techniques for server-side paging. You will need to register to view it, though.
I know for really large result sets then software like Google will simply estimate how many rows will be returned, bypassing the need to get a count of all the rows returned.
Sorry, if I can't give more help.