Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have to query a large data-set (~500 MB) using a sqlDataReader but I have to save the data in three different formats. Instead of querying the database 3 times I'm thinking about saving the data to an in-memory table and then save the data from there.

Is there a practical limit to a table size in memory? Any ideas for a better approach?

Thanks!

share|improve this question
    
it will depend on your OS, is it 32bit or 64? –  Yusubov Jul 6 '12 at 22:14
add comment

4 Answers

As for the approach - how about building each of your three outputs while looping over the data reader a single time?

share|improve this answer
    
I second this. If you can process your data as a stream the most you'd need to hold in memory is one row at a time. You can sequentially process each row with three different marshallers. –  Andrew Finnell Jul 6 '12 at 21:56
add comment

I am not aware of any memory size limits to data tables - you might hit the process size ceiling if you are running as a 32bit process however (the 3GB of addressable memory).

The approach (caching the data into an in memory structure like a DataTable) is valid and makes sense.

share|improve this answer
add comment

According to MSDN DataTable Class "The maximum number of rows that a DataTable can store is 16,777,216", surprisingly, the page is not showing limitations in terms of MBs. There are issues addressed in MSDN Out of Memory Exception.

If you are running under Windows, its quick and safe to serialize the data to a file of any kind you choose and then use the generated file as input for each of your 3 formats. Writing to a file is much more safer and can allow you to restart the application in case it fails during one of the generations. It would also allow you to run the generation of your 3 formats in parallel, hence making the application much faster and more reliably.

The save-to-file approach is reliable and is used in Data Mart projects all over the planet.

share|improve this answer
    
His data is already stored in a file, by the database. –  Andrew Finnell Jul 6 '12 at 22:08
    
@AndrewFinnell, sorry I did not get what you mean. What I mean in my answer is to store the data in a sequential file of any format and access it sequentially not through database query which could take time and resources to repeat 3 times. –  Emmad Kareem Jul 6 '12 at 23:58
add comment

SQL Server 2005 has 1024 columns limit per base table. A data table should be able to take at least 1024 data columns.

share|improve this answer
1  
Please don't include your website as a signature. You can include a link in your profile if you want. –  ChrisF Nov 19 '12 at 9:22
1  
How exactly does this answer the author's question? Why would you base your answer on SQL Server 2005 of all things? –  Ramhound Nov 19 '12 at 12:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.