Android 中外部存储上的 SQLite 性能不佳
我使用外部存储在数据库中存储等待发送到服务器的事件。
插入记录时我发现性能非常糟糕。 我知道外部存储器可能很慢，但我想查看一些数字，所以我编写了一个小应用程序来测试它。
这是代码：
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();
数据库与我的真实应用程序使用的数据库完全相同，我尝试删除索引，但没有任何区别。
结果如下：
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 卡变慢？
我可以补充说，在我的真实应用程序中，我已禁用 锁定 并没有什么区别。
I'm using the external storage for storing events in a database while they are waiting to be sent to the server.
I'm seeing really bad performance when inserting records.
I know the external memory can be slow but I wanted to see some number so I wrote a small app which 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 as the one my real app is using, I tried removing the index but it made no difference.
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 is, of course, to be expected, the test was mostly done out of curiosity.
What have me concerned however is the bad performance on my phone when using external memory, have I missed some crucial aspect of SQLiteDatabase or is it simply so that the SD card will be slow?
I can add that in my real app I've disabled locking and it makes little difference.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
CommonsWare 的评论是正确的。对数据库性能产生重大影响的是使用事务。将插入循环包装在事务中。我不确定它是否可以与 InsertHelper 一起使用，但您可以尝试用以下内容替换 for 循环：
CommonsWare is correct in his comment. Something that makes a big difference for db performance is using transactions. Wrap your insert loop in a transaction. I'm not 100% sure if it would work with the InsertHelper but you can try replacing your for loop with this:
我有一些数据库性能问题，因此我使用您的代码来测量系统上每秒的插入次数。但我还在 {begin,end}Transaction() 中添加了包装。
在模拟器中。我得到：
在三星 Galaxy Note 上：
I have some db performance issues so I used your code to measure the inserts per second on my system. But I also added wrapping in {begin,end}Transaction().
In the emulator. I got:
And on a Samsung Galaxy Note: