Very strange performance decrease when reusing a PreparedStatement

From: Frédérik Bilhaut <frederik(dot)bilhaut(at)noopsis(dot)fr>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-04-28 10:20:10
Message-ID: 5C2E1645-73B6-4509-96CD-FA77869516AB@noopsis.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi everybody,

I am experiencing a *very* strange problem when querying Postgres
through JDBC using PreparedStatements.

To say it short, for the same SELECT query :

- when reusing a single PreparedStatement the average response time
per query is 60 milliseconds

- when creating (and closing) a new PreparedStatement each time, the
average response time drops to only 2 milliseconds !

This seems unbelievable, but I cross-checked with several people, and
this is truly what happens. Maybe some cleaning or synchronizing is
done before executing again the same statement ? Has anybody
experienced this ?

This appears on two different 8.x versions of postgresql, on Mac and
Linux. The client runs under Mac/Java 5.

Here are a little bit more details :

At first I used to create a single prepared statement once in my
constructor :

this.stmt = getConnection().prepareStatement("SELECT resource.uri FROM
literal,resource WHERE resource.id=literal.id_subject AND
literal.id_graph=? AND literal.id_predicate=? AND literal.lexical_form
LIKE ?");

And then, for each new query :

this.stmt.setLong(1, graphID);
this.stmt.setLong(2, relationID);
this.stmt.setString(3, litteralValue);
ResultSet rs = this.stmt.executeQuery();
...
rs.close();

This gave me very poor performance (about 60ms/query). Just for
testing I added the following lines, and I got my 2 milliseconds per
query (you can double check that the statement creation is exactly the
same) :

if(this.stmt != null)
this.stmt.close();
this.stmt = getConnection().prepareStatement("SELECT resource.uri FROM
literal,resource WHERE resource.id=literal.id_subject AND
literal.id_graph=? AND literal.id_predicate=? AND literal.lexical_form
LIKE ?");

Many thanks for your help.

Best regards,
--
Frédérik Bilhaut

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Hitz 2009-04-28 11:40:48 Re: Thread hangs in VisibleBufferedInputStream.readMore
Previous Message Jeppe Sommer 2009-04-28 08:25:15 Re: Here's a fix to AbstractJdbc3Statement.getGeneratedKeys