Solved

VB6 DAO Problem - itterative process causing delays

Posted on 2003-11-22
12
427 Views
Last Modified: 2013-12-25
HELP!!!

I'm going round the twist with this...

I have a VB6 application which is using DAO to access a database
(Please - no lectures on ADO, I know what I'm doing with DAO!!)

Ok, problem is this....

importing a large csv file (189000 lines, 7 fileds per line)

Import this into various tables fine - takes a a couple of minutes...
the problem is when I then process these tables.

The progam is basically split into 2
-----------------------------------------------------------------------------------------------
Process A:
table name held in RECORDSETtables

      For each table ( 0 - 6)

            for each day (1 - 365)


                  call RoutineB(table,day)

            next day

      Next Table

RECORDSETtables.close

END

-----------------------------------------------------------------------------------------------

Routine B (sTABLE as string, sDATE as string)

set RecordsetTABLE = db.openrecordset(open table Day&date,
vbopendynaset)
      if not error
            table exists
            exit routine
            ' don't need to process as data already exists
      elseif
            'create table to store data in later
      end if

set RECORDSETdata = db.openrecordset(select data from sTABLE with
Specified sDate)

for each record in RECORDSETdata

      long and itterative calcualtions.
      store data in ARRAY(48,30)

next RECORDSETdata
close RECORDSETdata
with RecordsetTABLE
      for each line in ARRAY(48,30)
            .add new
            .fields(1-48) = ARRAY(line,1-48)
            .update
      next line
end with

recordsetTABLE.close

EXIT ROUTINE B back to calling routine A
-----------------------------------------------------------------------------------------------

Ok - the problem is, that as it processes each day, the time taken
increases from less than 1 second perday to over 7 seconds for the
same amount of processing - ie each day contains the same (more or
less) amount of  data

PLUS, when I quit the program, it takes an absolute age to return back
to the design environment.

So - the question is this: why is this happening and what can I do to
prevent it? When I am processing 8 tables with 365 entries in each
(2920 days in total) it gets stupidly long (a couple of hours) to
run.

I'm not using  ODBC so I can't use the .StillExecuting to check on the
opening of the recordsets, though I have a feeling thtt it is this
which is causing the "lag" - opening and closing the tables - thhogh I
couldbe wrong.

I wondered if this was a limitation of the Jet database (mdb file) and
thought about using dbase V instead - but can't find how to use
CreateDatabase for dbaseV - I can open one fine - but I can't  create
one. - any help on this please?

Should I erase the array one the data is saved?

Is ADO the only option?

Please help - this is driving me potty!!

0
Comment
Question by:ictis
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 9807451
I do not think the problem is DAO (or ADO since I think you will see the same result).  I think the problem is Access itself.  You may want to look into ADOX for some compact and repair features to help you out.

Leon
0
 
LVL 1

Author Comment

by:ictis
ID: 9808891
Thanks - but to compact and/or repair after each RoutineB will kill it completely!! What about using a different format - dbase 5??
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9809964
IMO you will still have the same problem.  In all honesty I think you may need to switch databases.  

The thing about your application, is that to really test something like that you will actually need to do it, because on a smaller scale everything will work fine no matter the format.  The only way to know for sure if the problem will persist with dbase 5 format is to try it.

Leon
0
 
LVL 1

Author Comment

by:ictis
ID: 9813383
Kinda makes sense :-)

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9813488
So whatch you gona do?

Leon
0
 
LVL 1

Accepted Solution

by:
robdogg10 earned 500 total points
ID: 9816048
I disagree with both of you.  I process large files all the time into an Access db and don't see any slowdown.  Granted I use ADO, but via the OLEDB Jet Provider 4.0

As far as your code goes, the only thing that jumped out at me is that you are inserting via the Recordset object.  Try inserting via Database.Execute "Insert (fld1,fld2...) values (v1,v2...)
This may help because the recordset object is very heavy duty, it already holds data you've already inserted, so it slows you down.  Using the Database.Execute method you will avoid the recordset all together.  I suspect this will save some significant chunks of time and memory.

Other than that...
...if you are using DAO 3.5, try to do the same task in DAO 3.6
Furthermore, if you suspect that the size of the file is a problem (btw, what is the size of the file when it starts getting slow?), create 7 separate databases, treat then as tables and see if your speed problem goes away.


0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9816911
Is RecordsetTABLE referring to a table which is not a parameter of Routine B? If not, just leave it open the whole time rather than opening and closing it within the function. You shoudl get a fairly good speed increase if you do that.

If it is the same Recordset tables that is in Routine A, then your psuedo code should look like this:


Process A:
table name held in RECORDSETtables

   ** Open RECORDSETtable

     For each table ( 0 - 6)

          for each day (1 - 365)


               call RoutineB(table,day)

          next day

   ** Close RECORDSETtable

     Next Table

RECORDSETtables.close

END
0
 
LVL 1

Author Comment

by:ictis
ID: 9817560
Looks like robdogg10 is going to get the points !!

database.execute ("INSERT INTO....")

Seems to be taking a constant 1 second per day now to calculate and store as opposed to 1 second raising to 8!!

0
 
LVL 1

Author Comment

by:ictis
ID: 9817639
Still using DAO this drastically improves matters. Now all I need to do is streamline my code for the calculations to imrpove on the 1 second per day :-)

File size doesn't appear to be a problem

BTW Its 3.51 I'm using...

Time to revisit some of my other code I think... :-)

Thanks again - points well earned
0
 
LVL 1

Expert Comment

by:robdogg10
ID: 9821449
DAO 3.6 resolved some file size issues, IIRC, you might want to look into it.   But speedwise, DAO 3.51 is pedal to the metal.
0
 
LVL 1

Author Comment

by:ictis
ID: 9823100
Thanks - I'll try changing the reference.
0
 
LVL 1

Author Comment

by:ictis
ID: 9823106
Thanks to everyone else for their contributions
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now