Android SQLite 数据库: 缓慢插入

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

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.

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.

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.

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();

If using a 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);




}

Since the InsertHelper mentioned by Yuku and Brett is deprecated now (API level 17), it seems the right alternative recommended by Google is using 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 sec to only 270 ms, amazing!

Here is how I used 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();
}