DataReader - network problems or benefits?

[Doug]

If I am grabbing a lot of rows (say 10,000 from SQL) would I be helping
or hurting the network by using a DataReader? I would think helping
because it's really only returning 1 row at a time, but I'm not sure.
Are there good articles out there that explain how the DataReader does
this as well as any that discuss performance problems/benefits?

 

[Thunder]

Doug,

The Data Reader is one small part of the overall formula. The Data Reader
is well suited for "fire hose" purposes; that is to say pulling data,
walking the data and discarding the data. It's usually better to avoid
"round trips" to the server, and to simply get the data once; one trip when
possible.

Based on your post, I'm not sure exactly what you are trying to accomplish.
What I can tell you is that its rare for a user to comprehend 10,000 rows of
data. so, if you are pulling back this data for a user, then you will want
to limit the rows to something reasonable; say 200 to 500 rows.

If this is for a "data transformation", then you should consider pushing the
logic to the back end and perform the transform in TSQL/PL-SQL. Again,
users cannot comprehend that much data in a GUI and all you are doing is
wasting resources and tasking the server.

The short answer is that a DataReader will out perform a DataAdapter for
obvious reasons. When you start building custom business objects and DALs,
you will find that your use of Data Adapters shrinks in favor of custom
objects and stored procs that manage concurrency, etc.

I hope my word did not confuse you. The answer is that the DR is a better
performer as opposed to a DA.

Michael Bowman
Team Vertical Works

 

[Doug]

Hi Michael,

No you didn't confuse me at all. I'm actually considering using a
DataReader not for a user application but a batch process...I'll be
grabbing large numbers of data like mentioned and storing the data into
a flat file. The concern I had was whether it would be better to use a
DataSet and grab it in bulk or should I do it one row at a time using a
DataReader. From a network point of view, I'm curious as to which way
would perform better and be less problematic for the network. I won't
have any control on the amount of data that comes back...it could vary
each time.

 

[Shawn B.]

DataReader. From a network point of view, I'm curious as to which way

would perform better and be less problematic for the network. I won't
have any control on the amount of data that comes back...it could vary
each time.

When you fill a DataTable/DataSet (which is a collection of DataTables plus
much more), the DataTable internally uses a DataReader to populate its rows.
So in terms of what would be more/less problematic, wouldn't be whether
using a DataReader is better than using a DataTable/DataSet... but rather
your use of the resource.

If you are iterating through 10,000 rows of a DataReader, and do any more
than just populating an internal collection/representation of that data,
such as processing financial statistics or something like that, then you are
actually consuming more processing time with an open connection to the
server than if you just iterate and populate and close (exactly what a
DataSet/DataTable does internally). So in each iteration of the Reader
you'll want to do as little processing as possible before you get the next
row and work with that otherwise you're in danger of over-consuming the
server resources (could be a problem in a high-volume transaction
environment where there are many users such as a web application or web
server or something).

Thanks,
Shawn

 

[Doug]

I would be iterating over each row and right the data to a flat file.
I wouldn't do any other processing than that. Does that sound like a
valid use of a data reader vs a data set?

One other thing. I'm building a generic process where the data
returned could vary from request to request. It is possible that some
of the data I return back could have a lot more columns than others
(yes, we have tables with over a hundred columns believe it or not!),
so it's possible that I could have say 10,000 rows but the number of
columns in each row could be high as well. What I'm trying to do is
come up with the best way to get this data, put it into a flat file and
be done and cause the least amount of problem with network traffic that
I can.

 

[RobinS]

This is from "Practical Guidelines and Best Practices For
Microsoft Visual Basic and C# Developers" by Francesco Balena.
This book is great; has hundreds of small tips that have
greatly enhanced my applications in many ways.

Read database data into a DataSet in the following cases:

a. You must update the database and you want to implement
an optimistic update strategy.

b. You must account for relations existing in different tables.

c. You are binding data to one or more Windows Forms controls.

d. You are binding data to two or more Web Forms controls.

e. You need to store data between consecutive postbacks
in an ASP.Net application.

f. You need to pass data between layers in a multi-tiered
application.

g. You want to cache data, for example, to reduce traffic
on a slow network and improve database performance and scalability.

In all other cases, use a DataReader for processing data
coming from a database.

More Details: In general, processing data by means of a
DataReader is faster than using a DataAdapter to fill a
DataTable in a DataSet. Also, the DataReader doesn't take
any memory either on the server or on the client; therefore,
it is potentially capable of improving scalability. (The
DataSet can also improve scalability and performance by
means of caching; see point g in previous list.)

When binding data from a database table to a *single* Web
Forms control, you can use a DataReader object. If two or
more Web Forms controls are bound to different columns
of the same database table, a DataSet or a DataTable is
a better choice because a DataReader would force you to
read the same table more than once.

Hope that helps.
Robin S.