SQLite Android data insertion

Navigation
5

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

I thought about using ContentValue then: 

Db.insert(DATABASE_TABLE, null, ContentValue);
    
asked by anonymous 24.10.2014 / 16:21

1 answer

6

I do not know much about Android itself, but considering database operations, it would be ideal to create insert SQL with parameters like 

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

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

As was done in the example quoted by Wakin in this article / a>, with some adjustments:

According to the same article, through SQLStatement the speed becomes 8x higher, in the simple tests that 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:

  • Blocked transactional control : With the transactional control over the entire INSERT block, the changes are performed in memory and then persisted, instead of performing a base adjustment on each insert and checking constraints and updating indexes , etc.
  • compileStatement : It's prepare, avoid a processing overhead as if each insert command should be checked, leaving the command with its "path" already drawn,
    • 30.10.2014 / 02:46
    How to execute a javascript function from a json file Get parameters passed by url on another page