Re: OutOfMemoryException Filling DataTable

From:

Jesse Houwing <jesse.houwing@newsgroup.nospam>

Newsgroups:

microsoft.public.dotnet.framework

Date:

Sun, 25 Oct 2009 17:22:39 +0100

Message-ID:

<#bt1g9YVKHA.4284@TK2MSFTNGP06.phx.gbl>

* SnapDive wrote, On 22-10-2009 22:57:

Upgrading is not an option. Using a DataReader is not an option.
Paging would be least-intrusive to the app, but would still impact
existing code. I'd like to let callers keep their same interface but
behind the scenes swap in/out what is needed from the "in memory"
construct, much the same way a memory-mapped file behaves.

You could just show them 1000 rows and require to filter more (if there
is such a facility) due to the amount of records.

Other than that, there is no way to map a dataset to disk and bind to
it. The grid showing the data would still have to retrieve most of the
data back into memory anyway.

You could always decide to fetch the next batch of records as th euser
scrolls down. (like Windows Media Player does when scrolling through the
media library).

I think there isn't going to be a "non-intrusive-way" to fix this.
Jesse

Thanks.

On Tue, 20 Oct 2009 16:54:15 +0200, Jesse Houwing
<jesse.houwing@newsgroup.nospam> wrote:

* SnapDive wrote, On 20-10-2009 16:35:

I am doing a simple select on a MSSQL 2005 database and asking for
10,000 rows using classic ADO.NET. I am getting an out of memory
exception. I need this big table, is there any way to back the
DataTable with a disk-resident stream or something else that will let
me "go big"? I don't care if it is slow or not.

Thanks.

DataTable tb = new DataTable();
try
{
using ( sqlco sqlco = new sqlco( myconnstring ) )
{
sqlco.Open();
using ( da da = new da( "select top 10000 id,key,value from mytable" ,
sqlco ) )
{
da.Fill( tb );
}
}
}

What do you need these 10.000 rows for? Can you
- Page the result set and load them per 10 or 100 or even 1000?
- Use a DataReader and only have one row in memory at the time?
- Upgrade to SQL 2008 and use the new FILESTREAM feature
(http://msdn.microsoft.com/en-us/library/cc716724.aspx)?

As long as we have no clue on whet you're trying to do with these
enormous amounts of data, we can't give you a proper answer.

--
Jesse Houwing
jesse.houwing at sogeti.nl