Tweetie Pooh wrote:
>
> I am looking for some hints as to how to speed up a processes we use in
> "archiving" data.
>
> We currently use one server with 2 schema's. The smaller collects data from
> 3 automatted sources. The second is a set of partitioned tables of similar
> structure to the first. Each night a perl script moves records from the
> small "live" system and with some enrichement stores them in the "archive".
>
> Now think of an order entry system with order headers, lines, delivery
> instructions etc. The process selects ordernumbers and rowids from header
> where delivery date is set. It then copies the header and child records to
> the "archive", enriching the data on one of the tables, and deletes the
> header (foreign keys delete the children).
>
> This process takes about 1 hr to move 40,000 header records.
>
> We are now moving the system to separate servers. Both boxes are bigger and
> faster and I have reworked the scripts to move the data using dblinks. This
> appears to take too long. The setup is slightly different as each of the 3
> feeds now use separate schemas on the smaller server so there are 3 archive
> actions but each with less data.
>
> Any suggestions on how to best get the data across? Is the use of dblinks
> and perl the way to do it or is there a better way? Also since the small
> collection server came online first we have a couple of weeks of data to
> move. I am attempting to export/import to a holding area on the main server
> and run the script locally.
>
> We use Oracle 8.1.7.4
If you are using PERL, then skipthe DB links and make two separate
connections. The DBLINKS use extra processing that you can avoid making
the connection directly. If you'd like, email me and I may be able to
help.
--
Ed Prochak
Magic Interface, Ltd.
440-498-3700(office)
Computer consulting, database and web services.
Received on Fri Dec 27 2002 - 16:30:49 CST
