Performance in mobile Business Apps – #1: Verify Unique Index

(edited 2015-09-26 – see below new tests added)

This is the first part of a series about performance in mobile business apps. Developing apps for the enterprise always is a great challenge: you have to deal with servers from backend, Online / Offline – mode, complex workflows and your app should help the employees to get their work easy done. Performance is one of the keys to success. I’ll talk here about some scenarios you may run into if developing business apps.

Getting data from a server via REST or SOAP you have to rely on data sent to you and sometimes it’s not as expected. Per ex. in one of my Enterprise projects from time to time I had to sync master data where 150k records must be fetched from server and stored locally into SQLite. From API documentation there was one field as unique index declared.

Persisting bulk data into SQLite should be done in a batch transaction. (More about this in another part of this series)

Did so and suddenly SQLite reports an error: INSERT failed because of “Index not unique”. From batch transaction SQLite doesn’t tell you which records are duplicates. To verify this I inserted record by record and found 20 duplicates and could report to IT. Inserting record by record was very slow: 80 minutes instead of some seconds. So what to do ?

To verify data it would be the best to check if the indexes are unique before inserting them into the SQLite. In this case index was a String field and my first idea was to use a QStringList:

void ApplicationUI::checkIndexQStringList()
{
    JsonDataAccess jda;
    QVariantList cacheList;
    QStringList myIndexes;
    int duplicates;
    cacheList = jda.load(dataAssetsPath("fakeName.json")).toList();
    duplicates = 0;
    for (int i = 0; i < cacheList.size(); ++i) {
        QVariantMap fakeMap = cacheList.at(i).toMap();
        QString myIndex = fakeMap.value("Username").toString();
        if (myIndexes.contains(myIndex)) {
            duplicates++;
        } else {
            myIndexes << myIndex;
        }
    }
}

Unfortunately this took 13 minutes – so was no solution.

Then instead of a QStringList I tried to use a QVariantMap:

void ApplicationUI::checkIndexQVariantMap()
{
    JsonDataAccess jda;
    QVariantList cacheList;
    QVariantMap myIndexes;
    int duplicates;
    cacheList = jda.load(dataAssetsPath("fakeName.json")).toList();
    duplicates = 0;
    for (int i = 0; i < cacheList.size(); ++i) {
        QVariantMap fakeMap = cacheList.at(i).toMap();
        QString myIndex = fakeMap.value("Username").toString();
        if (myIndexes.contains(myIndex)) {
            duplicates++;
        } else {
            myIndexes.insert(myIndex, "");
        }
    }
}

Now it took only some seconds 🙂

You can check this from a project at Github: https://github.com/ekke/performance_biz_apps

There I’m using fake data of 50k records and the difference is huge:

• 2 minutes (using a QStringList)
• 1.2 seconds (using a QVariantMap)

If there’s something slow in your app – it’s always worth to search for the reason. I never would have thought that a QStringList is so much slower in this usecase as a QVariantMap.

… updated 2015-09-26 ….

thanks to feedback from twitter @ICODeRUS and @qtproject I added two more tests to make it even faster 🙂

Using a QMap<QString> instead of a QVariantMap reduces time by 10% and using QHash<QString> speeds it up to 400 mS (3 times faster as QVariantMap). see the details from GitHub project, here are the results:

… more blog posts will follow: SQLDataAccess vs Qt/SQL, Threads or what else ?, Objects and Pointer in QML, Caching into JSO or SQLite ?, SQLite tuning, optimize Queries, Startup time of your app, …