pg73jbdc3 performace

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