Re: Under what circumstances does PreparedStatement use stored

I was reading this mail off the list, and hit the sentence:

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

What is a "reasonable size"? Is there an optimum size? Or a size
beyond which performance takes a massive hit?

Just wondering :-)

Thanks

Tim

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Michael
> Nonemacher
> Sent: 13 April 2004 23:10
> To: Oliver Jowett; Tom Lane
> Cc: James Robinson; pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] 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)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--------------------------------------------------------

This email is confidential and intended solely for the use of the person(s) ('the intended recipient') to whom it was addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of the Paterson Institute for Cancer Research or the Christie Hospital NHS Trust. It may contain information that is privileged & confidential within the meaning of applicable law. Accordingly any dissemination, distribution, copying, or other use of this message, or any of its contents, by any person other than the intended recipient may constitute a breach of civil or criminal law and is strictly prohibited. If you are NOT the intended recipient please contact the sender and dispose of this e-mail as soon as possible.