This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Prepared Statements not being freed by Informix Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Prepared Statements not being freed by Informix" Watch "Prepared Statements not being freed by Informix" New topic

Prepared Statements not being freed by Informix

curtis harrison

Joined: Sep 12, 2001
Posts: 8
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.setInt(1, myInt);
ps.setLong(2, myLong);
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

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.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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

I agree. Here's the link:
subject: Prepared Statements not being freed by Informix
jQuery in Action, 3rd edition