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

My ADO app slows down as I exceed processing 100,000 records

I have a SQL db that has 500,000 records with 11 columns.  I'm updating 1 column and the logic is very simple: Read a record, combine 2 columns into 1, and update.  The counters fly throught the first 20,000 then it starts to slow down.  By the time it hits 100,000 records
it might process 1 record every 10 seconds.  At this rate, it'll take a week to finish.  Is their some kind of buffer allocation I can do?  This is not a complicated process (about 7 lines).
0
EA4406
Asked:
EA4406
1 Solution
 
John844Commented:
try creating a simple update statement to update the records without having to iterate through a recordset.
0
 
mcmahon_sCommented:
Why not issue an SQL statement and the SQL do the work eg.

UPDATE table SET column1 = column2+column3

this will execute loads faster
0
 
agriggsCommented:
If it's not a complicated process write a stored procedure in T-SQL to do it for you.  That way there will be no network traffic or buffering to slow you down.  It can almost certainly be done with a cursor.  If you need help, post the code and I'll tell you for sure.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
John844Commented:
update tblYourTableName SET col1=col2+col3 WHERE <your where clause used to build original select statement for recordset>.

You might have to do conversions on any numeric datatypes if they are in col2 or col3 using convert() or cast()
0
 
DreamMasterCommented:
You might do this using a stored procedure....while your webserver will just execute the stored procedure...it is done after that....the stored procedure runs independant of the webserver and should be much faster...

Max.
0
 
DreamMasterCommented:
Sorry....stuck in webprogramming...

I mean...it is not relying on your VB program....so it can operate in the background....

Max.
0
 
anthony_glenwrightCommented:
I used to have the same problem with Access, and I solved it by using (back in DAO days) BeginTrans and CommitTrans at the start and end of the loop, and having a line in the middle like:

  counter=counter+
  if counter > 500 then
    connection.Commit
    connection.BeginTrans
    counter=0
  end if

Perhaps you can try something like that.
0
 
twalgraveCommented:
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- points to John844
Please leave any comments here within the
next seven days.
0
 
moduloCommented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now