ExamGuru IT/.Net Guru : Reading through large datasets in blocks on microsoft.public.dotnet.framework.performance

.NETGURU

Reading through large datasets in blocks

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.performance.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Rob via .Net Guru (VIP)

Hello All,

I'm trying to process a couple of gigs worth of data, which I have to process in blocks of an unknown size to be determined from the data itself. If I use a DataAdapter on the whole set of data, the docs tell me it will load the whole mess into memory, and I haven't got a couple of gigs to spare. If I use a DataReader to get the data one line at a time the docs tell me it won't do this, which sounds better, but its very vague about how this works:

"Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader."

Am I just pushing the memory burden back onto my SQL server, which has to hold the entire result set until I clear out enough space in my "network buffer" by reading lines of data? Or is it doing some clever shorthand to pause the query until the next batch of results is requested?

And while I'm at it, does anyone know a good way to get the rows from the DataReader into a DataSet, so I can store them up until I have a group big enough to process? I can't seem to find a clear way to go from one to the other, despite the fact that I'm told DataAdapters do exactly this...

Thanks,

- rob.

-----------------------
Posted by a user from .Net Guru (http://www.dot-net-guru.com/)

<Id>VQKgqN88skW7AhGwz3U0ig==</Id>

Reply to this message...

Bob Grommes

Rob,

A DataReader is read-only, forward-only -- what is known as a "firehose
cursor". I am not certain what the resource situation is on the server for
a large result set but have gotten the impression in the past that it does
not have to fetch the entire result at once, even on the DB server. That
particular question might be better asked on either the ado.net newsgroup or
one of the SQL Server newsgroups.

There is currently no built-in support that I know of for filling a DataSet
from a DataReader, but in VS 2005 / CLR 2.0, there will be new overloads to
DataAdapter.Fill() that will take a DataReader:

Fill(DataTable,IDataReader);
Fill(DataTable[],IDataReader,int,int);
Fill(DataSet,string,IDataReader,int,int);

In the meantime you would so far as I know have to cruft up something
yourself.

--Bob

"Rob via .Net Guru" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
Hello All,

I'm trying to process a couple of gigs worth of data, which I have to
process in blocks of an unknown size to be determined from the data itself.
If I use a DataAdapter on the whole set of data, the docs tell me it will
load the whole mess into memory, and I haven't got a couple of gigs to
spare. If I use a DataReader to get the data one line at a time the docs
tell me it won't do this, which sounds better, but its very vague about how
this works:

"Results are returned as the query executes, and are stored in the network
buffer on the client until you request them using the Read method of the
DataReader."

Am I just pushing the memory burden back onto my SQL server, which has to
hold the entire result set until I clear out enough space in my "network
buffer" by reading lines of data? Or is it doing some clever shorthand to
pause the query until the next batch of results is requested?

And while I'm at it, does anyone know a good way to get the rows from the
DataReader into a DataSet, so I can store them up until I have a group big
enough to process? I can't seem to find a clear way to go from one to the
other, despite the fact that I'm told DataAdapters do exactly this...

Thanks,

- rob.

-----------------------
Posted by a user from .Net Guru (http://www.dot-net-guru.com/)

<Id>VQKgqN88skW7AhGwz3U0ig==</Id>

Reply to this message...

	System.Data.Common.DataAdapter
	System.Data.DataSet
	System.Data.DataTable
	System.Data.IDataReader

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification