By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

Performance issue!!

P: n/a
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!

Oct 27 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
In terms of pure efficiency, the first option is more efficient, at least in
the short run. But you may want to think in terms of the long run, and
extensibility/maintenance as well.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada
"Varangian" <of****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!

Oct 27 '06 #2

P: n/a
hmmm.... IC

so what you mean.. is that in an Object Class I have to Add a Property
(Field) ...while using a DataAdapter I don't need to?
thanks again!

Kevin Spencer wrote:
In terms of pure efficiency, the first option is more efficient, at least in
the short run. But you may want to think in terms of the long run, and
extensibility/maintenance as well.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada
"Varangian" <of****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!
Oct 27 '06 #3

P: n/a
Varangian wrote:
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!
We use DataReaders to create business objects, and have made some
performance testing on this. We found that it's something like 30%
faster than using DataSets.

That's a bit faster, but nothing that will really take you out of any
performance issues. You should rather look at what you are doing, and
how you fetch the data from the database. For example adding indices on
the right fields in the database might make a query something like
10000% faster...
Oct 27 '06 #4

P: n/a
so what you mean.. is that in an Object Class I have to Add a Property
(Field) ...while using a DataAdapter I don't need to?
Well, you only posted 2 options out of many, in terms of what you can do to
store database data in memory. The first involved using a SqlDataReader to
populate an "ArrayList of Object Instances," while the second involved using
a SqlDataAdapter to fill a DataTable or a DataSet. In fact, there are
several different ways to store database data in memory, and none of them
are strictly "the best" (overall, not just in terms of performance).

When designing software, one has to take multiple factors into account,
including performance, interoperability, extensibility, and maintainability.
Remember that performance enhances the application to a certain extent, but
the most expensive aspect of software development is the human resources
consumed in developing, extending, and maintaining the software.

A SqlDataReader is very fast, and is, in fact, used by a SqlDataAdapter in
the process of filling a DataTable or a DataSet. But there are situations
where a DataTable or a DataSet are not necessary, and situations where they
are much better in terms of interoperability, extensibility, and
maintainability. The DataTable and DataSet classes are ubiquitous in the
..Net Framework, and therefore provide much greater interoperability. They
are also serializable as XML. In addition, they can provide schema
information about the table and/or the database itself, including such
things as relationships, constraints, nullability, etc.

While an "ArrayList of Object Instances" is ubiquitous as well, it is too
non-specific to provide much in terms of interoperability, without using
Reflection. It also has none of the database properties that the DataTable
and DataSet provide. And it is not strongly-typed.

Using a SqlDataReader to populate a DataTable is more interoperable and
extensible, but does not provide the database properties I mentioned, which
are fetched by a SqlDataAdapter.

In other words, there are reasons why there are many ways to work with
database data in the .Net Framework. When considering what is best overall,
one must weigh the present and possible future requirements, and the
characteristics of the various available tools, against the performance
gains and losses, and the human resource expense that will be incurred with
each possible solution.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada

"Varangian" <of****@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
hmmm.... IC

so what you mean.. is that in an Object Class I have to Add a Property
(Field) ...while using a DataAdapter I don't need to?
thanks again!

Kevin Spencer wrote:
>In terms of pure efficiency, the first option is more efficient, at least
in
the short run. But you may want to think in terms of the long run, and
extensibility/maintenance as well.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada
"Varangian" <of****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googleg roups.com...
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!

Oct 28 '06 #5

P: n/a
Hi again... thanks for the reply and time in replying :)

human resource is not an issue in this although I agree it would be
issue with many...

one question .. is SqlDataAdapter always slow irrespectible of how many
data you retrieve or becomes really slow when it starts exceeding an
amount of say 100 records or so?

thanks again!
Kevin Spencer wrote:
so what you mean.. is that in an Object Class I have to Add a Property
(Field) ...while using a DataAdapter I don't need to?

Well, you only posted 2 options out of many, in terms of what you can do to
store database data in memory. The first involved using a SqlDataReader to
populate an "ArrayList of Object Instances," while the second involved using
a SqlDataAdapter to fill a DataTable or a DataSet. In fact, there are
several different ways to store database data in memory, and none of them
are strictly "the best" (overall, not just in terms of performance).

When designing software, one has to take multiple factors into account,
including performance, interoperability, extensibility, and maintainability.
Remember that performance enhances the application to a certain extent, but
the most expensive aspect of software development is the human resources
consumed in developing, extending, and maintaining the software.

A SqlDataReader is very fast, and is, in fact, used by a SqlDataAdapter in
the process of filling a DataTable or a DataSet. But there are situations
where a DataTable or a DataSet are not necessary, and situations where they
are much better in terms of interoperability, extensibility, and
maintainability. The DataTable and DataSet classes are ubiquitous in the
.Net Framework, and therefore provide much greater interoperability. They
are also serializable as XML. In addition, they can provide schema
information about the table and/or the database itself, including such
things as relationships, constraints, nullability, etc.

While an "ArrayList of Object Instances" is ubiquitous as well, it is too
non-specific to provide much in terms of interoperability, without using
Reflection. It also has none of the database properties that the DataTable
and DataSet provide. And it is not strongly-typed.

Using a SqlDataReader to populate a DataTable is more interoperable and
extensible, but does not provide the database properties I mentioned, which
are fetched by a SqlDataAdapter.

In other words, there are reasons why there are many ways to work with
database data in the .Net Framework. When considering what is best overall,
one must weigh the present and possible future requirements, and the
characteristics of the various available tools, against the performance
gains and losses, and the human resource expense that will be incurred with
each possible solution.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada

"Varangian" <of****@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
hmmm.... IC

so what you mean.. is that in an Object Class I have to Add a Property
(Field) ...while using a DataAdapter I don't need to?
thanks again!

Kevin Spencer wrote:
In terms of pure efficiency, the first option is more efficient, at least
in
the short run. But you may want to think in terms of the long run, and
extensibility/maintenance as well.

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada
"Varangian" <of****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hi,

I have a performance issue question?

which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader OR using SqlDataAdapter to Fill a DataSet or DataTable ?

Thanks!
Oct 30 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.