DataReader Vs DataSet

At several times in my code I am opening and reading data from data reader.

Would using the DataSet instead improve the performance? As few times in my
code , to get items one by one while the connection is still open, sound
like a resource hog to me...

Any suggestions?

Sahil
Tue Apr 05 11:49:01 CDT 2005

Mavrick -

You are damn right, getting the items one by is a resource hog, especially
because it keeps the connection open until the last item is fetched.

But a dataset when filled, does essentially the same thing.

A Dataset's penalty is - a larger memory footprint, and the need to fill all
the data before you see the first row. Plusses are - once you have it, the
connection can be reused.

A DataReader's penalty is - keeping the connection open.

So if you have not tomes of data, and you need to do processing between rows
(including databinding), then a dataset is a better choice - since that
allows for better conn. pooling. (There are other reasons too - editability,
not forward only etc.).

But if you have a situation where streaming the data makes sense, then
datareader maybe a better choice.

If you have a situation where streaming doesn't make sense, and you have
tonnes of data, then using your custom business object to save the dataset
heavy footprint, and filling that from a datareader may make sense.

HTH :)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/




"mavrick101" <mavrick101@discussions.microsoft.com> wrote in message
news:C732A807-C0DE-4813-BA23-A8404C579A76@microsoft.com...
> At several times in my code I am opening and reading data from data
reader.
>
> Would using the DataSet instead improve the performance? As few times in
my
> code , to get items one by one while the connection is still open, sound
> like a resource hog to me...
>
> Any suggestions?

Val
Tue Apr 05 19:11:02 CDT 2005

Hi,

I believe it depends on what kind of data you get and what is the amount of
it you need to load to the client. If your records dynamic,then loading them
into static dataset could lead to the fact that DataSet will not hold
current records. You should weight the fact if you need to synchronize
dataset with the database vs. using reader. In a case if your SQL statement
fetches small amount of data, I do not think you will gain much from the
performance point of view.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"mavrick101" <mavrick101@discussions.microsoft.com> wrote in message
news:C732A807-C0DE-4813-BA23-A8404C579A76@microsoft.com...
> At several times in my code I am opening and reading data from data
> reader.
>
> Would using the DataSet instead improve the performance? As few times in
> my
> code , to get items one by one while the connection is still open, sound
> like a resource hog to me...
>
> Any suggestions?