I use external memory to store events in the database while they are waiting to be sent to the server.
I see very poor performance when inserting records. I know that external memory can be slow, but I wanted to see a certain number, so I wrote a small application that tests it.
Here is the code:
public static final int INSERTS = 100; File dbFile = new File(Environment.getExternalStorageDirectory(), "test.sqlite3"); // File dbFile = new File(getFilesDir(), "test.sqlite3"); dbFile.delete(); SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile, null); db.execSQL("CREATE TABLE events (_id integer primary key autoincrement, event_type TEXT NOT NULL, timestamp BIGINT, data TEXT);"); db.execSQL("CREATE INDEX mainIndex ON events (event_type, timestamp ASC);"); InsertHelper helper = new InsertHelper(db, "events"); final int eventTypeCol = helper.getColumnIndex("event_type"); final int timestampCol = helper.getColumnIndex("timestamp"); final int dataCol = helper.getColumnIndex("data"); long start = System.currentTimeMillis(); String eventType = "foo", data = "bar"; long timestamp = 4711; for(int i = 0; i < INSERTS; ++i) { helper.prepareForInsert(); helper.bind(eventTypeCol, eventType); helper.bind(timestampCol, timestamp); helper.bind(dataCol, data); helper.execute(); } long end = System.currentTimeMillis(); Log.i("Test", String.format("InsertHelper, Speed: %d ms, Records per second: %.2f", (int)(end-start), 1000*(double)INSERTS/(double)(end-start))); db.close(); dbFile.delete(); db = SQLiteDatabase.openOrCreateDatabase(dbFile, null); db.execSQL("CREATE TABLE events (_id integer primary key autoincrement, event_type TEXT NOT NULL, timestamp BIGINT, data TEXT);"); db.execSQL("CREATE INDEX mainIndex ON events (event_type, timestamp ASC);"); start = System.currentTimeMillis(); ContentValues cv = new ContentValues(); for(int i = 0; i < INSERTS; ++i) { cv.put("event_type", eventType); cv.put("timestamp", timestamp); cv.put("data", data); db.insert("events", null, cv); } end = System.currentTimeMillis(); Log.i("Test", String.format("Normal, Speed: %d ms, Records per second: %.2f", end-start, 1000*(double)INSERTS/(double)(end-start))); db.close(); dbFile.delete();
The database is exactly the same as my real application, I tried to delete the index, but it did not matter.
Here are the results:
Nexus One, Internal memory
Method | Records | Time (ms) | Records per second
------------- + --------- + ----------- + -------------- ------
Normal | 100 | 2072 | 48.26
InsertHelper | 100 | 1662 | 60.17
Nexus One, External memory:
Method | Records | Time (ms) | Records per second
------------- + --------- + ----------- + -------------- ------
Normal | 100 | 7390 | 13.53
InsertHelper | 100 | 7152 | 13.98
Emulator, Internal memory:
Method | Records | Time (ms) | Records per second
------------- + --------- + ----------- + -------------- ------
Normal | 100 | 1803 | 55.46
InsertHelper | 100 | 3075 | 32.52
Emulator, External memory:
Method | Records | Time (ms) | Records per second
------------- + --------- + ----------- + -------------- ------
Normal | 100 | 5742 | 17.42
InsertHelper | 100 | 7164 | 13.96
As you can see, the emulator cannot be trusted, InsertHelper should be faster if anything.
This, of course, is to be expected; the test was made mainly out of curiosity.
What bothers me, however, is it poor performance on my phone when using external memory, did I miss some important aspect of SQLiteDatabase or is it just that the SD card will be slow?
I can add that in my real application, I turned off locking , and that doesn't really matter.