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
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 |