Re: Memory usage with Postgres JDBC

Mike,

use cursors, unfortunately you will have to do this manually, as the
driver doesn't support them internally ( yet ).

Dave
On Fri, 2002-07-19 at 10:21, Mike R wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>