Speed comparision between datatable and datareader

[Guest]

I would like to use the datatable instead of the datareader so I do not have to worry about developers forgetting to close the datareader to ensure the connection gets closed (using System.Data.CommandBehavior.CloseConnection). But as soon as I say we will only use the datatable then everyone will talk about the speed difference

Can someone point me to documentation showing the speed difference between the two? Is there much of a difference? Is there a persuasive argument to using datatables instead of datareaders

Thanks.

 

[Cor Ligthert]

Hi Darin,

The datareader is not for nothing, when you want to make by instance large
sequential reports normaly the datareader is very adaquat for that.

The dataset(table) is also not for nothing, when you want interactive use of
data, normaly the dataset is very adaquat for that.

Use the tool for what it is made.

Just my thougth

Cor

 

[William Ryan eMVP]

Here are two comparisons and from what I've seen, pretty much tell the
story. The first on focuses on the DataReader vs DataTable exclusively,
http://www.devx.com/vb2themax/Article/19887/0/page/2 The next compares all
data access strategies
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch031.asp


Darin, I say this with all due respect and if I sound condescending please
be assured that nothing could be further from my intent. However, choosing
a data access strategy is a big deal that on any nontrivial app will have
serious ramificiations for many people. Like Cor mentioned, two strategies
are avaiable (at least two) and both have intended uses. Moreover, many
things will perform well in test, and then as things grow, may not scale all
that well.

There are a lot of considerations in this regard and writing off a whole
aspect of methodology is not well advised. To write it off for a reason
like 'you don't want developers to forget to close connections' is
horrifying. The whole presupposition of your question, and oversimplifies
this to a point that it's irresponsible. I'm not saying you are
irresponsible and I know my tone probably seems like I'm lecturing. I'm
not. I just want to point out that making a design decision on something
like this is a recipe for disaster and I don't want to see anyone make this
mistake. I think taking a step back and understanding the whole ADO.NET
paradigm and its architecture would be very beneficial. Check out David
Sceppa's ADO.NET core reference or Bill Vaughn's ADO and ADO.NET Best
practices. Both books explain in detail the many issues that you will
confront. Trust me, it's a lot more involved than developers forgetting to
close open connections. Even if this was a good reason, it'd be easy enough
to work around but that's another story.

Performance isn't the only issue, nor is simplicity. These issues are
seldom black and white and usually involve tradeoffs. If you check out any
of my Efficiently Using ADO.NET xxxx articles at www.knowdotnet.com in the
DataAcess section, I bring up many of these tradeoffs. What works great in
one scenario is often terrible in another and scalablity is a HUGE issue.
Design that ignores this is relying on the hope that change won't happen for
its success. This is ill advised.

I answered the technical aspect of your question with those first two links
but trust me, this isnt' an either or question. You are choosing between a
connected and a disconnected strategy and neither is 'good' across the board
in every situation.

There are many other performance tweak, like connection pooling, query
structure, index strategy table design etc tht will have huge performance
implications, far beyond what object you use to access the data.

The main thing is using the right tool for the right job and employing a
strategy that will bend as needs change.

I'll be glad to elaborate on any of this, so feel free to take me up on the
offer. I'm sure on this one, just about everyone else here will agree with
me on this and would be willing to offer their opinions too.

Whatever you do though, please make your decision on factors more
substantial than the ones mentioned though. Please do yourself a favor and
look into it a lot more - same for your developers. If this is the best
counter point they'd make to such a suggestion, they probably need to either
learn a lot more about ADO.NET, or if they know better reasons and just
didn't mention them, need to speak up more.

Again, I apologize if it sounds like I'm preaching, I've just seen two
projects blow up when I first started over this precise issue (thank God I
don't work there any more) and I can't imagine a scenario where it wouldn't
repeat itslef.

You can opt to not use DataReaders for many reasons, but even datatables use
DataReader behind the scenes as do the command's .Executexxx methods so you
can't swear off connected methodology without forgoing a fair amount. In
some projects this might be safe, but in most it's ill advised. Either way
, making an informed decisioin is the important thing.

Let me know if you have any questions.

Bill
--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

I would like to use the datatable instead of the datareader so I do not

have to worry about developers forgetting to close the datareader to ensure
the connection gets closed (using
System.Data.CommandBehavior.CloseConnection). But as soon as I say we will
only use the datatable then everyone will talk about the speed difference.

Can someone point me to documentation showing the speed difference between

the two? Is there much of a difference? Is there a persuasive argument to
using datatables instead of datareaders?

 

[William Ryan eMVP]

Here's another link you may find helpful
http://msdn.microsoft.com/msdnmag/issues/04/06/datapoints/default.aspx

--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

I would like to use the datatable instead of the datareader so I do not

have to worry about developers forgetting to close the datareader to ensure
the connection gets closed (using
System.Data.CommandBehavior.CloseConnection). But as soon as I say we will
only use the datatable then everyone will talk about the speed difference.

Can someone point me to documentation showing the speed difference between

the two? Is there much of a difference? Is there a persuasive argument to
using datatables instead of datareaders?

 

[Guest]

Thank you for the excellent replies.

Another issue I had with the datareader is the bug mentioned in this article: http://msdn.microsoft.com/library/d...mag03/html/The_NETConnectionPoolLifeguard.asp.

Has this issue effected your decission to use the datareader?

 

[William Ryan eMVP]

Actually, it's not something that has been discussed much. It's quite easy
to structure a block of code that ensures that the connection close and
we've been fanatical about it in our design so it's never really been a
concern. The fact that readers are used under the hood for just about every
task is the impitus for my attitude about their safety. If used with juts a
minor bit of caution (and finally blocks), they aren't going to be a
problem. Sure, you can't pass them through layers safely, and sure, they
are only valid in the context of having a connection available. I see
people messing this up more than anything else. The closing connection
issue is usually remedied during testing b/c people get the errror message
and take corrective action. There's always room for error but by and large,
this isn't nearly as much of a problem as say, people trying to pass a
reader through a a layer or two, and not passing connections or any other
number of stuff like that. Same holds for people trying to access a
connection on multiple threads at the same time and a few similarl problems.
Not really issues with the Reader per se but usage.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

 

[Eric Marvets]

Wow, thats the first I've seen of that article. I use DataReaders primarily
in my ASP.NET designs, as DataSets are designed for more stateful
environments, like a Windows Forms app, or where XML is the main method of
transport, such as in a WebService. I use datasets for some application
wide useages, like maintaining a list of groups or if its a relatively small
list of products that is rarely updated.

That being said, I prefer to send business objects from the business layer
to the UI. Even in a webservice, I prefer to define XML serialization for
business objects, rather than use a dataset (to have the option of exposing
a Service Oriented Architecture to other systems as well as use it from its
intended ASP.NET UI). I only use DataReaders for simple dropdowns or
datagrids for reporting, in which case, the issue disscussed would have
little bearing.

To get around the issue, I can see a implementing a simple wrapper class,
that contains the datareader and also the connection object. If it
implements the IDisposable interface, you could close the datareader and
connection explicitly. You still must force developers to call close, which
i see no option other than to beat some programming standards into their
heads.

At a company we all used to work for, Extreme Logic before it was bought out
and destroyed by HP and we started up this little company, we were all payed
by our performance. Our bonus structure was definitley part of our pay.
This entry into the bonus structure was Quality of Work. If the quality of
your work, i.e. closing objects, proper exception handling, or just plain
ol' good code was lacking, then it didn;t matter what your utilization was
or what internal projects you assisted in, you didn't get paid (and 1 more
qtr of that you would quit or be fired). Its amazing the quality of work
that gets produced when someone is watching and there are significant
rewards and penalties involved.

--
Eric Marvets
Principal Consultant

the bang project

<shameless self promotion>

Email (e-mail address removed) for Information on Our Architecture and
Mentoring Services

</shameless self promotion>

 

[Sahil Malik]

The right solution would be a combination of both. Always remember,
DataReader is connected, and DataSet is not. You most certainly don't want a
webservice to use a datareader and keep your connections open till the
second webservice call.

The exact situation and need will dictate what works better. Ignoring either
completely is not wise.

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/

I would like to use the datatable instead of the datareader so I do not

have to worry about developers forgetting to close the datareader to ensure
the connection gets closed (using
System.Data.CommandBehavior.CloseConnection). But as soon as I say we will
only use the datatable then everyone will talk about the speed difference.

Can someone point me to documentation showing the speed difference between

the two? Is there much of a difference? Is there a persuasive argument to
using datatables instead of datareaders?

 

[Guest]

If I do the following
SqlConnection connection
using(connection = new SqlConnection(myconnectionstring)

connection.Open()
// do wor


Do I have to worry about closing the connection after retrieving data with the datareader? Won't the "using" keyword call the connection's disposable method at the appropriate time?

 

[Jon Skeet [C# MVP]]

If I do the following:
SqlConnection connection;
using(connection = new SqlConnection(myconnectionstring))
{
connection.Open();
// do work
}

Do I have to worry about closing the connection after retrieving data
with the datareader? Won't the "using" keyword call the connection's
disposable method at the appropriate time?

Yes it will.

I would also use the using statement for any DataReaders, Transactions
etc that you create. Basically anything that implements IDisposable and
which has a simple lifetime.