Skip site navigation (1) Skip section navigation (2)

Re: Very strange performance decrease when reusing a PreparedStatement

From: John Lister <john(dot)lister-ps(at)kickstone(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-05-03 10:35:47
Message-ID: 49FD7383.1010005@kickstone.com (view raw or flat)
Thread:
Lists: pgsql-jdbc

Dave Cramer wrote:
>
>     It appears that the Postgres "server-prepared statement" cannot 
> handle
>     parameters to the statement. This is really unfortunate, because 99%
>     of real-life applications will want to re-use the same statement
>     (template) with different parameters.
>
>     The term "server-prepared statement" itself already indicates that
>     there may be something skewed about the "local" semantics of
>     java.sql.PreparedStatements in the Postgres JDBC driver. There is no
>     notion of "client-prepared statement" in the JDBC API, which 
> conceives
>     PreparedStatement instances as mere handles to server side objects.
>     And indeed, Postgres JDBC users have historically been using
>     java.sql.PreparedStatements for its side-effect of preventing SQL
>     injection rather than for the purpose the JDBC API designers had in
>     mind with this class.
>
>
> I'm not sure where this hypothesis is coming from. Postgresql server 
> prepared statements can certainly handle parameters.
>
> What makes you think it can't ?
>
Possibly the post refers to problems in generating optimal query plans 
with prepared statements, for example with respect to index choice. Some 
cases i can understand:
For example if you have a partial index on say (val=3) and you do 
something like "select ... where val=?" if the server knows the value is 
3 it can use the better index.

However it seems that other optimisations can't be made for example it 
doesn't seem possible to tell the server that parameter 1 is always 
going to be an int and therefore it should be using index A. The current 
implementation may not use index A as it is unaware as to the type of 
the supplied parameter. Perhaps in this case the execution planner 
should pick the parameters for the most optimal plan and return the 
types during the parse and let the driver convert the data to what the 
server requires...

Certainly it is a (minor) problem the prepared statements may not be the 
most optimal with any benefits saved during parse lost due to bad 
execution plans.


JOHN


In response to

Responses

pgsql-jdbc by date

Next:From: John ListerDate: 2009-05-03 10:41:03
Subject: Re: getTiIme/Timestamp with TimeZone inconsistency
Previous:From: Dave CramerDate: 2009-05-03 10:10:41
Subject: Re: Very strange performance decrease when reusing a PreparedStatement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group