append and update queries to PostgreSQL backend very slow

[GPE]

Hi,
I have 15 distributed Access 2002 databases that all have the same schema. I
would like to combine all of them into one centralized (read only) database,
mainly for reporting purposes. The distributed databases have multiple
tables, some of which contain 200.000 records or so.
The centralized database would be too big to hold in Access, so I created a
database schema in PostgreSQL and designed append and update queries in an
Access frontend that links to the PostgreSQL backend. Now, I have all the
data in the PostgreSQL backend and can run Access 2002 reports on it without
problems.

The problem is, I would need to “feed†the centralized database from the
distributed databases every two weeks to keep the info updated, and the
append and update queries are very slow (about 20 minutes each). So I wonder,
is there a faster/better way to do this? Any suggestions appreciated!

 

[Piet Linden]

Hi,
I have 15 distributed Access 2002 databases that all have the same schema.. I
would like to combine all of them into one centralized (read only) database,
mainly for reporting purposes. The distributed databases have multiple
tables, some of which contain 200.000 records or so.
The centralized database would be too big to hold in Access, so I createda
database schema in PostgreSQL and designed append and update queries in an
Access frontend that links to the PostgreSQL backend. Now, I have all the
data in the PostgreSQL backend and can run Access 2002 reports on it without
problems.

The problem is, I would need to “feed” the centralized database from the
distributed databases every two weeks to keep the info updated, and the
append and update queries are very slow (about 20 minutes each). So I wonder,
is there a faster/better way to do this? Any suggestions appreciated!

Check your indexing in Access. That could be part of your problem.

 

[John Spencer]

It MIGHT be faster to clean out the entire PostGreSQL database and the
just APPEND all the data from the Access databases instead of appending
and updating.

It could be even faster to drop the tables in the PostGreSQL database,
build the tables with out any indexes, append all the data, and then add
the indexes back in.

Whether this scenario will work for you (and improve performance)
depends on your data. For instance, if a record is deleted from an
Access database, do you want it deleted from the PostGreSQL database?
You did not mention that possibility. If you want the deleted record
retained, then the above scenario will not work for you.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[GPE]

Thanks a lot for your replies. I can't drop the tables in the PostgreSQL
database, because when I update from one Access database, I still want to
keep the data from the other 14 Access databases.

I don't have to worry about retaining or discarding deleted records (I log
the deleted records in the Access databases, so with that log I can delete
the records also from the PostgreSQL database).

The whole process of updating one Access database into the PostgreSQL
database takes about 3 hours, so doing this for 15 databases every two weeks
is a bit time consuming. Appending only instead of appending and updating
would be an option, but I think it will even be slower. The append queries
that I have now only append the primary key of new records from the Access
databases into the PostgreSQL tables (which takes about 20 minutes for the
bigger tables), and then I update all the fields of all the records (for that
particular Access database) through update queries.
The primary key is a combined key of two fields, a 13 char. text field and a
date field. This is probably why it is so slow.

I am thinking, maybe I should move away from Access and try to do this in
Navicat or some other GUI tool? What do you think? I really like Access and
can do almost anything I want with it, and I have no trouble with running
Access reports on the PostgreSQL tables (with millions of records!). Select
queries only take 2 seconds at most, so as a reporting tool Access is perfect.

 

[John Spencer]

Ok, the process I recommended would be to DROP all the records from all
the tables before you start doing the import from the 14 databases.

Remove all the indexes

Append all the records from all 15 databases

Add any needed indexes back

This will usually be faster.

If you are adding the information from the different Access databases at
different times then the process would need to be modified. If you can
identify which Access database was the source of the records in the
PostGreSQL table(s), delete those records that came from the Access
database you are going to import from.

Drop the indexes on the table(s) in the PostGreSQL database
Append all the records you want to from the Access database
Rebuild the indexes on the PostGreSQL databases.

I have not used PostGreSQL databases, but I do a similar task with
Access database and SQL server. To load well over 2 million records to
multiple tables (one table alone has over 1.7 million records) takes me
on the order of 30 minutes.

As always, your mileage may vary.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[GPE]

Thanks! I created an update routine that drops the records from the
PostgreSQL table (a delete pass-through query), drops the indexes, appends
and then sets the indexes back. This takes about 45 minutes for one Access
database, so quite an improvement from 3 hours. I would like to have it
faster still (because without the indexes on the BE tables you can't run
reports while there is an update ongoing...) but I guess we'll have to live
with it.
Thanks again,
G.

 

[John Spencer]

Glad to hear that it helped. And as far as running reports while you
are making massive updates I would question why you would want to while
the data is changing so much.d

Good luck and I hope you can find a faster method. I don't know much
about PostGreSQL (the name only really) but maybe you could throw more
hardware at the problem - more RAM, faster network connection, possibly
a faster processor.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================