473,320 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Performance issue!!

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
5 1997
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
0
by: Mortisus | last post by:
Hi, I'm running a fulltext query on a ~50000 record mysql database and while performance is usually satisfying - about 0.02 secs per query, i get a critical performance deterioration when looking...
1
by: bob | last post by:
Currently i'm writing some low level communication modules in C++ and is thinking of putting it into a library so that it can be used in C#. My concern is the performance issue when putting C++...
17
by: 57R4N63R | last post by:
I'm currently building a website for one of the client. There has been few errors here and there, but just recently the problem is getting worse. Basically the symptoms is that when the user try...
16
by: D. Stimits | last post by:
A non-profit organization is interested in a new data application that would use a SQL storage system. I'm interested to know how non-profit companies that are not selling products are considered...
1
by: Raja Shekar | last post by:
HI Everybody , This is the DB2 performance issue I was referring to . We have requirement where we need to insert some values to the DB2 table and get back its ID. Its possible by using an...
10
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the...
0
by: Shades799 | last post by:
Hi All, I was wondering if any of you could help me with a very difficult problem that I am having. I have an ASP site that works with an Oracle database using an ADODB connection. This...
7
by: Andres Rormoser | last post by:
I'm having a performance Issue with my Production SQL Server (2 x Xeon Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the querys take much longer than the querys in my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.