Re: Prepared Statements - Mailing list pgsql-jdbc

We have found that using PostgreSQL (>7.3) prepared statements is, in
general, more efficient.  We have a lot of queries that are executed
multiple times (sometimes every 30 seconds) and we generally see better
efficiency.  You must, of course, set the useServerPrepare to true
otherwise the query will not be "prepared" by the backend (by default it
is false):

PreparedStatement actionSt = conn.prepareStatement( myquery );
( (PGStatement) actionSt ).setUseServerPrepare( true );

However, we have also seen the reverse effect, depending on the query.
Some queries are not prepared well by the backend and actually execute
*slower* than without setting the useServerPrepare variable.  This is
rare, but when it happens it is can multiply the execution time by 10 or
more.

So from my experience, the efficiency of prepared statements is based on
how well the backend is able to prepare the query.

Nick


On Wed, 2003-07-16 at 22:24, Julien Le Goff wrote:
> Hello everyone,
>
> I have a question regarding the efficiency of Prepared Statements. I'm
> working on a project, and my task now is to decide whether it's worth
> it to use PS. This problem came up when, beginning to implement jdbc
> classes, we noticed that we would need a lot of PS - something like 40
> per class. Each PS would be a class variable, and it sounds weird to
> have 40 class variables... We could have a more elegant system using
> normal statements, but would it be much less efficient?
>
> I started doing some very simple tests: inserting 1000 elements to a
> table, doing 1.000.000 simple queries, then 1.000.000 queries with a
> join... But suprisingly, Prepared Statements didn't give better results
> than normal statements. Before warning the world that prepared
> statements are a big lie, I wanted to have your opinion. Has anyone
> done a reliable test showing the difference between PS and normal
> statements? Does anyone know "how" better PS are supposed to be?
>
> Then, concerning my test, what the hell could be wrong in what I did?
> The query is the following:
>
> String theJoinQueryPrepared =
> "SELECT tr.text FROM truc tr, test te " +
> "WHERE tr.id = te.id AND te.id = ?";
>
> for a Prepared Statement, and
>
> String theJoinQuery = "SELECT tr.text FROM truc tr, test te  " +
>  WHERE tr.id = te.id AND te.id = ";
>
> for a Statement.
>
> Then I just do:
>
>     for(int j = 0; j < 1000; j++)
>     {
>      for(int i = 0; i < 1000; i++)
>      {
>         thePS.setInt(1, i);
>         ResultSet theResultSet = thePS.executeQuery();
>
>      }
>     }
>
> and
>
>     for(int j = 0; j < 1000; j++)
>     {
>         for(int i = 0; i < 1000; i++)
>         {
>             ResultSet theResultSet =
>                 theStatement.executeQuery(
>                         theJoinQueryPrepared + i);
>         }
>     }
>
> I realize that this test is ridiculously simple, but shouldn't the first
> loop be more efficient? On my server both are equally fast...
>
> Ok, I hope this message wasn't too long / too stupid. Thanks in advance,
>
> Julien
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>