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(at)xythos(dot)com > wrote:
>
> *
>
> **From: *Barry Lind [mailto: barry(at)xythos(dot)com]
> *To: *brettonator(at)excite(dot)com
> *Cc: *pgsql-jdbc(at)postgresql(dot)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(at)excite(dot)com > wrote:
> >
> > *From: *Brett [mailto: brettonator(at)excite(dot)com]
> > *To: *pgsql-jdbc(at)postgresql(dot)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!