SQLite Android Data Insertion

Question:

What is the best way to insert more than 10000 rows of data into a table consisting of 13 columns using Android SQLite?

I thought of using the ContentValue then next:

Db.insert(DATABASE_TABLE, null, ContentValue);

Answer:

I don't know much about Android itself, but considering database operations, the ideal would be to create the insert SQL with parameters like

INSERT INTO Tabela (A, B, C) VALUES (:A, :B, :C);

Give a Prepare , Start a transaction and enter all the data changing only the parameters and end the transaction.

as was done in the example cited by Wakin in this article , with a few tweaks:

According to the same article, through SQLStatement the speed is up to 8x higher, in the simple tests he performed

private void bulkInsertRecords(String[] records) {
          String sql = "INSERT INTO "+ SAMPLE_TABLE_NAME +" VALUES (?,?,?);";
          SQLiteStatement statement = sampleDB.compileStatement(sql); //Este é o prepare
          sampleDB.beginTransaction();
          for (int i = 0; i<records.length; i++) {
                    statement.clearBindings();
                    statement.bindString(1, records[0]);
                    statement.bindString(2, records[1]);
                    statement.bindString(3, records[2]);
                    statement.execute();
           }
           sampleDB.setTransactionSuccessful(); 
           sampleDB.endTransaction();
}

Conclusion

According to this other article the reason for the performance gain is:

  1. Block transactional control : With transactional control over the entire INSERT block, changes are performed in memory and then persisted, rather than performing an adjustment on the basis of each insert and checking constraints and updating indexes, etc.
  2. compileStatement : It's prepare, it avoids a processing overhead as if each insert command had to be verified, leaving the command with its "route" already traced, having only to inform the values
Post Views: 1
Scroll to Top