在ASP.NET MVC应用程序中检索大量行(超过1000万行) [英] Retrieving large number of rows (more than 10 mil) in asp.net mvc application

问题描述

我正在研究asp.net mvc应用程序，它提供了使用DATAREADER从 ORACLE 数据库读取的功能，并将这些行显示给用户(有时可达1000万).读取大约900,000行后，datareader读取操作将抛出内存不足异常.

I am working on asp.net mvc application and it provides the functionality of reading from ORACLE database using DATAREADER and present those rows to the user (sometimes up to 10 mil). The datareader read operation throws out of memory exception after reading about 900,000 rows.

我正在与同事讨论这个问题，他建议我应该使用无连接范式(可能是Entity Framework)或存储过程，并将数据分成大块.

I was discussing this issue with my colleague and he suggested that I should use connectionless paradigm (may be Entity framework) or stored procedure and bring data in chunks.

我想知道是否有人可以权威地说这是解决上述问题的最佳方法.

I wonder if there is someone out there who can authoritatively say which is the best way to accomplish above issue.

推荐答案

不要将所有行都检索到内存中并执行分页

Don’t retrieve all the rows to memory and perform the paging

•并非所有用户都访问第二页

•Not all the users visits 2nd page

•因此您在内存中的数据将不会被使用

•So your data in memory will be unused

如果有更多的记录使用SQL端分页，则可以使用Row_number()函数在SQL端执行分页.

If you are having more records use SQL side paging, you can use Row_number() function to perform paging in SQL side.

您还可以使用ORM框架访问数据，它们始终提供执行数据相关操作的最佳方法.

You can also use ORM frameworks to access the data, they always provides best approaches to perform data related operations.

我更喜欢使用Peta Poco，它具有一种检索按页数据的方法.

I prefer to use Peta Poco, it has a method to retrieve page wise data.

var result=db.Page<article>(1, 20, // <-- page number and items per page
"SELECT * FROM articles WHERE category=@0 ORDER BY date_posted DESC", "coolstuff");

http://www.toptensoftware.com/petapoco/

这篇关于在ASP.NET MVC应用程序中检索大量行(超过1000万行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！