• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Prepared Statements not being freed by Informix

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In the following code snip, I'm closing the prepared statement, yet it seems like the database is still caching a copy of the statement.
I ran this several thousand times on a machine, connected through our internal network to a database on a different machine with nothing connected to the database except for my java program. I observed free memory gradually decreasing on the database machine (using the unix 'top' function). When I ended my java process that was running the code below, the database became VERY busy for several minutes. The CPU was at about 0% idle time. The database was cleaning out a bunch of prepared statements (all the same statement content as below).
Do I need to explicitly 'free' the prepared statement?
How do I do this?
Do I need to force some garbage collection every so often to clear the prepared statements from the databases cache?
Here's the code snip:
void myMethod()
{
String sql = "insert into stress_output "+
"(iteration, start_date)"+
" values (?,?)";
PreparedStatement ps = pConn.prepareStatement(sql);

ps.clearParameters();
ps.setInt(1, myInt);
ps.setLong(2, myLong);
ps.execute();
ps.close();
}
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
PreparedStatements are allowed to be compiled and cached on the database if the driver and database support this function.
Have a quick look at the description for java.sql.PreparedStatement in the API, and also Connection.prepareStatement
There is also this article about PreparedStatements from TheServerSide.
DOM
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Do I need to force some garbage collection..."
Impossible. The best you can do is gently suggest that the gc run to clean up the jvm. Whether it does or not, no one knows(until it happens). But this probably won't help you, since the gc only clears the garbage from the jvm, not in the database. The database takes care of itself. Usually, a dbms will check to see if the statement is already cached before it adds it to cache, but this is dbms dependant. It looks like your dbms just keeps adding the same statement to cache, even though it already exists.
I have 2 alternatives that may help you:
1. Change the prepared statement to a simple statement (and change your code to statement.executeUpdate() which is the standard way of executing sql updates). See if it is actually the caching that is the problem. If the problem still persists when you use statements, then we're barking up the wrong tree!
2. Declare your preparedStatement at the class level. This way, it will be cached only once. Re-use it in your method as needed.
example:

Jamie
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic