?
Solved

DataAdapter.Update speed problems

Posted on 2003-03-26
12
Medium Priority
?
409 Views
Last Modified: 2010-05-18
I have a large (>1M records) table in MS SQL Server 2000 with values that need to be updated. Changing the values in the table and using the Update method of DataAdapter works, but takes nearly 10 minutes. Using a similar query and executing from Query Analyzer takes under 15 seconds. Why the huge discrepancy? Anything I can do to mitigate the problem?
0
Comment
Question by:marcgale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 10

Expert Comment

by:GoodJun
ID: 8211686
use the .getchanges method to get only the changed records before run .update method.
0
 
LVL 1

Author Comment

by:marcgale
ID: 8211821
Good suggestion - I'll try it, but probably not until tomorrow (just didn't want you to think I'd abandoned my post). Thanks.
0
 
LVL 1

Author Comment

by:marcgale
ID: 8214366
I'm changing every record. .getchanges() would only succeed in using twice the memory.
0
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 5

Accepted Solution

by:
TRUENEUTRAL earned 400 total points
ID: 8214441
If you are changing every record, you are probably doing it programmatically, rather than letting the user change it.  I presume you are sending some sort of update statement to the table in the dataadapter.

I think the dataadapter.update method cannot take advantage of indexes on the table when doing such an update.

I would probably perform much faster (and take advantage of the indexes) if you issued the update statement directly to the database through a connection object.
0
 
LVL 1

Author Comment

by:marcgale
ID: 8214527
"I would probably perform much faster (and take advantage of the indexes) if you issued the update statement directly to the database through a connection object. "

I agree. As noted in my original my original post, I might even expect to go from 10 minutes to 15 seconds! So much for my middle tier.

*flame* .net is ridiculous: attempts to hide the database from the erstwhile application developer, but can't take advantage of indices? This seems pretty basic. Grrr.
0
 
LVL 5

Expert Comment

by:TRUENEUTRAL
ID: 8214561
<MyOpinion>
I do not permit any of my developers to utilize the dataadapter or dataset in this manner.  It smacks too much of RDO bound objects, databinding, slow applications, and untraceable bugs.
</MyOpinion>
0
 
LVL 1

Author Comment

by:marcgale
ID: 8214608
No disagreement as far as the slow applications argument goes - it certainly is.

It annoys me that in many situations, VB is to be relegated to doing nothing more than assembling SQL queries, using mind-numbing combinations of stringbuilder.appends() and if-thens/select cases. So much for "hiding" the database structure from the business logic.
0
 
LVL 5

Expert Comment

by:TRUENEUTRAL
ID: 8217485
<MyOpinion>
I have never been in favor of hiding anything from the developer.
</MyOpinion>
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 8218220
use the adapter.update method is the slowest way to update database. If you update lots of records, and speed is an issue, you better use command object to issue sql statement, or even think about use stored procedure if the speed is still an issue.
0
 
LVL 1

Author Comment

by:marcgale
ID: 8218324
"So much for "hiding" the database structure from the business logic."

Note: business object <> developer.

Objects whose responsibility it is to apply the business logic of the application should not be required to have knowledge of the underlying database structure. They shouldn't even care, necessarily, that a database exists. A separate set of objects, who can access the database and present the information found therein in a consistent manner, should have sole responsibility for communicating with those objects responsible for the business logic. Were this not the case, every component of the system would have to be rewritten whenever the database changed! I don't make this stuff up - this is standard n-tier development.

<SadRealization>
Initially, it seemed to me that the .Net structure had made significant strides in enabling this type of application development. Unfortunately, the more I learn, the less I find it to be the case and the more I'm forced to employ the same sorry workarounds I'd hoped to avoid.
</SadRealization>

<Acknowledgement>
Though it did not solve my problem, your observation as to why the .Update method was so slow did, at least, enlighten me as to why it might not be solvable. Thus, I accept it as the answer.
</Acknowledgement>
0
 
LVL 1

Author Comment

by:marcgale
ID: 8218394
0
 
LVL 5

Expert Comment

by:TRUENEUTRAL
ID: 8219241
thanks for the points

n-tier development:
the workaround we have had to use involves creating business logic objects that communicate with database api objects (objects that have the database logic, and yes, sql written in them).  

We try to use the same set of database api objects for each program, so when the database changes, we rewrite the database api's and copy them to all of the production apps.  

However, this is lett than perfect... I feel you pain.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question