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
