To populate my database, I have some loops like that:
for (int i=0;i<something;++i){
myDatabase.insert(...);
}
But when I run it, it takes a too long time. So I'd like to know how to improve the runtime? I'd try to make a only one call to insert like that:
String queries="";
for (int i=0;i<something;++i){
queries += "my query;";
}
myDatabse.execSQL(queries);
But the execSQL function insert only the first query, not others. So how can I do?
You should exec all requests through one transaction:
db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
Have you tried inserting multiple rows in one statement? Something like this:
INSERT INTO table (c1, c2, c3) VALUES
(1, 2, 3),
(4, 5, 6),
....
You could also try wrapping your inserts in a single transaction. Putting all your work in one transaction should avoid the per-insert locking overhead.
You can try to enclose those insert lines with "BEGIN" and "COMMIT" transaction. SQLite wraps each DML and DDL calls in transaction block, creates transaction journal and after insertion for example, it will remove it and this will repeat again (in case of your first example of code).
When you manually exec TCL commands, than SQLite database leaves auto-transaction mode and will do that you say to it to do.
