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.
- Add this line to your gradle. compile 'io.realm:realm-android:0.72.0'
- Register configuration in Application class.
- 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
- 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.
- 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
