Thread

1. Re: Migrating data from MSAccess

   wsheldah@lexmark.com — 2002-11-21T21:26:09Z

   Sounds like you have some on insert triggers that are taking a while to
   validate the data. You probably want to look for ways to speed up the
   triggers, and reduce the amount of validation that has to be done for every
   record if at all possible.
   
   Wes
   
   
   
   "Mihai Gheorghiu" <admin@planwithtan.com>@postgresql.org on 11/21/2002
   12:45:26 PM
   
   Sent by:    pgsql-general-owner@postgresql.org
   
   
   To:    <pgsql-general@postgresql.org>
   cc:
   Subject:    [GENERAL] Migrating data from MSAccess
   
   
   I'm at the stage to rehearse data migration from MSAccess to PG. The PG db
   actually consolidates 4 MSAccess dbs, with all the possible data
   validations
   and integrity in place. I have some queries over the Access tables which
   then write the data to the ODBC-linked tables in the PG db. Data coming
   from
   Access cannot be fully trusted, so it must be loaded with regular inserts,
   in order for PG to perform all the validations.
   The problem is speed. If I run the query over the Access db only to view
   the
   records, it takes a couple of seconds. When it also writes to the PG db, it
   takes a couple of hours. The following is the output of a vmstat 60 2 on
   the
   PG server (PG7.2.1 on RH7.3) while appending records:
      procs                      memory    swap          io     system
   cpu
   r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy
   id
   1  1  1  19496  10096 176188 617472   0   0     7     3    1     1   4   1
   4
   4  0  0  19496  10036 176196 617532   0   0     0   661  244  6560  78  20
   2
   Please comment. Is the PG server the bottleneck? Is there a faster way to
   perform this task?
   Thank you all.
   
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 4: Don't 'kill -9' the postmaster
   
   
   
   
   
   

2. Re: Migrating data from MSAccess

   Richard Huxton <dev@archonet.com> — 2002-11-22T09:21:08Z

   On Thursday 21 Nov 2002 9:26 pm, wsheldah@lexmark.com wrote:
   > Sounds like you have some on insert triggers that are taking a while to
   > validate the data. You probably want to look for ways to speed up the
   > triggers, and reduce the amount of validation that has to be done for every
   > record if at all possible.
   
   I think Wes is right here, but there are a couple of other things to check:
   
   1. Try updating 1000 rows of a simple dummy table - how long does it take?
   2. Same again but wrapped within BEGIN;...COMMIT; so it's one transaction. How 
   long now?
   
   If both are slow, look to your ODBC. If only the first is slow, try batching 
   updates into transactions containing somewhere between 100-10,000 rows.
   
   If it turns out PG is slow, we'll need to know more about the database and the 
   validation etc. you have on it.
   
   -- 
     Richard Huxton
   
   > "Mihai Gheorghiu" <admin@planwithtan.com>@postgresql.org on 11/21/2002
   > 12:45:26 PM
   >
   > Sent by:    pgsql-general-owner@postgresql.org
   >
   >
   > To:    <pgsql-general@postgresql.org>
   > cc:
   > Subject:    [GENERAL] Migrating data from MSAccess
   >
   > I'm at the stage to rehearse data migration from MSAccess to PG. The PG db
   > actually consolidates 4 MSAccess dbs, with all the possible data
   > validations
   > and integrity in place. I have some queries over the Access tables which
   > then write the data to the ODBC-linked tables in the PG db.