CODEWITHSUNDEEP
androidsqlitequery-optimization
Imotep
Imotep

Reputation: 2056

Android sqlite and multiple queries

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?

Upvotes: 3

Views: 4354

Answers (3)

kzotin
kzotin

Reputation: 5365

You should exec all requests through one transaction:

db.beginTransaction();
try {
    ...
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

Upvotes: 10

mu is too short
mu is too short

Reputation: 434935

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.

Upvotes: 4

Serhii Mamontov
Serhii Mamontov

Reputation: 4932

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.

Upvotes: 3

Related Questions