H
Hexman
Hi all,
I've written a vb.net 2005 app that uses an Access 2000 db. There are
2 tables used in this particular procedure. Table 1 has 9,870
"transaction" rows. Table 2 is like a "master" table and has 15,000
rows.
It takes 16 minutes to process the 9,870 rows. (debug mode on an AMD
3100+, with fast drives). That's about 615 transactions per minute.
Its no slouch, but this is only the test tables. My machine never
shows more than 15% cpu utilization during execution.
After conversion, there will be approximately 1,000 of these
transactions per day, but I have several other procedures that will
match and update as this procedure does. So I want to speed it up as
much as I can without increasing complexity of the code or using
non-standard methods.
I'm really only sweating the conversion process. I'll have 75,000
transactions, but will have 1.25 million rows to update with info from
3 other tables. Calculated out that seems to be about 100 hours. I
know there are other ways to do the conversion, but just want to find
an efficient way to accomplish this process.
Everything is done in code, not design mode. here is pseudo code:
---------------------------------------------------------------------------------------------------
new dataAdapter, connection, dt1, dt2
create select and update statements for dt1 & dt2
fill dt1 with transactions
loop thru dt1 rows
create dt2 select statement
fill dt2 with master (only 1 record)
if any rows selected
assign new cell values in dt2
adapter.update(dt2)
dt2 accept changes
dt2.clear() 'Clear out dt2
assign 'updated status code' to dt1 row
end if
end loop
adapter.update(dt1)
dt1.acceptChanges
close connection
dispose connection
----------------------------------------------------------------------------------------------------
The client will probably migrate to SQL, but right now the requirement
is for Access.
I need info on my coding methodology, like "you should create a new
dt2 within the loop rather than use dt2.clear()". Maybe there is a
faster way to search & update the equivalent of dt2 rather than using
a data table?
Something to think about, Thanks,
Hexman
I've written a vb.net 2005 app that uses an Access 2000 db. There are
2 tables used in this particular procedure. Table 1 has 9,870
"transaction" rows. Table 2 is like a "master" table and has 15,000
rows.
It takes 16 minutes to process the 9,870 rows. (debug mode on an AMD
3100+, with fast drives). That's about 615 transactions per minute.
Its no slouch, but this is only the test tables. My machine never
shows more than 15% cpu utilization during execution.
After conversion, there will be approximately 1,000 of these
transactions per day, but I have several other procedures that will
match and update as this procedure does. So I want to speed it up as
much as I can without increasing complexity of the code or using
non-standard methods.
I'm really only sweating the conversion process. I'll have 75,000
transactions, but will have 1.25 million rows to update with info from
3 other tables. Calculated out that seems to be about 100 hours. I
know there are other ways to do the conversion, but just want to find
an efficient way to accomplish this process.
Everything is done in code, not design mode. here is pseudo code:
---------------------------------------------------------------------------------------------------
new dataAdapter, connection, dt1, dt2
create select and update statements for dt1 & dt2
fill dt1 with transactions
loop thru dt1 rows
create dt2 select statement
fill dt2 with master (only 1 record)
if any rows selected
assign new cell values in dt2
adapter.update(dt2)
dt2 accept changes
dt2.clear() 'Clear out dt2
assign 'updated status code' to dt1 row
end if
end loop
adapter.update(dt1)
dt1.acceptChanges
close connection
dispose connection
----------------------------------------------------------------------------------------------------
The client will probably migrate to SQL, but right now the requirement
is for Access.
I need info on my coding methodology, like "you should create a new
dt2 within the loop rather than use dt2.clear()". Maybe there is a
faster way to search & update the equivalent of dt2 rather than using
a data table?
Something to think about, Thanks,
Hexman