Thread: 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 server 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.setX; 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!

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!