Solved

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

Posted on 2001-06-07
9
173 Views
Last Modified: 2010-05-02
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
Comment
Question by:EA4406
9 Comments
 
LVL 7

Expert Comment

by:John844
ID: 6163997
try creating a simple update statement to update the records without having to iterate through a recordset.
0
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 6164001
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
 
LVL 2

Expert Comment

by:agriggs
ID: 6164008
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 7

Accepted Solution

by:
John844 earned 50 total points
ID: 6164011
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
 
LVL 19

Expert Comment

by:DreamMaster
ID: 6164258
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
 
LVL 19

Expert Comment

by:DreamMaster
ID: 6164265
Sorry....stuck in webprogramming...

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

Max.
0
 
LVL 6

Expert Comment

by:anthony_glenwright
ID: 6170253
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
 
LVL 16

Expert Comment

by:twalgrave
ID: 7707636
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
 

Expert Comment

by:modulo
ID: 7758276
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA combine two working workbooks 8 54
Paint/Redraw window while dragging 16 78
Visual Studio 2005 text editor 10 38
Automatic Email Reminder 4 38
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

777 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