Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2001-06-07
9
Medium Priority
?
180 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
Industry Leaders: 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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

610 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