Thread: pg73jbdc3 performace

pg73jbdc3 performace

From
"Christer Sundgren"
Date:
It takes 14 times longer time to get data from a large log table with a JDBC
connection compared to libpq. Is there a way to increase the speed with the
pg73jdbc3 driver?

The JDBC select looks like this:

    String sql =
            " SELECT id "              +
            "      , message_id "      +
            "      , client_id "       +
            "      , messagedatetime " +
            "      , value "           +
            "      , identifier "      +
            "      , count "           +
            "      , status "          +
            " FROM messagetransaction_history "        +
            " WHERE messagedatetime >= '2003-11-01' "  +
            "  AND  client_id = 126 "   +
            "  AND  message_id = 1550 " +
            " ORDER BY messagedatetime ";

The total time to fetch 52000 records is 1218 seconds with the pg73jdbc3
driver and 1180 seconds with the pgjdbc2.

The libpq - C Library select looks like this:

      strcpy ( sql, "DECLARE mtrans CURSOR FOR " );
      strcat ( sql, "SELECT id " );
      strcat ( sql, "      , message_id ");
      strcat ( sql, "      , client_id ");
      strcat ( sql, "      , messagedatetime ");
      strcat ( sql, "      , value " );
      strcat ( sql, "      , identifier " );
      strcat ( sql, "      , count " );
      strcat ( sql, "      , status " );
      strcat ( sql, "FROM messagetransaction_history " );
      strcat ( sql, "WHERE messagedatetime >= '2003-11-01' " );
      strcat ( sql, "  AND client_id = 126 " );
      strcat ( sql, "  AND message_id = 1550 " );
      strcat ( sql, "ORDER BY messagedatetime\n" );

The total time to fetch 52000 records is 89 seconds.

// Christer

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus


Re: pg73jbdc3 performace

From
Oliver Jowett
Date:
On Sun, Nov 02, 2003 at 08:50:54AM +0100, Christer Sundgren wrote:
> It takes 14 times longer time to get data from a large log table with a
> JDBC connection compared to libpq. Is there a way to increase the speed
> with the pg73jdbc3 driver?

[...]

> The total time to fetch 52000 records is 1218 seconds with the pg73jdbc3
> driver and 1180 seconds with the pgjdbc2.

Quite a lot of this can be JVM tuning. What JVM are you running, exactly? If
it's a Sun JVM, is it the client or server JVM? What heap settings are you
using? Are they approprate for the amount of data you're retrieving (how
large is each row)? Try running with -verbose:gc and see how much time is
spent in GC.

There was a similar question a while back where the dominant cost was GC ..
the default JVM heap settings aren't too good for large data sets.

-O