Re: Under what circumstances does PreparedStatement use stored

That certainly may be true, but I prefer the earlier point of view that
someone using PreparedStatements for portability and not performance
shouldn't be surprised when his database interaction isn't as fast as it
could be.

Also, how does this play with batching? It's possible (and even
encouraged) to have JDBC code that looks like this:
ps = c.prepareStatement("update my_table set name=? where id=?");
for (Iterator it = list.iterator(); it.next(); ) {
Thing t = it.next();
ps.setString(1, t.getName());
ps.setInt(2, t.getId());
ps.addBatch();
}
ps.executeBatch();

(Code that ensures the batch remains at a reasonable size left out for
clarity.)

I think you'd want to consider statement-batching when considering
prepared statements.

mike

-----Original Message-----
From: Oliver Jowett [mailto:oliver(at)opencloud(dot)com]
Sent: Tuesday, April 13, 2004 4:40 PM
To: Tom Lane
Cc: James Robinson; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Under what circumstances does PreparedStatement use
stored

Tom Lane wrote:
> James Robinson <jlrobins(at)socialserve(dot)com> writes:
>
>>... I suppose this all assumes that the lookup + maintenance of such a

>>datastructure would ultimately cost less than re-planning all queries
>>all the time.
>
>
> I think that is a safe bet to be true, as long as you get *some*
> mileage out of the plan cache. If the application issues a bunch of
> no-two-alike queries then it's a loss of course. But doesn't the JDBC

> API distinguish prepared statements from unprepared ones? ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once. I don't think the driver need
> second-guess this choice.

The problem is that JDBC's PreparedStatement provides two things:
repeated execution of the same query with different parameters, and
portable parameterization of queries. So it's likely that many one-shot
or infrequently executed queries will still use a PreparedStatement.

This is why a threshold on PreparedStatement reuse before using
PREPARE/EXECUTE seemed like a good idea -- we should be able to avoid
PREPARE-ing the one-shot queries, at a minimum.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)