This isn addon to my last article Conference App Secrets #3: JSON vs XML from my series ‘Conference App Secrets’.
Before going on, you should
- read my Blog Conference App Secrets #3: JSON vs XML at first
- follow the Thread in BlackBerry Native Development Foums
- take a look at my corresponding Github Repository (Apache 2 License)
I found out that using JSON instead of XML can be up to 30 times faster for the use-cases from my last article: reading/writing 10’000 addresses or reading/writing 119 speakers. For applications where you get data from REST or WebServices, cache them local and use them in-memory it’s very common to read/write to your file-based cache. My Conference Apps are going this way: caching and queuing, work in-memory with GroupDataModel. Next articles will explain HowTo Search and Filter.
From discussions in forum I was asked to compare the speed if using a SQLite Database on your BB10 Device.
I did this and enhanced my sample app to compare the execution times also using SqlDataAccess.
JSON vs SQL in the UI
In this article I won’t discuss the QML UI, because I was using same patterns as already described for JSON vs XML. Here are the screenshots.
Reading/Writing 10’000 Addresses I added two Bars for SQL:
From the values you can see that reading 10’000 addresses using SQL is even faster then JSON:
- READ JSON: 2632 ms
- READ XML: 79795 ms
- READ SQL: 1496 ms
Same for writing: SQL is the fastest way to do it
- WRITE JSON: 5587 ms
- WRITE XML: 3787 ms
- WRITE SQL: 1788 ms
Cascades does a really great job not only for JSON but also using a local SQLite DB.
For the 119 Speakers I added an ActionItem to push the SpeakerSQLPage.qml comparing execution times of JSON and SQL:
Now the results are different: Reading from JSON or from SQLite is similar. Doing some executions in series sometimes JSON is faster, sometimes SQL is faster.
Writing the 119 Speakers into the DB took around three times longer as writing them into a JSON file. Seems there’s some initial overhead using a SQL DB, so inserting less records can be slower then JSON. If you’re doing the write async, the user won’t notice this.
Rule #1: if caching data, use JSON or SQL – both are much faster then XML
If you take a look at the file sizes needed, the JSON or XML files storing the 10’000 addresses occupy 4 MB from your disk space where the SQLite only needs 1.7 MB
Rule #2: if caching a lot of data: SQL uses disk space more efficient then JSON or XML
But there’s a drawback using SQL:
- more and complexer code
- more errors to deal with
- fragile if you’re not the owner of the API providing the data
Business Logic (C++) to deal with SQL data
Now let’s see what happens at C++ side.
Prepare your app to use SQLite DB
We have to add some includes into applicationui.cpp:
#include <bb/data/SqlDataAccess>
#include <bb/data/DataAccessError>
#include <QtSql/QtSql>
Some constants will help – per ex. the Database Name and Table Names. (You’ll find some more Strings in the sample where you should use constants in a real app. It’s up to you …)
const QString DB_NAME = "testdata.db";
const QString ADR_TABLE_NAME = "addresses";
const QString SPEAKER_TABLE_NAME = "speakers";
We also have to add some code to the Header File (applicationui.hpp) to manage a SQLite DB:
#include <bb/data/SqlDataAccess> private: .... bool mDatabaseAvailable; bool initDatabase(); bb::data::SqlDataAccess* mSQLda; .... };
At Startup we initialize the database and open the connection:
bool ApplicationUI::initDatabase() {
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName(dataPath(DB_NAME));
if (database.open() == false) {
const QSqlError error = database.lastError();
// you should notify the user !
qWarning() << "Cannot open testdata.db " << error.text();
return false;
}
// create the Connection
mSQLda = new SqlDataAccess(dataPath(DB_NAME), this);
return true;
}
Read all data from SQLite DB
Reading all data from SQL is really easy:
QString sqlQuery = "SELECT * FROM " + ADR_TABLE_NAME; QVariant result = mSQLda->execute(sqlQuery);
The result from the Query is a QVariantList containing QVariantMap for each Address.
So this is similar to JsonDataAccess.
Write (INSERT) all data into SQLiteDB
Writing all data to a SQLite DB means you have to INSERT from a QVariantList containing QVariantMaps for all your addresses / speakers / … INTO the DB.
Inserting all data into the DB at first you have to think about replacing current data. Writing to a JSON file the content was automatically overwritten. If you want to replace all data of a Table you have to DROP an existing Table:
mSQLda->execute("DROP TABLE IF EXISTS " + ADR_TABLE_NAME);
As next step you have to CREATE the TABLE before you can INSERT any data. If you know the structure of your data you can use a hard-coded QString to do this. But we’re talking here about data getting from REST services in JSON or XML format. If you own the REST service and control the API you also can do it the hard-coded way. If you don’t know exactly what you’re getting, it’s better to dynamically create the Table.
If you know that all data you receive have the same properties, you can simply get the QVariantMap from first row:
QVariantMap addressMap; addressMap = allAddresses.at(0).toMap(); QString createSQL = createTableCommandForAddresses(addressMap);
Then loop through all keys of your map. If some properties are known, you can add some special behaviour like PRIMARY INDEX or special data types:
QString ApplicationUI::createTableCommandForAddresses(
const QVariantMap& addressMap) {
QStringList allAddressColumns = addressMap.keys();
QString createSQL = "CREATE TABLE " + ADR_TABLE_NAME + " (";
for (int i = 0; i < allAddressColumns.size(); ++i) {
QString colName;
colName = allAddressColumns.at(i);
createSQL += colName;
if (colName == "Number") {
createSQL += " INTEGER PRIMARY KEY";
} else if (colName == "Latitude" || colName == "Longitude") {
createSQL += " DOUBLE";
} else {
createSQL += " TEXT";
}
if (i == (allAddressColumns.size() - 1)) {
createSQL += ");";
} else {
createSQL += ", ";
}
}
return createSQL;
}
If you don’t know data types it’s no problem to always use TEXT, because for a SQLite DB the Type is only a hint. After constructing the SQL command simply execute it to create the Table:
mSQLda->execute(createSQL);
Now we can insert the data. The fastest way is to use a parameterized statement:
QString ApplicationUI::createParameterizedInsertCommand(
const QVariantMap& rowMap, const QString& tableName) {
QStringList allColumns = rowMap.keys();
QString insertSQL;
QString valueSQL;
insertSQL = "INSERT INTO " + tableName + " (";
valueSQL = " VALUES (";
for (int i = 0; i < allColumns.size(); ++i) {
insertSQL += allColumns.at(i);
valueSQL += ":";
valueSQL += allColumns.at(i);
if (i == (allColumns.size() - 1)) {
insertSQL += ") ";
valueSQL += ") ";
} else {
insertSQL += ", ";
valueSQL += ", ";
}
}
insertSQL += valueSQL;
return insertSQL;
}
… and execute this command:
mSQLda->executeBatch(insertSQL, allAddresses);
Thanks to Cascades this insert is lightning fast: 1780 ms to insert 10’000 records from a one-liner 🙂
Searching StackOverflow HowTo optimize a bulk import into SQLite you’ll find many tips and discussions, but using Cascades ignore them: executeBatch() runs automatically inside a Transaction !
ATTENTION: This only works if all QVariantMaps match exactly the properties from your parameterized statement. This will work perfect for the Addresses, but will fail for the Speakers !
The Speakers JSON is optimized and only contains properties with values. This took the smallest amount of space and memory. Getting those properties from a QVariantMap is no problem, because you can use defaults if the key wasn’t contained.
If you want to store all possible properties at first you have to make all QVariantMaps equal. There’s a helper method in the sample app to do this: firstInitSqliteSpeakers().
Please also have in mind that we’re using simple Maps with one level of data, where it’s easy to create a Table for. If using more complex data you have to carefully think about your SQLite Tables. As we’ve seen: SQLite is really fast, but can cause some overhead.
There’s no easy answer what will be the best. As always: it depends … 😉
Stay tuned for my next articles where I’ll go deeper into the data models and caching strategies for my Conference Apps.
Download and Discuss
The Sample APP is available at GitHub Open Source (Apache 2 License): https://github.com/ekke/cascades_json_vs_xml
There’s a Thread in the Forums, where you can ask or discuss.
Have fun with the sample app and copy/paste what you need to go your way to use JSON, XML or SQL to persist your data.
business apps 