1

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.

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
kulNinja
  • 526
  • 6
  • 14
  • How do you plan on presenting the data to the user? All 10 million records visible on the page at once? Paginate the records so that only a few hundred at a time are visible? AJAX to dynamically fetch more records as you scroll? – mbeckish Aug 13 '13 at 16:58
  • 1
    One word: **don't** - unless you must do statistical analysis or something, do **NOT** retrieve millions of rows. How are you going to work with that? How is your user going to interact with such a huge set of data? Impossible to find a good solution. Fetch **only** as much data as you can use/show to the user etc. - get 100, 500, 1000 rows - no more. – marc_s Aug 13 '13 at 17:01
  • "mbeckish"/marc_s I am already paginating but before paginating I am retrieving *all* data (of course that approach do not work with 10 mil records) - I like approach suggested by mbeckish to use AJAX to dynamically fetch more records as user scroll. I am not too sure how would I implement this - Is there a link that you can suggest? THanks – kulNinja Aug 13 '13 at 17:29
  • @user430017 Research the term 'infinite scrolling'. There are many options out there for asp.net mvc. – Eric King Aug 13 '13 at 18:10
  • Thanks Eric King. I will do the research and get back to you. – kulNinja Aug 13 '13 at 18:50
  • Sorry I didn't mention that I am using ORACLE. It seems PetaPOCO supports oracle and massive do not. – kulNinja Aug 13 '13 at 21:13

1 Answers1

0

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

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

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

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/

CreativeManix
  • 2,162
  • 1
  • 17
  • 29
  • Thanks @CreativeManix - I am going to give POCO a try. It reads interesting. I will get back to you. – kulNinja Aug 13 '13 at 18:50
  • Pardon me - I may be oversimplifying things and failing to understand your point. Have you ever tried to paginate right in ORACLE. I came across this **http://stackoverflow.com/questions/241622/paging-with-oracle** I wonder if I can use above method to paginate instead of going through petaPeco. The reason for this is 1. I don't understand much of ORM 2. It will be simpler. What do you think? Thanks – kulNinja Aug 13 '13 at 22:59
  • I came across this - http://stackoverflow.com/questions/18243213/error-in-running-transformationindexoutofrangeexception-petapeco – kulNinja Aug 14 '13 at 22:14