#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    3
    Rep Power
    0

    Question postgres perfomances


    hi

    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 ?
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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 ...)?
    4. Network?
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    3
    Rep Power
    0
    hi
    thanks for answering me that fast

    here is the struture I'm tryng to implement :

    dayly external data ( four huge tables )
    | no problem
    v
    integration in four huge postgres tables (with indexes)
    | no problem
    v
    creation of postgres views (same number of rows, fewer colums, but with cases, operations, substrings, cast) of the tables
    |
    v
    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 ?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    3
    Rep Power
    0
    and the server and the network aren't the cause : 512 mo RAM and the network, 100 mo in local
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    Re: postgres perfomances


    Originally posted by jcl
    hi

    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 )
    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.)


    I tried to configure the postgres ODBC driver but nothing changes !
    what can I do to improve the performances ?
    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).
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo