Re: avoid prepared statements on complex queries?

Hi Maciek,

I tried playing around with the prepareThreshold parameter, by setting it in the JDBC connection string.  I tried setting it to zero, which should cause it to not use a prepared statement, right?  Query is still slow.  I also tried setting it to 1 and 3, and in all cases it's slow.  Is there any way to verify if postgres is using a planned query?

I found this old post:
http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00077.php
"However even when plans are not cached, you will still run into the issue that an unnamed statement using out-of-line parameter values may generate a less efficient plan than an unnamed statement using inline parameter values, because there is no way to tell the planner at the protocol level "I am really only ever using this query once, please give me a specific plan for these values and don't worry about generating a plan that is correct for other values too"."

Maybe that's what's happening?

I tried a different tactic: setting the protocolVersion=2.  I can see that forces it to not use a prepared statement, but it seems to ignore that I set the fetch size to 1000 for the prepared statement.  I need to be able to set the fetch size, otherwise I run out of memory since I'm getting back 4 million results.

thanks for your help.

Anish

________________________________
From: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
To: Anish Kejariwal <anishkej(at)yahoo(dot)com>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Sent: Tuesday, November 15, 2011 5:31 PM
Subject: Re: [JDBC] avoid prepared statements on complex queries?

> If only there was a way to have parameterized queries without using prepared
> statements....

Take a look at the prepareThreshold connection string parameter.
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com