快速举例说明为什么你应该做正确的事情而不是“错误的事情”。这是在 ICS 4.0.4 上运行的测试,它的插入性能很糟糕。
首先,一个简单的SQLiteOpenHelper
创建一个表UNIQUE
对列的约束有时会导致冲突。
class SimpleHelper extends SQLiteOpenHelper {
// InsertHelpers are a really good idea - they format a prepared statement
// for you automatically.
InsertHelper mInsert;
public SimpleHelper(Context context) {
super(context, "tanika.db", null, 1);
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
mInsert = new InsertHelper(db, "target");
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE target (\n" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
"val1 TEXT NOT NULL,\n" +
"val2 TEXT NOT NULL,\n" +
"val3 TEXT NOT NULL,\n" +
// Let's make one unique so we can get some juicy conflicts
"val4 TEXT NOT NULL UNIQUE\n" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
捆绑在任何旧Activity
我们添加以下简单的测试方法:
long test(final int n) {
long started = System.currentTimeMillis();
ContentValues values = new ContentValues();
for (int i = 0; i < n; i++) {
values.clear();
// Every 20th insert, generate a conflict in val4
String val4 = String.valueOf(started + i);
if (i % 20 == 0) {
val4 = "conflict";
}
values.put("val1", "Value1");
values.put("val2", "Value2");
values.put("val3", "Value3");
values.put("val4", val4);
mHelper.mInsert.replace(values);
}
return System.currentTimeMillis() - started;
}
正如您所看到的,这将每 20 次引发一次冲突INSERT
或者。呼唤InsertHelper#replace(..)
导致助手使用INSERT OR REPLACE
关于冲突。
现在,让我们在没有事务围绕的情况下运行此测试代码。
class Test1 extends AsyncTask<Integer, Void, Long> {
@Override
protected Long doInBackground(Integer... params) {
return test(params[0]);
}
@Override
protected void onPostExecute(Long result) {
System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
}
}
class Test2 extends AsyncTask<Integer, Void, Long> {
protected Long doInBackground(Integer... params) {
SQLiteDatabase db = mHelper.getWritableDatabase();
db.beginTransaction();
long started = System.currentTimeMillis();
try {
test(params[0]);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
return System.currentTimeMillis() - started;
}
@Override
protected void onPostExecute(Long result) {
System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
}
}
一切都是这样开始的:
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
mHelper = new SimpleHelper(this);
mHelper.getWritableDatabase(); // Forces the helper to initialize.
new Test1().execute(2055);
new Test2().execute(2055);
}
结果如何?没有交易的情况下INSERT
s take 41072ms。他们进行的交易940ms。简而言之,FFS,开始使用InsertHelper
和交易。