SQLite is extremely slow in Samsung Galaxy GT-I9000

Question

I am having problem with performance of SQLite database in Android application in Samsung Galaxy GT-I9000 device. The database contains 10 tables with 3 - 8 columns each.

As soon as a database file gains about 400 KB update/instert queries need much more time to execute - 2 seconds! In comparision to other devices (HTC Magic, HTC Desire) update/insert queries take about 40 msec !

The database file is located in application data directory. Moving database file to sd card increases the speed significantly.

Periodic ANALYZE and VACUUM commands do not affect the speed. As well as PRAGMA cache_size command.

Do you have any ideas how to improve the performance?

Any idea is welcome except for keeping the database in sd card or splitting the database into many files containing one table each.

Answer 1

Bulk insert commands should be done within a transaction:

BEGIN TRANSACTION;
// INSERTS
COMMIT;

Answer 2

Use transactions and parameterized queries. Sqlite has to parse every sql statement if you don't use parameterized queries. All this unecessary parsing makes a simple insert statement 2 to 3 times slower. See here: http://stackoverflow.com/questions/904796/926251#926251

Using transactions is also very important because Sqlite can do an insert very fast but it commits are slow so you have to bundle you inserts/updates and deletes into one big transaction.

Answer 3

This helped a lot and and motivated me to search even more. And I found a solution which gives a huge boost:

PRAGMA synchronous=OFF

INSERT/UPDATE Queries run 4 times faster, now.

It has however a small disadvantage.

More details here:
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragmas

Answer 4

Access to the internal SD card is very slow on the Samsung Galaxy S. There are a variety of inofficial lag fixes available, it seems the RFS file system from Samsung is responsible for the delays.

Hopefully Samsung will release an Update that fixes this problem, until then your best bet is to avoid I/O if possible.