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

PreparedStatment Memory Size

From: "Jennie Browne" <jennie(dot)browne(at)flytxt(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: PreparedStatment Memory Size
Date: 2006-07-18 13:11:09
Message-ID: 487854A6FD4B404FBE01CF73C357E66F74BE19@euw0300239.xe2hosting.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
We have a prepared statement example below:

 

Query = SELECT consumer.consumer_id, consumer.address,
consumer.operator_id  

FROM consumer 

INNER JOIN registration_list_consumer AS rlc ON (consumer.consumer_id =
rlc.consumer_id AND rlc.registration_list_id = 15)  

INNER JOIN registration_list as rl on (rl.registration_list_id =
rlc.registration_list_id  AND rl.status_id = 25  ) 

WHERE  consumer.address_type_id IN (1) 

AND  NOT EXISTS (SELECT 'x' FROM target_run_transaction trt 

WHERE consumer.consumer_id = trt.consumer_id AND trt.target_run_id =
468)  AND  NOT EXISTS (SELECT 'x' FROM registration_list_consumer AS
rlc_exclude, registration_list rl WHERE rlc_exclude.registration_list_id
= rl.registration_list_id AND rlc_exclude.consumer_id =
consumer.consumer_id AND rl.status_id = 25 

AND rlc_exclude.registration_list_id in (34))  ORDER BY RANDOM() LIMIT
788

 

With no dynamic parameters populated during runtime...it will return
roughly 1million rows. During execution of the query the
preparedStatment grows in size up to 80MB and beyond.

 

We have the fetchSize set to 1000

And autocommit set to false.

 

And the following params set...

 

stmt =
conn.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.
CONCUR_READ_ONLY);

 

can you suggest a work around to fix this problem...why is it taking up
so much memory?

 

Thanks, 

jennie

 

 

pgsql-jdbc by date

Next:From: jennie browneDate: 2006-07-18 16:42:35
Subject: Prepared Statement Memory Size
Previous:From: Tom LaneDate: 2006-07-14 16:15:01
Subject: Re: JDBC 7.4 to 8.1 regression problem

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