SEO

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Tue, 18 Jul 2006, jennie browne wrote:

> We have a prepared statement example below:
>
> 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);
>

setFetchSize will only work with ResultSet.TYPE_FORWARD_ONLY. It has no
effect on scrollable ResultSets.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jennie,

You say it returns a million rows, but you have a limit of 788 ?? How
is that possible.

What version of postgres, and driver are you using ?

Dave
On 18-Jul-06, at 12:42 PM, jennie browne wrote:

> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match