?
Solved

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

Posted on 2001-06-07
9
Medium Priority
?
177 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
[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
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
Technology Partners: 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!

 
LVL 7

Accepted Solution

by:
John844 earned 200 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

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 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