How We Boosted SQLite Chat Performance: Fragment Caching, Table Splitting, and Index Refactoring

Background

Opening a chat session is slow.

When a session contains many historical messages, queries, updates, and deletions become noticeably slower.

Refresh speed drops significantly with a large amount of historical messages.

Analysis Phase

The optimization is divided into two stages.

Stage 1 – Sessions with a small amount of history

Using Android’s trace tool we identified the main cost points during session entry:

Disk read/write operations.

Inflating the UI (creating View objects).

Resetting the list adapter on exit, which clears the View cache and forces recreation on re‑entry.

Activity switching overhead.

We moved all database and file write tasks to a background thread and used EXPLAIN QUERY PLAN to optimise slow SQL statements.

For UI‑related costs we introduced view caching and replaced the Activity‑based UI with Fragments, which share a single View hierarchy while keeping the Context consistent. This reduced the need for repeated View creation.

However, Fragment‑based animation runs at the View level rather than the Window level, causing lower animation efficiency on some devices. We mitigated frame drops by pausing the Handler queue and lowering other thread priorities during animation.

Images illustrating the Fragment migration are retained.

Data Evidence

Testing showed a 10‑15% improvement for first‑time session entry and a 50‑70% improvement for subsequent entries, despite occasional animation stutter on low‑end devices.

Stage 2 – Sessions with a large amount of history

SQL performance data revealed that queries such as counting messages, fetching the latest 18 messages, and pagination still suffered high latency despite indexes being used.

We first attempted table splitting (one table per talker). This reduced index page loads by ~70% and improved query time, but introduced severe drawbacks:

Longer development cycles and extensive code changes.

Startup time and memory usage skyrocketed (prepare‑SQL time >2 s and +10 MB native heap after splitting 2 000 tables).

Further profiling showed that SQLite’s sqlite3Parser and memory allocation dominate the prepare cost, as the engine parses the entire schema stored in sqlite_master.

Because the schema‑serialization approach proved impractical, we abandoned table splitting.

Alternative Solution

We introduced an integer mapping for the talker field (replacing the long string with a 2‑byte integer ID) and added an index on this new column.

PageTracer, a custom tool that hooks into SQLite’s page‑load functions, was used to measure page counts before and after the change:

PageTrace "SELECT COUNT(*) FROM message WHERE talkerid = 202"
result: all PageCount:437 , Table embedded:1 , Table leaf:6 , Index embedded:10 , Index leaf:349

This reduced index page loads by ~70% compared with the original schema and approached the performance of full table splitting without its memory penalty.

Additional query rewrites (e.g., limiting to the latest 18 messages before sorting) further cut page loads from 1 382 to 22.

Next Steps

Automate per‑SQL page statistics to identify poorly designed queries that EXPLAIN QUERY PLAN cannot flag.

Implement static analysis warnings for long string fields used in indexes to raise awareness across teams.