Re: Taking advantage of prepared statement performance

Brett,

This isn't yet documented in the jdbc docs, but it is in the mail
archives.  But the short answer is:

((PGStatement)<yourstatementobject>).setUseServerPrepare(true);

thanks,
--Barry



Brett wrote:
>
> Sure. I have created a pretty nice jdbc benchmarking app that allows
> you to provide work objects and the number of threads to instantiate,
> the infrastructure invokes the threads and takes care of timing, collecting
> statistics based on type of query and printing them out.
>
> I used it to basically poo poo the idea of using mysql.. Was lighting
> fast with selects.. 10x's faster than postgres. Then I started doing
> deletes and performance fell 10 fold and it was actually slower than
> mysql.. this was using mysql in a transactional way.
>
> Anyway, getting back on topic here, I'll be glad to try this and post
> the results. Currently there's only one 1 1 gigabyte ram dual 600Mhz
> machine where both jdbc testing app and db reside on. Soon we'll get a
> second machine to put the jdbc app on. It would probably be more
> relevant*if the testing happened on two machines as opposed to one.
>
> Only one problem, actually using the server side prepared statements. Could
> you point me to where I can find info on using them or should I just
> peruse the past discussions on this subject? There might be updates
> or gotchas which haven't been discussed publicly yet?
>
> Thanks.
>
> --- On Thu 10/10, Barry Lind < barry@xythos.com > wrote:
>
> *
>
>     **From: *Barry Lind [mailto: barry@xythos.com]
>     *To: *brettonator@excite.com
>     *Cc: *pgsql-jdbc@postgresql.org
>     *Date: *Thu, 10 Oct 2002 10:48:02 -0700
>     *Subject: *Re: [JDBC] Taking advantage of prepared statement performance
>
>     Brett,
>
>     It might help performance. I would suggest testing it out and comparing
>     the results with and without server side prepared statements (I am
>     assuming you are attempting to use serv er side prepared statements, if
>     not then you shouldn't see any difference in performance). Then report
>     back to the list your findings, as I am sure there are many here that
>     would be insterested in knowing the results.
>
>     --Barry
>
>     Brett wrote:
>      >
>      > Correction... version is 7.3b2
>      >
>      >
>      > --- On Thu 10/10, Brett < brettonator@excite.com > wrote:
>      >
>      > *From: *Brett [mailto: brettonator@excite.com]
>      > *To: *pgsql-jdbc@postgresql.org
>      > *Date: *Thu, 10 Oct 2002 13:30:00 -0400 (EDT)
>      > *Subject: *[JDBC] Taking advantage of prepared statement performance
>      >
>      >
>      >
>      >
>      >
>      >
>      > I have a web application that invokes java jdbc code for each
>      > request. Would
>      > it be faster if I stored all my prepared statements for each
>      > connection, so
>      > when I want to do a query I a) pull a connection object from the
>      > pool then
>      > b) call ps.se tX; ps.setY;...; ps.executeQuery(); and not close the
>      > prepared
>      > statement? That way, another thread could grab the connection and not
>      > have to recreate the prepared statement. I would associate sets of
>      > prepared statements with connections. Would this be faster than
>      > creating a prepared statement for each web request?
>      >
>      > I am using 7.1.3b2 (for production *crosses fingers*) in case that
>      > matters.
>      >
>      > Once again thanks for any help and sorry for any formatting
>     issues with
>      > this message.
>      >
>      >
>      >
>      >
>     ------------------------------------------------------------------------
>      > *Join Excite! - http://www.excite.com*
>      > The most personalized portal on the Web!*
>
> ------------------------------------------------------------------------
> *Join Excite! - http://www.excite.com*
> The most personalized portal on the Web!