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
