Prepared Statements not finalized?

[GoogleCodeExporter]

I'm not sure if I'm doing something wrong, but I had an issue using prepared 
statements to do lots of insert operations.
I was using the following code to do the inserts :
void Insert(SQLiteConnection db){
...
String sql = "INSERT INTO `" + tablename
        + "` VALUES (" + this.time + "," + this.nodeID + values + ")";

SQLiteStatement st = db.prepare(sql);
st.step();
st.dispose();
}

Surrounded by a db.exec("BEGIN IMMEDIATE TRANSACTION"); and after a few 
thousand inserts a db.exec("COMMIT TRANSACTION");

After a while (maybe 500,000 inserts), the SQLite.getMemoryUsed() went through 
the roof - multiple gigabytes, and the JVM got killed.
I've tried setting SQLite.softHeapLimit(HEAP_LIMIT) and even 
SQLite.releaseMemory(), but got 0 effect (No matter how much i asked to 
released, never released more then a few KB).

Now the weird part : when I changed my inserts from db.prepare->step->dispose 
into db.exec(sql), memory usage never gets over a few MB!
According to http://old.nabble.com/Memory-Usage-td23154686.html it could be 
because sqlite3_finalize() is not called on every statement. However, I 
couldn't find out how to do that with SQLStatement.

Original issue reported on code.google.com by paul.har...@gmail.com on 31 Jul 2011 at 12:59

[GoogleCodeExporter]

Paul, SQLiteStatement instances are cached by default in SQLiteConnection and 
not disposed unless removed from cache. So the memory is consumed not only by 
every single SQLiteStatement but also by cache entries.

If you'd like SQLiteStatement not be cached, you can set "cached" to false when 
using method 
http://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/SQLiteConnectio
n.html#prepare(java.lang.String, boolean)

Please note that your example of "prepare, step, dispose" - without any bind or 
column calls is equal to calling exec.

Also, please note that putting values as literals into SQL is generally a 
no-no, so I guess you either have very good reasons to use such an SQL, or you 
need to rewrite this part with bind variables (which then would reduce the 
number of prepared statements to the number of tables in your DB, and caching 
would make sense).

Cheers
Igor

Original comment by ser...@gmail.com on 31 Jul 2011 at 7:56

• Changed state: WontFix
• Added labels: Type-Other
• Removed labels: Type-Defect

[GoogleCodeExporter]

Ah thank you, didn't know what I was supposed to do with the SQLiteStatements. 
I'm now using a single statement and rebinding it for every insert. It handles 
very similar to using exec, i.e. negligible memory usage. Haven't noticed 
significant performance increase, but hey, at least I'm using it correctly :-) 

Original comment by paul.har...@gmail.com on 2 Aug 2011 at 2:12