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).
EA4406Asked:
Who is Participating?
 
John844Connect With a Mentor Commented:
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
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.