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

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 (view raw or flat)
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

pgsql-jdbc by date

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

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