DataReaders - networking 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?

 

[Marc Gravell]

Well, the nature of the connection means that it is (via most routes) only
/returning/ 1 row at a time - it is just that DataTable etc build them all
into a single mass before completing. Most of the overhead here is on the
client, not the network or server. Arguably, you could say that since the
DataTable approach uses more resources, it is likely to be a little slower,
and so pester the server for longer. The network probably won't mind either
way as long as the same number of trips and records are involved.

Perhaps a better question is: can you avoid bringing it over the network at
all? Can you aggregate etc at the server, reduce the columns (don't use "*")
/ rows ("WHERE") fetched, things like that...

Marc

 

[mark_overstreet]

DataSets (DataTables) use a DataReader under the covers any way so it
is the same result.

 

[Dave Sexton]

Hi Doug,

As opposed to what other mechanism?

DataReader is used internally by DataAdapter, so I really don't see any other
fully-managed choice but to directly or indirectly use a DataReader.

Also, the latency depends on the amount of data in each row. If each of the
10000 rows are reasonably small in size the operation should be quick on a
LAN. Figure that if each row has a maximum size of 100 B then the total size
of the 10000 rows of data won't exceed 1 MB (+ protocol control bits). On a
10Mb/s LAN the total latency would be ~1 second.

I agree with Marc that your choice of queried data is probably more important
than how you get it into your application. After all, decreasing the size of
a returned row, 10000 times, is going to help out more than switching the
mechanism that downloads them.

If you are still seeing performance problems, then mock up a program to
compare the performance with a DataReader vs. a DataReader used by a
DataAdapter vs. any other mechanism you have in mind. Choose the quickest of
them all

 

[Doug]

DataSets (DataTables) use a DataReader under the covers any way so it
is the same result.

Is there any difference between the two as far as how much data is sent
over the network at one time?

 

[Dave Sexton]

Hi Doug,

DataSets and DataTables don't use DataReaders. They are objects that hold
data in-memory.

 

[Yury]

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.

1) Network usage is the same for both DataAdapter and DataReader (like
Dave said DataAdapter use DataReader)

Are there good articles out there that explain how the DataReader does
this as well as any that discuss performance problems/benefits?

2) Difference in usage of that data. If you want to display rows in
datagrid then use DataAdapter+DataTables (10k rows in one datagrid not
very useful for user, so paging may help). If you want to dump data to
file or smth like that - DataReader is preferred choice because you
don't have all 10k rows in memory at the same time.

look at part IV here:
http://download.microsoft.com/download/6/4/3/643c270e-5f43-48c7-a0cd-0eb1a6c7c4f0/ScaleNet.pdf

 

[Yury]

look at part IV here:
http://download.microsoft.com/download/6/4/3/643c270e-5f43-48c7-a0cd-0eb1a6c7c4f0/ScaleNet.pdf

sorry, Part III, Chapter 12 is yours %). But all parts of this paper is
very useful ;-).

 

[Kevin Spencer]

Correct, but they are populated using TableAdapters, which use DataReaders
internally.

--
HTH,

Kevin Spencer
Microsoft MVP
Ministry of Software Development
http://unclechutney.blogspot.com

I just flew in from Chicago with
a man with a wooden leg named Smith
who shot an elephant in my pajamas.
So I bit him.

 

[Kevin Spencer]

Correction, I mean DataAdapters.

--
HTH,

Kevin Spencer
Microsoft MVP
Ministry of Software Development
http://unclechutney.blogspot.com

I just flew in from Chicago with
a man with a wooden leg named Smith
who shot an elephant in my pajamas.
So I bit him.

 

[Dave Sexton]

Right, and I mentioned that already in another post in this thread.