DataReaders Vs DataSets

[John Smith]

Hey folks,

Since in the windows form world, you can't bind a SqlDataReader to a
DataGrid, but you can bind a DataSet, would it be quicker to:

a) Return data to a SqlDataReader. Then loop through it and add the data to
the DataGrid manually 1 row at a time.
b) Return data to a DataSet and bind it to the DataGrid

I'm asking because I know DataSets have extra overhead which if not being
utilized is essentially a waste.

Thanks!

 

[novelle.vague]

Hey folks,

Since in the windows form world, you can't bind a SqlDataReader to a
DataGrid, but you can bind a DataSet, would it be quicker to:

a) Return data to a SqlDataReader. Then loop through it and add the data
to the DataGrid manually 1 row at a time.
b) Return data to a DataSet and bind it to the DataGrid

I'm asking because I know DataSets have extra overhead which if not being
utilized is essentially a waste.

Thanks!

They also don't support the return of joins or stored procedures.

 

[Marina]

The answer to this questions is as easy as writing a small test program to
look at the times. Select 10,000 rows in each case, and try both scenarios.
This should only take a few minutes - certainly faster then waiting for a
response in the newsgroup.

 

[Miha Markic [MVP C#]]

John,

DataGrid needs a data source, thus DataSet is just fine.

 

[Cor Ligthert]

Marina,

The answer to this questions is as easy as writing a small test program to
look at the times. Select 10,000 rows in each case, and try both scenarios.
This should only take a few minutes - certainly faster then waiting for a
response in the newsgroup.

What is it easy when you are a woman and may write such direct and true
answers.

As forever I fully agree of course.

(Except that I would add, and more sure).



Cor

 

[Marina]

Marina,



What is it easy when you are a woman and may write such direct and true
answers.

As forever I fully agree of course.

(Except that I would add, and more sure).



Cor

 

[John Smith]

Hmm...appears you're right. I just assumed you could add rows to it, or
take an empty one and populate it yourself. Guess not. A bit surprising.

Also surprising that you can't bind a SqlDataReader to it.

Oh well.

John,

DataGrid needs a data source, thus DataSet is just fine.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hey folks,

Since in the windows form world, you can't bind a SqlDataReader to a
DataGrid, but you can bind a DataSet, would it be quicker to:

a) Return data to a SqlDataReader. Then loop through it and add the

data

to
the DataGrid manually 1 row at a time.
b) Return data to a DataSet and bind it to the DataGrid

I'm asking because I know DataSets have extra overhead which if not being
utilized is essentially a waste.

Thanks!

 

[Cor Ligthert]

John,

It is very simple a datagrid in windowforms holds no data only references.
The data has to be somewhere, and although the dataset holds as well no
data, is that the best reference box to get to the data for the windowforms
datagrid.

I hope this helps?

Cor
..

 

[William \(Bill\) Vaughn]

This is a question as old as ADO.NET itself.
The DataAdapter Fill method uses the DataReader to fetch rows. Its overhead
is slight until the size of the DataTable(s) it creates start getting large.
That's because it has to allocate memory and store the data--a DataReader
does not. In most cases we want to manage the data in a variety of ways when
we display it in a WinForms application. We want to sort, filter, find and
update it. That's what a DataSet is for. Writing your own code to do what
Fill does is inefficient and if you worked for me, I would send you back to
use Fill instead. Many shops don't permit their developers to use the
DataReader as they are the source of a number of other issues--especially in
Web applications. Part of the problem is that the DataReader requires you to
manually control the connections. Get it wrong and you end up leaking
connections.

In ADO 2.0 we'll find it even easier to use DataReaders to build bindable
DataTables or DataSets with multiple resultsets or even the rowsets
generated from SPs. You'll also be able to take a DataSet and generate a
DataReader from the rowsets. I'll be discussing all of this in my Workshop
at VSLive in Orlando next month and in my SQL Immersion session in Chicago
in October.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

Huh? Both the Fill and DataReader Execute methods support multiple
resultsets and rowsets returned from stored procedures.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

Sigh, as an interesting as this might seem, testing ADO with 10,000 rows to
see how it performs is like loading a bicycle with 800 pounds of rocks to
see how well it can climb a hill. It's not typical of how it should be used.
ADO has never been a good way to handle bulk data. It's a query interface,
not a bulk copy utility.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Marina]

Well, if one is testing which method is faster, then it certainly works.
Testing it with 5 rows is not likely to yield accurate results.

 

[William \(Bill\) Vaughn]

Yes, but does this test really "scale"? In other words, will a 10,000 row
test accurately reflect what happens when you use a "typical" (10-100 row)
query with 100 or 1000 users? I suspect it won't.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Marina]

Well, if someone wants to know which method is faster in general - then you
have to do it with a large set of data.

Now, for small result sets, most sets of methods are similar - because if
you are talking about 10 rows, well, those few milliseconds are not going to
make a difference to the user.

But the question was specifically which is faster. So I suggested to test
out - and the only way to tell which is truly better performing was to test
with large sets of data.

 

[John Smith]

Thanks a lot everyone. You were very helpful.

DataSet it is and I'll stop whining.

 

[John Smith]

Thanks for the info. Very helpful.

I did a test similar to what you suggest except I was loading a ListView

one record at a time instead of a DataGrid. I discovered two things in my
testing:

1. There is no significant extra overhead using a DataSet when used to

populate a DataGrid. Not significant enough to worry about anyway.

2. Binding a DataGrid to a DataSet is extremely fast compared to loading

a ListView one row at a time from a DataReader.

 

[John Smith]

Thanks for helping to clear that up for me. Explains many of my confusions.

 

[Cor Ligthert]

Marina and Bill,

We disagree this time a little bit.

I think that a better test would be to do it in a loop of a read/fill of
100rows and than in a loop from 1000 times.

(That can than include the connection open and close).

And than of course to compare right also with a test of 1 times 10000 rows.

I think that for this approach the statement from Bill about 100 users is
not valid because this is about datareader and dataset which both use in
fact the datareader.

Just my thought,

Cor

 

[Jonathan Allen]

2. Binding a DataGrid to a DataSet is extremely fast compared to loading
a ListView one row at a time from a DataReader.

Did you wrap the DataReader code with BeginUpdate and EndUpdate?

--
Jonathan Allen

I did a test similar to what you suggest except I was loading a ListView

one record at a time instead of a DataGrid. I discovered two things in my
testing:

1. There is no significant extra overhead using a DataSet when used to

populate a DataGrid. Not significant enough to worry about anyway.

2. Binding a DataGrid to a DataSet is extremely fast compared to loading

a ListView one row at a time from a DataReader.