From: | "jennie browne" <jiebe(at)hotmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Prepared Statement Memory Size |
Date: | 2006-07-18 16:42:35 |
Message-ID: | BAY101-F2820CC730656C794594375AE630@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 runtimeit 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 problemwhy is it taking up so
much memory?
Thanks,
jennie
From | Date | Subject | |
---|---|---|---|
Next Message | Kris Jurka | 2006-07-18 17:06:24 | Re: Prepared Statement Memory Size |
Previous Message | Jennie Browne | 2006-07-18 13:11:09 | PreparedStatment Memory Size |