Back when Handheld Contact first start developing its mobile software solutions for Act! users, we chose Firebird as a databases structure. Firebird is a fast open-source SQL relational database management system that runs on Linux, Microsoft Windows, macOS, and several Unix platforms, as well.
Recently when we were reviewing areas that was could improve the efficiency of our Classic service, we decided to assess whether this platform was as fast as its SQL Lite counterpart, with which we have developed a number of recent applications with – not the least of which is QuickTasks for Act!.
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world, and is built into all mobile phones and most computers.
Needless to day, SQL Lite seemed quite suitable for our purposes, so we set out 2-months ago to rewrite the Handheld Console to process the data with this new open-source platform, and early results were very encouraging - especially during resend-alls.
Last Friday we completed this large development project and immediately implemented it into high volume testing – in other words, we tested it on our own database. This test involved 36,000 contacts, hundreds or activities, opportunities, and custom fields, but most importantly, over 120,000 notes & histories. We figured if it could handle this level of stress testing, it would certainly standup to most customer’s requirements.
Last Friday we initiated two identical sequential tests with the new console, and then repeated the same with the exact same dataset using the current product console. Two tests on each would expose anomalies between each sync, and show whether the results were consistently reproduceable.
Before we get into the exact results, it's important to explain the three main parts of the syncing process, so readers can understand what we were hoping to improve with these changes. The time-consuming parts of syncing are as follows:
- Retrieving data from Act (this takes the most time, and there is little we can do to speed up this stage)
- Processing data into the database (this is the part that we changed, replacing Firebird with SQLite)
- Sending data, xml files to mware (speeds influenced by available system resources and connectivity)
So with this understood, here are the results from the four tests writing to the database.
Production Console (time writing to database):
- Test #1 = 23-minutes
- Test #2 = 65-minutes
New SQL Lite Console:
- Test #1 – 7 minutes
- Test #2 – 18-minutes
For context, each of these syncs were 2.5 -3 hours, so the first sync was 16-minutes faster (approximately 11% faster in total) and the second was 47-minutes faster (26% faster in total). For regular syncs, where there is less data involved, the speed improvements may be smaller.
Over the last year Handheld Contact has admittedly invested most of our resources in developing and improving our API products, but we never looked past our flagship Classic product. We know this remains the trusted solution for most of our subscribers, and we will continue to make it a priority to improve its features and performance.