• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

DataAdapter.Update speed problems

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
marcgale
Asked:
marcgale
  • 6
  • 4
  • 2
1 Solution
 
GoodJunCommented:
use the .getchanges method to get only the changed records before run .update method.
0
 
marcgaleAuthor Commented:
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
 
marcgaleAuthor Commented:
I'm changing every record. .getchanges() would only succeed in using twice the memory.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TRUENEUTRALCommented:
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
 
marcgaleAuthor Commented:
"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
 
TRUENEUTRALCommented:
<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
 
marcgaleAuthor Commented:
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
 
TRUENEUTRALCommented:
<MyOpinion>
I have never been in favor of hiding anything from the developer.
</MyOpinion>
0
 
GoodJunCommented:
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
 
marcgaleAuthor Commented:
"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
 
marcgaleAuthor Commented:
0
 
TRUENEUTRALCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now