Re: Taking advantage of prepared statement performance - Mailing list pgsql-jdbc

From Brett
Subject Re: Taking advantage of prepared statement performance
Date
Msg-id 20021010183502.C2A7129A1A@xmxpita.excite.com
Whole thread Raw
In response to Taking advantage of prepared statement performance  ("Brett" <brettonator@excite.com>)
List pgsql-jdbc
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 relevantif 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!

pgsql-jdbc by date:

Previous
From: Nic Ferrier
Date:
Subject: Re: Getting a ResultSet for a refcursor element.
Next
From: "Brett"
Date:
Subject: Re: Taking advantage of prepared statement performance