我正在使用外部存储器在等待发送到服务器时将事件存储在数据库中.
我在插入记录时看到了非常糟糕的表现.
我知道外部存储器可能很慢,但我想看到一些数字,所以我写了一个测试它的小应用程序.
这是代码：
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,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,end-start,1000*(double)INSERTS/(double)(end-start)));
db.close();
dbFile.delete();
数据库与我的真实应用程序使用的数据库完全一样,我尝试删除索引,但它没有任何区别.
结果如下：
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
正如您所看到的,模拟器不可信任,InsertHelper应该更快.
当然,这是预期的,测试主要是出于好奇.
然而,让我担心的是当我使用外部存储器时手机性能不佳,我是否错过了SQLiteDatabase的一些关键方面,还是只是为了让SD卡变慢？
我可以在我的真实应用程序中添加我已禁用locking并且它没什么区别.
解决方法
CommonsWare的评论是正确的.对数据库性能产生重大影响的是使用事务.在事务中包装插入循环.我不是100％确定它是否适用于InsertHelper,但您可以尝试用以下方法替换for循环：
db.beginTransaction();
try {
for(int i = 0; i < INSERTS; ++i) {
helper.prepareForInsert();
helper.bind(eventTypeCol,eventType);
helper.bind(timestampCol,timestamp);
helper.bind(dataCol,data);
helper.execute();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}