Re: Memory usage with Postgres JDBC

Anyone working on "internal cursor" support? (Or would not it be a better
way to call it "implied cursor" support?)

Pete

-----Original Message-----
From: Dave Cramer [mailto:Dave(at)micro-automation(dot)net]
Sent: Friday, July 19, 2002 4:51 PM
To: Mike R
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] 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(at)postgresql(dot)org
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly