Android Sqlite data insertion

Asked

Viewed 1,111 times

5

What is the best way to insert more than 10000 rows of data into a 13-column table using Sqlite Android?

I thought of using the ContentValue then:

Db.insert(DATABASE_TABLE, null, ContentValue);
android sqlite

  • What is the way you plan to use?

  • I thought to use Contentvalue then Db.Insert(DATABASE_TABLE, null, Contentvalue);

  • 1

    @Ludger, from what I read, every insert that you do Sqlite creates a transaction, which makes it slow if you know that no constraint will fail. A lot of people recommend using only one SQLiteStatement (just varying the parameters) to speed. Take a look at this benchmark article: http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/ with these two alternatives. As soon as I have time I can formulate an answer.

1 answer

6


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

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

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

as was done in the example cited by Wakin in this article, with some adjustments:

According to the same article, through Sqlstatement the speed reaches to be 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();
}

Completion

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

  1. Transactional block control: With transactional control over the entire INSERT block, the changes are performed in memory and then persisted, rather than making a base adjustment to each Insert and checkar constraints and updating indexes, etc.
  2. compileStatement: It is the prepare, avoid an overhead processing as if each command of Insert should be checked, leaving the command with its "route" already traced, should only inform the values
  • 2

    Is there any reason, in terms of efficiency, to use the SQLiteStatement?

  • 2

    @Jorgeb. I added a conclusion ;)

  • 1

    @Jorgeb. for practical terms, the link I put in the question has a comparison, the gain is 17 times.

  • 2

    Yes @Wakim but the link may disappear. I try not to go see the links (unless it’s documentation) to keep the site trustworthy.

  • 1

    Using Sqlitestatement the app has improved performance, it takes less time to insert.

Browser other questions tagged android sqlite

You are not signed in. Login or sign up in order to post.