Skip site navigation (1) Skip section navigation (2)

Memory usage with Postgres JDBC

From: "Mike R" <mr_fudd(at)hotmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Memory usage with Postgres JDBC
Date: 2002-07-19 14:21:49
Message-ID: F82yJrNKBqujsclWXSZ00010652@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi,
We are running a Java application which can run against either a Postgres or 
Oracle database using jdbc.
I noticed a dramatic difference in memory usage between the two databases, 
presumably stemming from the different jdbc drivers.
The simple test program below connects to a database and does a select from 
a table containing about 40000 records (select * from mytable).  When using 
Oracle, the memory usage peaked at about 11Mb.  With the Postgres driver it 
peaked at 75Mb.  I suspect that the PG jdbc driver brings back the entire 
ResultSet all at once and keeps it in memory on the client, while the Oracle 
driver probably fetches in blocks.

Is there any way to reduce memory usage with Postgres?
I know there is a setFetchSize method in the Statement interface which 
likely was intended for this very purpose.  Unfortunately, it isn't 
implemented by the Postgres JDBC driver (...so much for standards).

Any help on this matter would be greatly appreciated.
By the way, we cannot have different SQL code for the different databases.  
It must be standard.  So using PostgreSQL specific commands is not an 
option.

Cheers,
Mike.

(Other info: In both cases, the application is running on Windows2000.  The 
Postgres database is on a Linux machine while Oracle is on Windows2000.)


/** Postgres Code **/
import java.sql.*;
import java.io.*;

public class JdbcCheckupPG
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    DriverManager.registerDriver(new org.postgresql.Driver());

    String user;
    String password;
    String database;

    database="jdbc:postgresql://myserver:1234/mydatabase";
    user    ="postgres";
    password="";

    System.out.flush ();

    Connection conn = DriverManager.getConnection (database, user, 
password);
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from mytable");

    while (rset.next ())
      System.out.println (rset.getString (1));

    rset.close();
    stmt.close();
    conn.close();
  }
}

/** Oracle Code **/
import java.sql.*;
import java.io.*;

public class JdbcCheckupORA
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    String user;
    String password;
    String database;

    database="jdbc:oracle:thin:@myserver:1521:mydatabase";
    user    ="test";
    password="test";

    Connection conn = DriverManager.getConnection (database, user, 
password);
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from mytable");

    while (rset.next ())
      System.out.println (rset.getString (1));

    rset.close();
    stmt.close();
    conn.close();
  }
}



_________________________________________________________________
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx


Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2002-07-19 14:51:29
Subject: Re: Memory usage with Postgres JDBC
Previous:From: Guthrie, JohnDate: 2002-07-19 13:25:21
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group