Improving Smart SMS Performance Using Attach-Database

Improving Smart SMS Performance Using Attach-Database

Recently, our engineering team took the initiative to entirely focus on removing all the performance bottlenecks related to Smart SMS. We identified a lot of things to improve, but there was one issue that everyone was sure should be fixed, but also aware of the complexity and humongous efforts it would need.

The issue was that there was a slight yet noticeable flicker when smart cards were displayed in the conversation.

If you notice closely, when the conversation page opens, normal messages are shown for a fraction of a second before they are converted to Smart SMS.

Why Is It Blinking?

Are we processing the messages while loading them on the UI? Is a lot of processing happening on the UI layer? Absolutely not. Even though all the messages are processed on the device itself, it mostly happens in the background, and before a user opens his conversation, Smart SMS data is already preprocessed and stored in the DB.

The problem is that all the Smart SMS data is kept in a separate database from the main application database where all the messages are stored. Even though both databases are present in the device itself, every time we load the messages, we have to load the Smart SMS data from a different database and map it to the actual messages on the UI layer. That’s why we observe this flickering.

Can’t We Move Our Data to a Single DB?

It should be easy, right? Smart card data was kept intentionally in a separate database to minimize the impact of Smart SMS processing on our basic app performance. For Smart SMS, a lot of reading and writing needs to be done in big chunks, which keeps the database locked for long periods of time, and that would degrade the performance of our basic features if we used a single database. Apart from this, in a huge app like Truecaller, it's always best to have separate databases for different modules to maintain separation of concerns and improve code maintainability.

Aren’t We Using Flows to Merge the Data?

We are already using flows and coroutines, and these are great tools for getting updates reactively. However, the issue remains the same: the data merging is still happening in the application code instead of at the database level.

The Final Solution: Attach DB

We could not use a single database, but we needed something similar. That’s when we came across a very promising SQLite feature called ATTACH DATABASE. Using the ATTACH DATABASE statement, we can merge two databases into one connection and query them as a single database.

We can attach a database to an existing connection with this simple command:

ATTACH DATABASE smart-cards-db As smart

Once both our databases are connected to a single connection, we can perform a single query on both of them as if they were a single database:

SELECT * FROM main.messages_tab­le AS msg LEFT JOIN smart.processed_da­ta_table AS pdt ON msg.message_id = pdt.message_id WHERE msg.sender = 'AXISBK'

Here, smart is the alias for the secondary database, while the main alias is used for the primary database.

And that’s it. It solves our problem of querying two different databases and merging the result on the UI. It worked well for us, and without any design changes and with minimal changes to the query, we were able to solve the issue. And the flickering was gone!

Testing and Roadblocks

It was now time for testing, and our test engineers worked tirelessly to verify all the scenarios related to Smart SMS to ensure nothing was broken.

We found some minor issues that we fixed along the way. But soon, we hit one major roadblock:

android.database.sqlite.SQLiteDataba­seLockedExce­ption: database is locked

Although it was intermittent, it was a showstopper. This exception occurred because two connections must not write to a database simultaneously.
So, we can have multiple writable connections if we can synchronize the writes. However, in a big application like this, different flows are initiated at different places with different lifecycles; synchronizing such flows is a nightmare.

So, the best way to ensure this is to have only one writable connection per Database.

But in our case, for the smartcards database, we already had one connection that was writing the processed data, and the second one was the attached connection, which was also writable. To fix this, we decided to attach the database in read-only mode.

To achieve this, we created a separate read-only connection to our main database and attached the smartcards database to it; hence, the attached connection also became read-only. This issue was finally resolved.

A few weeks and several bugs later, we finally released it. The team was proud and satisfied that we actually prioritized and fixed a performance issue causing a glitchy UI experience, which we would never want to provide to our users.

What’s Next

The use of the Attached Database had a big impact on the performance of our smart cards. But there are many places in the app where it can be very helpful. We are also exploring FTS (full-text search) in combination with an attached database to make the smart information easily searchable. But that’s for another blog.

Here is a link to the official documentation.