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 );
> 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.
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.
<jesse.houw...@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.
> 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
> On Tue, 20 Oct 2009 16:54:15 +0200, Jesse Houwing > <jesse.houw...@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.
|
