ictis
asked on
VB6 DAO Problem - itterative process causing delays
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!!
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!!
ASKER
Thanks - but to compact and/or repair after each RoutineB will kill it completely!! What about using a different format - dbase 5??
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
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
ASKER
Kinda makes sense :-)
So whatch you gona do?
Leon
Leon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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!!
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!!
ASKER
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
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
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.
ASKER
Thanks - I'll try changing the reference.
ASKER
Thanks to everyone else for their contributions
Leon