Thread: Performance??

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

    Join Date
    Nov 2002
    Posts
    3
    Rep Power
    0

    Performance??


    I'm having some problems with performance in PostgreSQL. More precisely with “selects” from relatively large tables. If I select a field or every field from a table with about 200,000 records PostgreSQL will collect ALL records before outputting them. (On my system this takes more than 5 seconds)

    On ORACLE I use “set optimizer_goal = first_rows” which will make ORACLE output the records immediately, rather than waiting for all rows to be read into memory.

    I was wondering if there is a PostgreSQL equivalent to the “set optimizer_goal = first_rows” in ORACLE.

    If there already is a thread on this subject then just spank me and direct me there.

    Thanks.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I will see if I can find a similar setting. What is your client environment? Are you querying over a network, or into some other programming environment, such as Perl DBI, PHP, etc...?

    Why are you querying ALL records? I can hardly ever think of a situation where all records need to be fetched at one time, especially if you want the first few rows to be displayed at once. PostgreSQL has the LIMIT row, [offset] method, which allows you to grab any distinct set of rows from a SELECT query. This is great for paging results, etc...
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Posts
    3
    Rep Power
    0
    The environment is an ANSI C program on a Solaris server.

    About querying all records, we use that a lot in our program since most of the logic is in the program itself. In reality we don't need all records, but we ask the database for them and cancel the query when we don't need anymore.

    We try to make our program virtually independent of what database system it is running on, Oracle, Informix, Nonstop SQL, DB2 and PostgreSQL(I hope).

    Thanks for looking for a similar setting.
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Well, I have never done C programming with PostgreSQL, so I don't know if I can be much help, but you might want to look here.

    ...most of the logic is in the program itself.

    In reality we don't need all records, but we ask the database for them and cancel the query when we don't need anymore.

    We try to make our program virtually independent of what database system it is running on
    Perhaps you've heard of Views? Stored procedures? These provide great ways to separate the client program from the internal logic of the database. Keeping all your logic in the code, in order to avoid using the capacities of an SQL DBMS seems to me like walking to avoid the issues of learning manual versus stick shift .

    Personally, I think the time you are gaining in portability issues will be completely lost in data logic and integrity issues. Plus, what do you do if another application wants to access the same database?
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Posts
    3
    Rep Power
    0
    Thanks for responding!

    I understand your views but we cannot afford major changes in the code to facilitate PostgreSQL. The strength of our software is that it is independent of the database its running on.

    Keeping all your logic in the code, in order to avoid using the capacities of an SQL DBMS seems to me like walking to avoid the issues of learning manual versus stick shift .
    The fact is that no database system can supply all the features we need so we will keep the logic in the program.


    Plus, what do you do if another application wants to access the same database?
    Well as long as they aren't changing data it isn't a problem. All changes to the database MUST be done by our software.



    Simply put, what we need is a way to flush the result of the query to the program while the query is still running.
    Last edited by Max Renn; November 19th, 2002 at 07:39 AM.
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Did that libpq link above provide any help? You might try searching through http://archives.postgresql.org/

    I am not talking about changing code for PostgreSQL. I am talking about at least minimally programming for some portability.

    The fact is, you made a very non-portable choice when you decided to use Oracle's "set optimizer_goal = first_rows" syntax. Such a method is not an ANSI SQL implementation, and is hardly likely to be the same throughout database systems.

    Every DBMS has a method to return a limited number of rows from a large query. I believe the only differences are minimal. In most DBMS's such as Oracle, SQL Server, etc... the syntax is TOP [integer], while in PostgreSQL and a few others, it is LIMIT [integer].

    So at most, you have a few character's difference, and you can get this functionality accross database systems.

    On the other hand, SQL offers many, many ways to shield your application from syntax differences, so you don't need to change application code at all to access extra functionality. The idea is to push the syntax differences inside the database, so your application doesn't even need to know the differences. All it knows is that it sends a certain query or calls a certain procedure, and receives a set of results.

    Views and stored procedures, for example, are available in any serious DBMS. Also, all serious DBMS systems have CURSOR handling, which is another great way to handle limiting and paging results from a large query. (http://www.postgresql.org/idocs/inde...l-declare.html, http://www.postgresql.org/idocs/inde...l-cursors.html). Combining stored procedures with cursors is a very standard way of working with databases in C.
    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