Android SQLite数据库：插入缓慢

5 回答

• 184

  你应该进行批量插入 .

  伪代码：

  db.beginTransaction();
  for (entry : listOfEntries) {
      db.insert(entry);
  }
  db.setTransactionSuccessful();
  db.endTransaction();
  

  这极大地提高了我的应用程序中的插入速度 .

  更新：
  @Yuku提供了一篇非常有趣的博文：Android using inserthelper for faster insertions into sqlite database

  回复于 2024-04-29T04:06:57+08:00
• 68

  由于Yuku和Brett提到的InsertHelper现在为deprecated（API等级为17），因此Google推荐的正确选择似乎是使用 SQLiteStatement .

  我使用了这样的数据库插入方法：

  database.insert(table, null, values);
  

  在我遇到一些严重的性能问题之后，以下代码将我的500个插入从 14.5 sec 加速到仅仅 270 ms ，太棒了！

  以下是我使用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();
  }
  

  回复于 2024-04-29T04:06:57+08:00
• 3

  编译sql insert语句有助于加快速度 . 它还需要更多的努力来支撑一切并防止可能的注射，因为它现在都在你的肩上 .

  另一种可以加快速度的方法是文档不足的android.database.DatabaseUtils.InsertHelper类 . 我的理解是它实际上包装了编译的insert语句 . 从非编译的事务处理插入到编译的事务处理插件，对于我的大型（200K条目）但简单的SQLite插入，速度增加了3倍（每个插入2ms到每插入0.6ms） .

  示例代码：

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

  回复于 2024-04-29T04:06:57+08:00
• 13

  如果表上有索引，请考虑在插入记录之前删除它，然后在提交记录后将其添加回来 .

  回复于 2024-04-29T04:06:57+08:00
• 1

  如果使用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;
  
  }
  

  然后执行插入的私有函数（仍然在您的内容提供者中）：

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

  回复于 2024-04-29T04:06:57+08:00