September 4th, 2003, 09:00 AM
I'm installing postgresql as a front database in order to provide data to a MSACCESS database through access queries to postgres views. unfortunately I can't replace the Access database.
Here is my problem :
Despite the postgres tables are huge(from 300.000 rows to 10 million rows) , I thougt postgres would do the views quickly, but when I start an access query, the postgres server works very slowly (more than one hour )
I tried to configure the postgres ODBC driver but nothing changes !
what can I do to improve the performances ?
September 5th, 2003, 04:59 AM
Be shure of what is the bottleneck:
1. Access or PostgreSQL?
2. Queries (explain plan) used to build views?
3. Database (indexing, vacuuming ...) or Server (memory, hdd ...)?
5. If all else fails turn your view into a table and use triggers/stored procs to keep it's data fresh.
Maybe this post could also help MySQL SLOW Searches for the Access part.
Last edited by pabloj; September 5th, 2003 at 05:12 AM.
September 5th, 2003, 06:01 AM
thanks for answering me that fast
here is the struture I'm tryng to implement :
dayly external data ( four huge tables )
| no problem
integration in four huge postgres tables (with indexes)
| no problem
creation of postgres views (same number of rows, fewer colums, but with cases, operations, substrings, cast) of the tables
run access queries (selects, insert into access tables...) on the postgres views
I wanted to use postrges as the backend and access as the frontend ( no choice )
when I run an access query I can see that access doens't work but postgres is doing its select ( fill the view ), I strace the processus and see it reads very slowly. I think the bottleneck is postgres filling its views
I tried with sybase as a backend and it works ( faster than access only, it's logical ! )
but I'm suprised because I thought postgres would work as well even better...
what do you think ?
September 5th, 2003, 07:46 AM
and the server and the network aren't the cause : 512 mo RAM and the network, 100 mo in local
September 5th, 2003, 08:13 AM
Re: postgres perfomances
One quick way to determine where the bottleneck is: have you tried these views from the 'psql' command line? How long does it take to select data from the view in that case?
Remember, if you are selecting the whole view in Access, then that means PostgreSQL will try to send all 300,000 rows to Access via ODBC. That is not what you should do. What happens when Access tries to select one single record from a view? (SELECT * FROM view_name WHERE id=[number], or something like this)
Also, have you done any indexing of your columns? Were you aware that you can even index a function in a view? (This means that your casts, substring operations, etc... can be indexed for speed.)
Did you install the official ODBC driver for PostgreSQL on the Windows client?
What version of PostgreSQL? What version of PSQL ODBC? And (very important): is PostgreSQL installed on Windows, or on a Linux/Unix machine?
Once we get these questions cleared up, then we can go to the next step: tuning your PostgreSQL server parameters (the default settings are very conservative).