Android - DB handling - Efficient ways

Hi guys,

I'm back with a solution to effectively parse huge data into Database. I want to start by trying different ways to insert data into DB. In my case it is 17000 records of User object with 5 different columns.

I tried 3 ways to insert

• Using raw DB insert() method
• Using Sqlite binding
• Using Realm

For the first 2 options, I assume you have basic idea in creating DataBaseHelper class. You can check  this reference for basic idea.

Using raw DB insert() method. 

This is straight implementation available in Android documentation. Just have a look at the code.My method goes like this

// Gets the data repository in write mode
SQLiteDatabase db = mDbHelper.getWritableDatabase();

// The below user for loop executes for 17000 times in my case
for ( User user : users.getItems()) {
    // Create a new map of values, where column names are the keys
    ContentValues values = new ContentValues();
    values.put(DirectoryContract.DirectoryEntry._ID, user.getId());
    values.put(DirectoryContract.DirectoryEntry.COLUMN_USER_TITLE, user.getTitle());
    values.put(DirectoryContract.DirectoryEntry.COLUMN_USER_PHOTO, user.getFullPhotoUrl());
    values.put(DirectoryContract.DirectoryEntry.COLUMN_USER_LOCATION, user.getLocation());
    values.put(DirectoryContract.DirectoryEntry.COLUMN_USER_DISPLAY_NAME,  user.getDisplayName());
    values.put(DirectoryContract.DirectoryEntry.COLUMN_USER_COUNTRY, user.getCountry());

    // Insert the new row, returning the primary key value of the new row
    long newRowId = db.insert(
             DirectoryContract.DirectoryEntry.TABLE_NAME,
             null,
             values);
}

This is the most expensive operation taking 35-40 seconds for 17000 records. Can you expect end user to wait for that time to populate users on ListView. So, searching for a solution, I came though binding of data through SqliteStatement.

Data binding using SqliteStatement

As per the documentation this handles insertion in batch rather than inserting one by one. Lets have a look at the code to insert or update user object.

String sql = "insert or replace into "+ DirectoryContract.DirectoryEntry.TABLE_NAME + " ( " +
                DirectoryContract.DirectoryEntry._ID + COMMA_SEP +
                DirectoryContract.DirectoryEntry.COLUMN_USER_TITLE + COMMA_SEP +
                DirectoryContract.DirectoryEntry.COLUMN_USER_PHOTO + COMMA_SEP +
                DirectoryContract.DirectoryEntry.COLUMN_USER_LOCATION + COMMA_SEP +
                DirectoryContract.DirectoryEntry.COLUMN_USER_DISPLAY_NAME +  COMMA_SEP +
                DirectoryContract.DirectoryEntry.COLUMN_USER_COUNTRY +
                " ) "+
                " values (?, ?, ?, ?, ?, ?)";


SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();

SQLiteStatement stmt = db.compileStatement(sql);

for ( User user : users.getItems()) {

    stmt.bindString(1, user.getId());
    stmt.bindString(2, user.getTitle());
    stmt.bindString(3, user.getFullPhotoUrl());
    stmt.bindString(4, user.getLocation());
    stmt.bindString(5, user.getDisplayName());
    stmt.bindString(6, user.getCountry());

    stmt.execute();
    stmt.clearBindings();
}

db.setTransactionSuccessful();
db.endTransaction();

This worked like a charm reducing the insertion time from 35 seconds to 6 seconds. But as I tested the App for a while, I'm still not happy with the performance as it is taking nearly 10-15 seconds with webservice call and insertion. Here comes Realm. 

Using Realm

Realm is replacement for Sqlite and it is very fast. But you have to do some small configuration. Some basic steps involve.

1. Add this line to your gradle. compile 'io.realm:realm-android:0.72.0'
2. Register configuration in Application class.
3. Extend Bean/Model object with RealmObject ( In my case, I extended my User Object)

Realm localRelm = Realm.getDefaultInstance();

try{
    long timeStamp = System.currentTimeMillis();

    JSONObject response = (JSONObject)params[0];

    JSONArray array = response.getJSONArray("records");

    localRelm.beginTransaction();

    localRelm.createOrUpdateAllFromJson(RealmUser.class, array);
    localRelm.commitTransaction();

    Log.d(TAG, "Time taken to populate directory is " + (System.currentTimeMillis() - timeStamp) + " milli sec");

}catch (OutOfMemoryError | IOException | JSONException ex)
{
    ex.printStackTrace();
    localRelm.cancelTransaction();
}finally {
    localRelm.close();
}

This is crazy. It took me just 1 second to insert. I can easily query all my users with a single line like this.

RealmResults users = realm.where(RealmUser.class).findAllAsync();

If there is a search in your listview, Then you can do filtering on the users like this.

 RealmResults realmUsersList = realm.where(RealmUser.class)
                        .beginGroup()
                            .contains("Name", mSearchedText, false)
                            .or()
                            .contains("Title", mSearchedText, false)
                            .or()
                            .contains("Country", mSearchedText, false)
                        .endGroup()
                        .findAllSorted("Name", true);

Please look into this reference  for more details.

Also realm comes with a lot of limitations. Please have a look before implementing.

I strongly recommend Realm for huge data, but you have to obey the limitations.

Some of them are

1. You cannot use the realm objects of one thread in another. This causes issue when we try to load all the query data in Async and set the data to adapter on UI thread. This thows exception. I gues guys at realm are working to sort out this. Sweet news coming soon.
2. You are not freely allowed to write your custom methods in the model which extends RealmObject. I guess there is a way to write using @Ignore annotation, but never tried. 

Happy coding.

Cheers,

Sree

Android - Script to Copy drawables to your project

Hello Guys,

When ever designer gives you a set of drawables, we need to manually copy them to all the respective folders (mdpi, hdpi, xhdpi etc). But,  how about writing a script where you mention source folder and destination( project ) folder which does all the copying and replacing for you. This script is written for Mac.

Download the script from here.

Follow the below instructions after that

• Copy script.sh into any of the folder
• Open terminal – Add some permissions to make script.sh as executable
• Navigate to the folder where script.sh resides
• Type in  - sudo chmod 700 script.sh
• Prompts for Sys password – Enter it
• Your file is marked as executable
• Note:
• Do this step only on first time
• Reference
• In terminal navigate to the folder in which script.sh resides
• Type in - ./script.sh

o   Copies all resources in drawable foder of source to mipmap folder of destination.

o   Do not forget to change the source and destination path in script.sh.

Note:

• Open script.sh and set your source and destination folders properly.
• Currently script does not support spaces in folders.

Hope this saves some time for you.

Cheers,

Sree