CODEWITHSUNDEEP
javaandroid
Sivakumar S
Sivakumar S

Reputation: 681

Maintaining order of items in a Grid View

Let say i have a grid view with lot of items in it. The items inside the grid view can be shuffled by drag & drop. Once the shuffle is done, i would like to store the order of the items in Database, So that i can read it later to restore the same order.

My thought process is to have order assigned to each item and while the shuffle is done, i plan to updated the all the related records in database with new order. But it seems to so expensive task to run many update queries. Please suggest me with a optimized technique to accomplish the task. Thanks in advance.

Upvotes: 0

Views: 223

Answers (1)

Xaver Kapeller
Xaver Kapeller

Reputation: 49817

Most of the performance issues in SQLite come from the additional overhead of executing every command in its own transaction. If you just do something like this:

final SQLiteDatabase database = this.openHelper.getWritableDatabase();
for(Entity entities : viewModels) {        

    // Perform your updates here
    ContentValues contentValues = EntityConverter.toContentValues(entity);
    database.update(table, contentValues, "_id = ?", entity.getId());
}

Then each call to update(...) is performed in a separate transaction. If you just want to update 3 or 4 rows it doesn't matter but if you want to update hundreds or even thousands of rows the additional overhead adds up an the whole process could take a very long time.

The solution is simple: Just perform everything in one single transaction.

I doubt that you run into any performance issues that way. Try something like this: 

final SQLiteDatabase database = this.openHelper.getWritableDatabase();
try {
    // Begin the transaction
    database.beginTransaction();

    for(Entity entities : viewModels) {

        // Perform your updates here
        ContentValues contentValues = EntityConverter.toContentValues(entity);
        database.update(table, contentValues, "_id = ?", entity.getId());
    }

    // When everything worked set the transaction successful.
    // If you don't call this method the transaction will be rolled back.
    database.setTransactionSuccessful();
} finally {
    // End the transaction
    database.endTransaction();
}

Aside from the very large performance boost there are also other advantages to this. Imagine you want to update 2000 rows in the database, you perform everything in one transaction but halfway through your app crashes. If you had executed every command in its own transaction you might have a huge problem now. Half of the data is committed, the other half is lost. Your database might be corrupted or in an undesirable state. But if you do everything in one single transaction you are save because a transaction can either be completely successful or not. If there is an error or exception or anything while performing the transaction then no changes will be made to the database. As long as you are using transactions like I explained above you can be sure that every command in a transaction is either executed successfully or no commands are executed at all.

Upvotes: 3

Related Questions