Prepared Statements not finalized?

[Former user]

Originally reported on Google Code with ID 32 ``` 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. ```

Reported by `paul.harkink` on 2011-07-31 12:59:36

[Igor Sereda]

``` 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/SQLiteConnection.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 ```

Reported by `sereda` on 2011-07-31 19:56:45 - Status changed: `WontFix` - Labels added: Type-Other - Labels removed: Type-Defect

[Former user]

``` 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 :-) ```

Reported by `paul.harkink` on 2011-08-02 14:12:57