Inserting Bulk data taking lot of time Sqlite

Question

I am trying to insert 1120 records (records=questions since it is trivia game) in my database but it is taking around 20secs i can't even work with insertHelper because it has been deprecated in android. i searched a lot and used beginTransaction(),setTransactionSuccessful() & db.endTransaction(); but nothing helped. maybe i haven't used them correct so please correct me if it's wrong

HelperClass

private void addingeachquestions(Questions question) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        values.put(QUESTION, question.getQUESTION());
        values.put(OPTION1, question.getOPT1());
        values.put(ANSWER, question.getANSWER());
        values.put(ANSWER2, question.getANSWER2());
        db = this.getWritableDatabase();
        db.insert(TABLE_NAME, null, values);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

public void addquestions() {
    //famous people
    Questions q1 = new Questions("Who was the first African American to have served as president of United States of America ?", "BAROBAACKMAQCAEMBD", "BARACK", "OBAMA");
    this.addingeachquestions(q1);
    Questions q2 = new Questions("Who co-founded Apple company with Steve Wozniak, Ronald Wayne ?", "TSOVWIBYUBZRGOEJSE", "STEVE", "JOBS");
    this.addingeachquestions(q2);

MainActivityClass

demoHelperClass = new DemoHelperClass(this);
    SQLiteDatabase sqLiteDatabase = demoHelperClass.getWritableDatabase();
    demoHelperClass.addquestions();

`addquestions()` or `addingeachquestions(Questions question)` ? — John Joe, May 23 '17 at 03:04
Put all your inserts inside one transaction. See the accepted answer here: https://stackoverflow.com/questions/32088056/how-to-bulk-insert-in-sqlite-in-android — aljo f, May 23 '17 at 03:05
Is this something you absolutely have to do in the app? Can't you just package the pre-populated database with your app? — Mike M., May 23 '17 at 03:06
@aljo i have previous gone throw that but i didn't understand how they have used loop after content values. can u please tell in my case how it will work — sarvesh chavan, May 23 '17 at 04:29
I mean, make the database yourself, and package _that_ in your app, instead of the text file - or whatever - you're using to create the database after installation. Follow me? — Mike M., May 23 '17 at 04:37

Nitin Patel · Answer 1 · 2018-10-12T05:39:16.400

I have used below method for around 1,00,000 rows insert and definitely faster than other. You can try it.

Instead of one bye one data insert, directly beginTransaction and insert all data and complete transaction.

Add below code(function) in DatabaseHelper(/DbHelper) class and call that function with arraylist of custom class(DataModel class).

Make some adding/changes as per your requirement:-

public void insertBigDataQuickly(ArrayList<DataModel> arrayList) {
    SQLiteDatabase db = this.getWritableDatabase();   //db is instance of DatabaseHelper(/DBHelper) class
    db.beginTransaction();
    try {
        String sql = "Insert or Replace into table_name (column1, column2, column3) values(?,?,?)";
        SQLiteStatement statement = db.compileStatement(sql);
        for (int i = 0; i < arrayList.size(); i++) {  //Loop to insert all data one-by-one with Arraylist data
            DataModel singleData = arrayList.get(i);
            statement.bindString(1, singleData.getValue1());    //1 - Index value of column
            statement.bindLong(2, singleData.getValue2());      //2 - Index value of column
            statement.bindDouble(3, singleData.getValue3());    //3 - Index value of column
            statement.executeInsert();
        }

        db.setTransactionSuccessful(); // This commits the transaction
    }catch (Exception e) {
        e.printStackTrace();
        Log.d("Database error: ",e.getMessage());
    }
    finally {
        db.endTransaction();
    }
    db.close();
}

score 0 · Accepted Answer · answered May 23 '17 at 04:54

You can collect your data, put it in a list, then iterate through the list inside a transaction like so:

private void addAllQuestions(Arraylist<Questions> allQuestions) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();

        for (Questions question: allQuestions) {
            values.put(QUESTION, question.getQUESTION());
            values.put(OPTION1, question.getOPT1());
            values.put(ANSWER, question.getANSWER());
            values.put(ANSWER2, question.getANSWER2());
            db = this.getWritableDatabase();
            db.insert(TABLE_NAME, null, values);        
        }

        db.setTransactionSuccessful();

    } finally {
        db.endTransaction();
    }
}

public void addquestions() {
    //famous people
    ArrayList<Questions> allQuestions = new ArrayList<Questions>();

    allQuestions.append(new Questions("Who was the first African American to have served as president of United States of America ?", "BAROBAACKMAQCAEMBD", "BARACK", "OBAMA"));

    allQuestions.append(new Questions("Who co-founded Apple company with Steve Wozniak, Ronald Wayne ?", "TSOVWIBYUBZRGOEJSE", "STEVE", "JOBS"));

    this.addAllQuestions(allQuestions);
}

Based on this: https://stackoverflow.com/a/32088155/4268599

Inserting Bulk data taking lot of time Sqlite

2 Answers2

Linked