[AccessD] pls advise: Performance problem => Staging + UpdateBatch?

Jim Lawrence (AccessD) accessd at shaw.ca
Thu Oct 28 08:24:35 CDT 2004


Hi Sander:

I was working on a contract to roll-out an update inventory database, for a
number of stores, this summer. The system had each computer, with it's own
full database, which could be fully independent (in the event of a
catastrophic failure on the LAN, the POS system had to run). After the
database was rebuilt on the server, it was then transferred to each station
across the LAN. The late-night process could take 20 to 30 minutes per
station. Given that some sites had ten terminals the process would have been
very time-consuming.

We sped the process by zipping the master DB and then broadcasting the
result. It would now only take three minutes per station, as the database
and in your case, a flat-file, compress down very nicely.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of S D
Sent: Thursday, October 28, 2004 2:55 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] pls advise: Performance problem => Staging +
UpdateBatch?


Hi group,

I've got a A2K (that needs to migrate to XP) app that needs to import a
flatfile.
A 267kb file takes about 25-28 HOURS(!!) to proces. Yes, it's a slow
network.
I need to improve this.
This is how it 'works' now:
1 - read the file on the network to determine the number of lines
2 - start a for x=0 to NumberOfLines loop
3 - check the first x characters in the line
4 - create a new record in a table (table depends on step 3, there are 5
different tables to store the records)
5 - execute an update
6 - goto the next record

Here's how I want to do it:
1 - copy the file from the network to the desktop
2 - import the complete file in a staging table
3 - Use an UpdateBatch mechanisme to update the BE.

Question:
How can i implement this UpdateBatch? I mean I have to store the records for
each table
and after the complete file is processed I want to update all tables.

Question:
I need to implement a transaction. If the processing failes somewhere I need
to rollback the changes (if I use an updatebatch this is probably not
necesseray but it's good programming I think)

any tips/advice is greatly appreciated!!

Regards,

Sander


---------------------------------
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list