Android SQLite 数据库：插入缓慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3501516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Android SQLite database: slow insertion
提问by benvd
I need to parse a fairly large XML file (varying between about a hundred kilobytes and several hundred kilobytes), which I'm doing using Xml#parse(String, ContentHandler). I'm currently testing this with a 152KB file.
我需要解析一个相当大的 XML 文件（在大约一百千字节和几百千字节之间变化），我正在使用Xml#parse(String, ContentHandler). 我目前正在用一个 152KB 的文件对此进行测试。
During parsing, I also insert the data in an SQLite database using calls similar to the following: getWritableDatabase().insert(TABLE_NAME, "_id", values). All of this together takes about 80 seconds for the 152KB test file (which comes down to inserting roughly 200 rows).
在分析时，我也插使用类似于下面的调用在SQLite数据库中的数据：getWritableDatabase().insert(TABLE_NAME, "_id", values)。对于 152KB 的测试文件（归结为插入大约 200 行），所有这些加在一起大约需要 80 秒。
When I comment out all insert statements (but leave in everything else, such as creating ContentValuesetc.) the same file takes only 23 seconds.
当我注释掉所有插入语句（但保留其他所有内容，例如创建ContentValues等）时，同一个文件只需要 23 秒。
Is it normal for the database operations to have such a big overhead? Can I do anything about that?
数据库操作有这么大的开销正常吗？我能做些什么吗？
回答by WarrenFaith
You should do batch inserts.
您应该进行批量插入。
Pseudocode:
伪代码：
db.beginTransaction();
for (entry : listOfEntries) {
db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();
That increased the speed of inserts in my apps extremely.
这极大地提高了我的应用程序中的插入速度。
Update:
@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database
更新：
@Yuku 提供了一篇非常有趣的博文：Android using inserthelper for fast inserts into sqlite database
回答by qefzec
Since the InsertHelper mentioned by Yuku and Brett is deprecatednow (API level 17), it seems the right alternative recommended by Google is using SQLiteStatement.
由于 Yuku 和 Brett 提到的 InsertHelper现在已弃用（API 级别 17），Google 推荐的正确替代方法似乎是使用SQLiteStatement。
I used the database insert method like this:
我使用了这样的数据库插入方法：
database.insert(table, null, values);
After I also experienced some serious performance issues, the following code speeded my 500 inserts up from 14.5 secto only 270 ms, amazing!
在我也遇到了一些严重的性能问题之后，下面的代码将我的 500 次插入从14.5 秒加速到只有270 毫秒，太棒了！
Here is how I used SQLiteStatement:
这是我如何使用 SQLiteStatement：
private void insertTestData() {
String sql = "insert into producttable (name, description, price, stock_available) values (?, ?, ?, ?);";
dbHandler.getWritableDatabase();
database.beginTransaction();
SQLiteStatement stmt = database.compileStatement(sql);
for (int i = 0; i < NUMBER_OF_ROWS; i++) {
//generate some values
stmt.bindString(1, randomName);
stmt.bindString(2, randomDescription);
stmt.bindDouble(3, randomPrice);
stmt.bindLong(4, randomNumber);
long entryID = stmt.executeInsert();
stmt.clearBindings();
}
database.setTransactionSuccessful();
database.endTransaction();
dbHandler.close();
}
回答by Brett
Compiling the sql insert statement helps speed things up. It can also require more effort to shore everything up and prevent possible injection since it's now all on your shoulders.
编译 sql insert 语句有助于加快速度。它也可能需要更多的努力来支撑一切并防止可能的注射，因为它现在全都在你的肩膀上。
Another approach which can also speed things up is the under-documented android.database.DatabaseUtils.InsertHelper class. My understanding is that it actually wraps compiled insert statements. Going from non-compiled transacted inserts to compiled transacted inserts was about a 3x gain in speed (2ms per insert to .6ms per insert) for my large (200K+ entries) but simple SQLite inserts.
另一种可以加快速度的方法是文档不足的 android.database.DatabaseUtils.InsertHelper 类。我的理解是它实际上包装了已编译的插入语句。对于我的大型（200K+ 条目）但简单的 SQLite 插入，从非编译事务插入到编译事务插入的速度提高了大约 3 倍（每次插入 2 毫秒到每次插入 0.6 毫秒）。
Sample code:
示例代码：
SQLiteDatabse db = getWriteableDatabase();
//use the db you would normally use for db.insert, and the "table_name"
//is the same one you would use in db.insert()
InsertHelper iHelp = new InsertHelper(db, "table_name");
//Get the indices you need to bind data to
//Similar to Cursor.getColumnIndex("col_name");
int first_index = iHelp.getColumnIndex("first");
int last_index = iHelp.getColumnIndex("last");
try
{
db.beginTransaction();
for(int i=0 ; i<num_things ; ++i)
{
//need to tell the helper you are inserting (rather than replacing)
iHelp.prepareForInsert();
//do the equivalent of ContentValues.put("field","value") here
iHelp.bind(first_index, thing_1);
iHelp.bind(last_index, thing_2);
//the db.insert() equilvalent
iHelp.execute();
}
db.setTransactionSuccessful();
}
finally
{
db.endTransaction();
}
db.close();
回答by kaD'argo
If the table has an index on it, consider dropping it prior to inserting the records and then adding it back after you've commited your records.
如果表上有索引，请考虑在插入记录之前删除它，然后在提交记录后将其添加回来。
回答by CircuitBreaker716
If using a ContentProvider:
如果使用 ContentProvider：
@Override
public int bulkInsert(Uri uri, ContentValues[] bulkinsertvalues) {
int QueryType = sUriMatcher.match(uri);
int returnValue=0;
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
switch (QueryType) {
case SOME_URI_IM_LOOKING_FOR: //replace this with your real URI
db.beginTransaction();
for (int i = 0; i < bulkinsertvalues.length; i++) {
//get an individual result from the array of ContentValues
ContentValues values = bulkinsertvalues[i];
//insert this record into the local SQLite database using a private function you create, "insertIndividualRecord" (replace with a better function name)
insertIndividualRecord(uri, values);
}
db.setTransactionSuccessful();
db.endTransaction();
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
return returnValue;
}
Then the private function to perform the insert (still inside your content provider):
然后是执行插入的私有函数（仍在您的内容提供程序中）：
private Uri insertIndividualRecord(Uri uri, ContentValues values){
//see content provider documentation if this is confusing
if (sUriMatcher.match(uri) != THE_CONSTANT_IM_LOOKING_FOR) {
throw new IllegalArgumentException("Unknown URI " + uri);
}
//example validation if you have a field called "name" in your database
if (values.containsKey(YOUR_CONSTANT_FOR_NAME) == false) {
values.put(YOUR_CONSTANT_FOR_NAME, "");
}
//******add all your other validations
//**********
//time to insert records into your local SQLite database
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
long rowId = db.insert(YOUR_TABLE_NAME, null, values);
if (rowId > 0) {
Uri myUri = ContentUris.withAppendedId(MY_INSERT_URI, rowId);
getContext().getContentResolver().notifyChange(myUri, null);
return myUri;
}
throw new SQLException("Failed to insert row into " + uri);
}
