473,746 Members | 2,709 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2030
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.c omwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.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.c omwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.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, interoperabilit y, 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 interoperabilit y, extensibility, and
maintainability . The DataTable and DataSet classes are ubiquitous in the
..Net Framework, and therefore provide much greater interoperabilit y. 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 interoperabilit y, 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.c omwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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
extensibilit y/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.c omwrote in message
news:11******* *************** @k70g2000cwa.go oglegroups.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, interoperabilit y, 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 interoperabilit y, extensibility, and
maintainability . The DataTable and DataSet classes are ubiquitous in the
.Net Framework, and therefore provide much greater interoperabilit y. 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 interoperabilit y, 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.c omwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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.c omwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.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
5224
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 includes the following columns: DocID (INTEGER, PRIMARY KEY, CLUSTERED) IsRecord (INTEGER, NONCLUSTERED)
0
1257
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 for popular keywords (words that appear in more than 50% of the data) - about 0.25 secs/query. I know that the standard fulltext engine does not return any results on such words, and this is good, but this does not explain the performance issue....
1
1356
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++ codes into C#. Does anyone has any idea(s) on this issue????
17
2061
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 to access the page, it takes really long time to load. However, after up to 1 hour, the website will run fine again as normal. This issue has been there with the site. I usually just ask the system admin to restart the IIS Service. However, the...
16
1942
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 for licensing. Can they use PostgreSQL just like anyone else or do non-profits qualify as commercial use and need to consider other licensing? D. Stimits, stimits AT comcast DOT net ---------------------------(end of...
1
1534
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 insert query , followed by select ID query. (Using IDENTITY_LOCAL_VAR) However what we are looking for is (ofcourse, an ideal case) a single query
10
4300
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 application looks for existing rows in the table...if they already exist then it updates otherwise inserts them. The table is pretty large, around 6.5 million rows.
0
1483
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 connection is stored in a .dll file. This site had been working fine. However recently we upgraded our Oracle database from 9i to 10g and ever since then we have been having serious performance problems with this site only. The website works fine...
7
1778
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 development server . For example a recursive UDF takes 20s in my development server and 2m in my production server (both with same users load) but my production server it's much hardware powerfull than the other server. I start monitoring an realized...
0
8974
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9508
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9350
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9218
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8229
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4586
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4836
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2199
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.