CODEWITHSUNDEEP
androidsqliteandroid-asynctaskandroid-sqlite
mhorgan
mhorgan

Reputation: 886

Android SQLite Bulk Insert Using Transactions

I have an insert statement and it takes my app about 25 seconds to insert ~1000 rows. I'm trying to use Transactions to speed up my processing time but I'm having trouble implementing it.

Here is my method for inserting in my DatabaseHandler:

public boolean insertQuestions(String gid, String qid, String qname) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_GID, gid);
        contentValues.put(KEY_QID, qid);
        contentValues.put(KEY_QNAME, qname);

        long result = db.insert(TABLE_QUESTIONS, null, contentValues);
        db.setTransactionSuccessful();
        if (result == -1) {
            db.close();
            return false;
        } else {
            db.close();
            return true;
        }
    } finally {
        db.endTransaction(); //Error is here
    }
}

I'm getting the following error when I try to run the above method:

08-28 15:50:40.961  20539-20539/? E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.murrion.navigationdrawer, PID: 20539
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /storage/emulated/0/testapp3.db
        at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
        at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:522)
        at com.murrion.navigationdrawer.utils.DatabaseHandler.insertQuestions(DatabaseHandler.java:197)

The statement is being run in an Asynctask method in my Activity.

Upvotes: 1

Views: 5250

Answers (3)

Karakuri
Karakuri

Reputation: 38605

Two problems

  1. You are only doing one insert per transaction. You need to do ALL the inserts in the same transaction.
  2. You are closing the database before ending the transaction.

You can make a Question class that encapsulates the data of a single question. In that case your method might look like this:

public boolean insertQuestions(List<Question> questions) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues contentValues = new ContentValues();
        for (Question question : Questions) {
            contentValues.put(KEY_GID, question.gid);
            contentValues.put(KEY_QID, question.qid);
            contentValues.put(KEY_QNAME, question.qname);
            db.insert(TABLE_QUESTIONS, null, contentValues);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

Upvotes: 4

Rajesh Jadav
Rajesh Jadav

Reputation: 12861

I think you are inserting question using insertQuestions() one by one. So beginTransaction() and endTransaction() called many more times which is inefficient.

There is some changes needed to speed up transactions:

You should have to first insert all questions to array then use like:

db.beginTransaction();
for (entry : listOfQuestions) {
    db.insertQuestions("","","");
}
db.setTransactionSuccessful();
db.endTransaction();

Some modifications in your method.

public boolean insertQuestions(String gid, String qid, String qname) {
    SQLiteDatabase db = this.getWritableDatabase();

    boolean wasSuccess = true;
    try {
        db.beginTransaction();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_GID, gid);
        contentValues.put(KEY_QID, qid);
        contentValues.put(KEY_QNAME, qname);

        long result = db.insert(TABLE_QUESTIONS, null, contentValues);

        if (result == -1) {
            wasSuccess = false;
        } else {
            db.setTransactionSuccessful();
        }
    } finally {
        db.endTransaction(); 
        db.close();
    }
    return wasSuccess;
}

I hope it helps!

Upvotes: 1

petey
petey

Reputation: 17150

You have already closed the db before ending the transaction, instead close it after ending.

public boolean insertQuestions(String gid, String qid, String qname) {
    SQLiteDatabase db = this.getWritableDatabase();

    boolean wasSuccess = true;
    try {
        db.beginTransaction();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_GID, gid);
        contentValues.put(KEY_QID, qid);
        contentValues.put(KEY_QNAME, qname);

        long result = db.insert(TABLE_QUESTIONS, null, contentValues);

        if (result == -1) {
            wasSuccess = false;
        } else {
            db.setTransactionSuccessful();
        }
    } finally {
        db.endTransaction(); 
        db.close();
    }
    return wasSuccess;
}

Upvotes: 5

Related Questions