local database optimization
Open eXpress and send a simple “Hello” to the chat. Pay attention to how much time passed between the time you clicked the “send” button and the message appearing in the chat. To the human eye, the display of a sent message in a chat occurs instantly. This is a modern standard – chat actions should happen as quickly as possible. Ideally, there should be no delays.
Sending a message, however, is a complex operation that requires a lot of work. Its most essential part is working with the local database. eXpress is designed to be offline-first and stores a lot of data locally. One user may have thousands of active chats and tens/hundreds of thousands of messages, and working with them must remain fast.
My name is Daria Voronskaya, I work in the Android Core team eXpress and am optimizing the application. I'll tell you about the approaches we used to bring the speed of sending and rendering messages to that very ideal result.
This article will only talk about working with a local database. Database optimization is only part of a big job; we will talk about our solutions for optimizing the presentation layer in other articles.
Our stack: Kotlin, Room, Coroutines, WebSocket, OkHttp.
Disclaimer
When carrying out optimization work, we need to understand not only whether this or that solution will give an increase, but also what kind of result we can potentially get. Therefore, this article will also discuss intuitive solutions that we accompanied with measurements.
All measurements were carried out on an empty project, on an Android 13 device, Xiaomi Poco f3.
Percentiles will be used to compare function execution times:
p50 (median) – execution time, which turned out to be faster than 50 percent of all measurements
p90 – execution time, which turned out to be slower than 90 percent of all test measurements
p95 – execution time, which turned out to be slower than 95 percent of all test measurements
Simple on-site solutions
We started optimizing by implementing simple tips suggested by the documentation and community. The speed of certain operations depends on the project architecture and database structure, therefore Every time you implement new optimization solutions, you need to take measurements.
At first, we tried to apply those solutions that would require minimal code changes and that could be applied in a specific place where performance issues are especially acute.
1. Reading required database columns
By listing specific fields in a query, you can obtain an object containing only the specified data.
@Query("SELECT * from chats where id = :id")
suspend fun getChatById(id: String): Chat
@Query("SELECT updated_at, updated_message_id from chats where id = :id")
suspend fun getChatById(id: String): ChatUpdatedMessageWe conducted an experiment to see how much the time to obtain data decreases when specifying the required columns. We took a table of 5 columns of type TEXT containing 5000 rows and compared the execution time of two functions.
@Query("SELECT * from table_name where id in (:ids)")
suspend fun getByIds(ids: Collection<Int>): Table
@Query("SELECT first_field from table_name where id in (:ids)")
suspend fun getChatById(ids: Collection<Int>): TableFirstFieldBelow is a chart that shows how the execution time of the above functions varies for one element.
If the table contains few columns, then selecting all columns itself is fast. A query that reads only 1 column is faster, but only by a few percent (in our case, 10%).
On the graph of the dependence of the execution time of two functions (on the graph orange – select the entire row, red – select 1 field) on the size of the source list, you can see that as the number of requested elements increases, the time increases linearly. For example, in our case, selecting 1 column by 100 elements is almost 2 times faster than selecting 5 columns. But since the selection of all fields is still performed quickly (in our case 0.4 ms), there is little point in refining the columns, except in cases where every fraction of a second counts.
On massive tables, the time required to select an entire row will be longer, and the difference in the execution time of the above-mentioned functions will be significantly higher. For example, if there are 40 columns in a table and we need only 1 of them, then selecting one field will be 2 times faster than selecting the entire row.
On a similar graph of the dependence of execution time on the size of the source list, we can see what kind of speed increase selecting 1 field can give us.
When receiving 100 rows, selecting 1 field was 8.6 times faster than selecting the entire row
When receiving 50 rows, selecting 1 field was 5.8 times faster than selecting the entire row
Naturally, if heavy information is stored in columns, this also affects the reading speed.
For clarity: we filled a table of 40 columns with 400 kb rows (tables of this size should not be created in production code) and carried out the same experiment (blue line – select *, green line – select field). For this case, selecting one field works 50-100 times faster than selecting the entire row.
To summarize the above, the main thing that affects the duration of a read operation is the size of the cursor (pointer to the current line). If heavy data or several fields are read, the cursor size increases and the selection time becomes longer. But if there are few columns in the table and there is little data, then selecting all fields is already fast, and the potential performance gain when specifying specific fields will be small.
Also, if the table contains fields of types that require conversion via TypeConverterthen it especially makes sense to specify the fields in the request. Custom objects are usually stored in SQLite as a string, such as the JSON format. Type converters work for custom objects with every select and can take a long time to complete.
Reading only the necessary data is not only faster, but also more correct from an abstraction point of view. Such a change is readable, safe and cannot lead to further bugs. It also makes writing tests easier.
You can also move some of the simple logic into the query. For example, using the EXISTS operator, we can determine whether the row we need exists in the table.
// Определение существования записи через SELECT *
@Query(“SELECT * from chats where chat_id = :chatId”)
fun getChatById(chatId: String): Chat?
val isChatExists = getChatById(chatId) != null// Определение существования записи через SELECT EXISTS
@Query(“SELECT EXISTS (SELECT 1 from chats where chat_id = :chatId)”)
fun isChatExists(chatId: String): Boolean
val isChatExists = isChatExists(chatId)The acceleration in this particular case will only be due to the absence of the need to read the contents of the fields and form an object from them. The histogram below compares function execution speeds for a 20-column table. The gain is small, but this approach does not have any potential problems:
Moving more complex logic inside the request, of course, can improve performance to some extent, but it will definitely complicate testing and can lead to bugs and blurring of logic between dao and other domain classes.
2. Merging multiple records into one transaction
Every write to the database is performed in a write transaction. First, a set of changes is collected, and at the end of the transaction, it is written to the database. Using extension functions withTransaction, runInTransactionas well as annotations @Transaction You can execute multiple queries within a single transaction.
appDatabase.withTransaction {
chatsDao.clearDraft()
messagesDao.insert(messageDB)
attachmentsDao.insert(attachmentDB)
}If you run multiple queries in one transaction, changes to multiple queries (and tables) are applied in one operation. There are several benefits to combining multiple write requests into a single transaction:
Data Consistency — the database will not be in a state where data that depends on each other will not correspond to each other. If a read request is made between two write requests in the same transaction, the race condition will not occur.
Speed – since the number of writes to the database is reduced to 1, we achieve an improvement in performance. This is more of a side effect of the transaction, but in this case it is the most important for us.
Below is a graph of execution time versus quantity INSERTin one transaction (blue line), and a graph depending on the quantity INSERT without a general transaction (green line). Inserting in a transaction is approximately 5 times faster than inserting without a transaction, regardless of the number of inserts. If there are very few operations, then the increase will be small or not at all.
A similar situation with operations UPDATE. Below is a graph of the execution time depending on the number of updates in one transaction (red line) and a graph of the dependence on the number of updates without a common transaction (orange line). UPDATEin a transaction it is about 7 times faster than update without a transaction, regardless of the number of operations.
In any case, if there are very few operations (up to 5), the performance gain will be small, or vice versa, the transaction will slow down the recording.
You should be careful about what goes inside the transaction lambda. Since SQLIte is single-threaded for writing, a network request or mathematical logic accidentally included in a transaction can block the operation of all parts of the application that require the database.
The withTransaction block should only contain direct calls to the dao. The scope of a transaction is a single logical operation, and developers must clearly see what is happening inside it to avoid unnecessary operations accidentally falling into the transaction. If you put a call to a method of another class in a transaction, it will be difficult to keep track of its contents.
3. Combining records of several fields into 1 table
In an SQL write query, you can list several fields that need to be updated:
@Query("UPDATE chats SET input = :input, reply = :reply WHERE chat_id = :chatId")1 update that updates multiple fields is always faster than multiple, regardless of whether those multiple queries are executed in 1 transaction. You can speed up recording several times, starting with combining 2 operations into 1:
4. Ignoring useless requests
The result of some queries is obvious even before they are executed. For example, record dao.insertList(emptyList())obviously won't change the table entry dao.getById(emptyList()) will obviously return an empty list. Optimization and query planning only occur at the native code level, and the java wrapper classes do not analyze the query itself. Before SQLite understands that no operation needs to be performed, it still needs to open a transaction, compile the query, and do a number of other minor work to support the query. Such a useless request can take up to several milliseconds. If there are a lot of such requests, and the speed must be almost instantaneous, then such requests should be avoided.
Here a rule of good manners can help us: immediately exit a function if it is obvious that no useful work will be done in it. For example, if a function processes a list, then you should immediately exit the function if the list is empty. In fact, such a rule should be applied in any functions that perform heavy operations when processing a list.
fun saveChats(chats: List<Chat>) {
if (chats.isEmpty()) return
chatsDao.insertList(chats)
// какая-нибудь еще обработка списка
}
Most of the wasted write time is spent maintaining the transaction, so if the transaction has already started, then canceling any queries within it will not provide a radical improvement. If you already have to save on matches and squeeze out maximum performance, then, of course, you should ignore useless requests in transactions.
Below is a histogram that shows how long it takes to complete INSERT, DELETE, UPDATE, SELECT in parameters that have an empty list.
One useless operation is performed quickly, but if there are many of them, in total they can take an impressive amount of time to complete.
Speed up reading
In our case, it was enough to methodically apply the simple tools described above to achieve several times the improvement. Not only has the rendering speed improved, but also the speed of actually sending the message. But after speeding up the sending, we noticed that a new message appears in the chat with some delay.
The chat screen receives any message updates from the database to support the “Single Source of Truth” concept. Sent messages are no exception. The next step was to optimize the message path from the database to rendering.
1. Parallel execution of queries
By using journal_mode=WAL (this mode is enabled by default in Room), SQLite can perform multiple read operations simultaneously. This allows you to get a speed increase when parallelizing IO operations. According to observations, when combining several selects into one transaction, approximately the same speedup is achieved as when parallelizing.
You should not rely on parallelization as a panacea if there is already a lot of work happening simultaneously in io threads. If all processor cores are already busy, then parallelization will not lead to a good result.
2. Removing useless transactions
Any operation in SQLite is somehow performed in a transaction. Transactions cannot be nested. But in Room using annotation @Transaction or functions withTransaction we can combine several operations into one transaction, moreover, nested ones withTransaction will not cause the application to crash.
// Матрешка из транзакций не имеет смысла, но краша не вызовет
Db.withTransaction {
Db.withTransaction {
Dao.insert(entity)
}
}This happens because the transaction that is created by the SQLite wrappers does not correspond to the transaction inside the native code. Nested transactions at the java code level are straightened into one transaction at the native code level. However, maintaining nested transactions is not free, and if a “matryoshka” from a transaction or annotation is found @Transactionattached to the dao function, which performs one request, you can safely delete unnecessary transactions.
Acceleration of data processing
Carefully following the above approaches allowed us to greatly improve the speed of sending a single message. But forwarding a large number of messages to multiple chats could still take a long time. Next, we applied approaches that improved the processing of the data array, but had little effect on the speed of processing a single element.
1. Changing the function contract for array processing
It is easier to set up a productive system if the functions process not just 1 element, but an entire list. If a function processes only 1 element, you can skip obvious problems that greatly slow down the processing of the entire data array. All the above practices become easier to implement.
// сложнее оптимизировать
list.map { interactor.process(it) }
// проще оптимизировать
interactor.process(list)Within a function that works with a list, you can:
Take out the general part of the work and complete it for the entire list. If, when processing each element, you need, for example, to obtain data about the current user, then in the function that processes the list, you can request this data once.
Combine IO operations for each element into one. It especially worsens the performance of network requests in functions that process 1 element. If necessary, ask backend developers to add network requests that process the list of data.
This change to the function contract has 2 problems:
If tests are written for a class, they will have to be significantly rewritten.
Typically functions that process a single element are a little easier to read.
Further advice may require changes to the function contract.
2. Solution to N+1 problem
A typical N+1 problem looks like this:
// получили список ids
val messageIds = chatsDao.getLastMessageIdsByChatIds(chatIds)
// в дальнейшем для каждого из ids выполнили запрос
val message = messagesDao.getById(messageId)The problem is called n+1 because N questions are executed for each of the elements received in 1 request.
Most likely, in your code there will not be a situation where in one method you first get a list of ids, and then a separate request is called for each id. Most likely the list will be received in one method, and n other requests will be received in other methods. This is a good example of why you should design functions to take a list as a parameter rather than a single element.
There is a debate about how much of an N+1 problem actually requires a solution. SQLite developers, e.g. state in their documentation that N+1 is not a problem.
In normal situations, especially in mobile applications, the N+1 situation does not really lead to bad performance.
In our situation, where there are many large tables and high speed requirements, we had to solve the N+1 problem.
The easiest way is to reduce the number of requests to two.
// получили список ids
// SELECT last_event_id from chats …
val messageIds = chatsDao.getLastMessageIdsByChatIds(chatIds)
// получили список сущностей по ids
// SELECT * from messages where id in (:messageIds)
val messages = messagesDao.getByIds(messageIds)When using the IN operator, you need to be careful about the size of the list being passed. Increasing the length of the list, on the contrary, leads to slow operation of the request or its cancellation. A crash may occur when receiving a large array of data, due to the fact that the size of the cursor (pointer to a table row) is limited to 1 MB.
The histogram below shows how the speed of several selects of 1 element differs from one select of 10 elements.
Below is a graph of the dependence of 1 select for several elements on the number of elements (red graph) and a graph of the dependence of several selects for 1 element. Using 1 select will always be faster than several.
1 select over 100 elements is 20 times faster than 100 selects over 1 element
1 select over 50 elements is 18 times faster than 50 selects over 1 element
1 select on 10 elements is 6 times faster than 100 selects on 1 element
2. Using @Embedded and @Relation
Using built-in room annotations, you can solve N+1 problems.
Let's say we need to get a list of messages with reactions. We use the MessageReactionDB class for this
class MessageReactionDB(
@Embedded val message: MessageDB,
@Relation(
parentColumn = "message_id",
entityColumn = "message_id",
entity = ReactionDB::class
)
val reaction ReactionDb?,
)Let's write a dao function to get this object:
@Query("SELECT * FROM `messages` WHERE chat_id = :chatId ")
abstract suspend fun getMessagesReactions(chatId: String): List<MessageReactionDB>We wrote ourselves only an SQL request for receiving message, and the request for receiving reactionDB will write room for us. If you look at the generated code (implementation of the abstract class or dao interface), you can see that an SQL query of the type is created:
SELECT <перечисление нужных полей> FROM `reactions` WHERE `message_id`
IN (:<список id сообщений, полученных от запроса сообщений>)It turns out that the function getMessagesReactions() will return us the result of 2 requests: 1 request to get the messageDB list, the other to get the reactionDB list. The resulting list of reactionDB rooms is independently correlated with the required messageDBs.
3. Minimizing unnecessary triggering of reactive database subscriptions.
With Room+Coroutines you can create functions that can reactively receive database changes. For example:
@Query(“SELECT unread_messages from chats where chat_id = :chatId”)
fun observeUnreadMessagesByChatId(chatId: String): Flow<Int?> The mechanism for subscribing to database changes is based on triggers. Triggers are an internal Sqlite mechanism that allows you to set a callback for certain events. When we create a subscription to update the database, Room creates triggers for insert, update, and delete events in the table. As a result of these events, the request is executed, the result is emitted to the flow, and the subscriber receives information about the changes. It is important to understand several nuances here:
A subscription query is executed when any table row is updated. For example, if we call the above function
observeUnreadMessagesByChatIdwith the parameter chatId = 1, its request will be executed when rows with any chatId are changed.The subscription request is not executed if insert did not insert anything or update with a selection based on data that does not exist in the table.
The subscription request is executed as a result of update, which overwrites the same data. Suppose, in the chats table in the row with id = 1, unread_messages = 12. If we call update, in which we write unread_messages = 12 in the row with id = 1, then the subscription selector will still be called, despite the fact that the actual data has not changed .
As a result, if the application has subscriptions to database changes, then you should always call flow subscriptions distinctUntilChanged().
But even if flow is called distinctUntilChanged() The subscription requests themselves are still executed for each change. If such queries include a lot of data and are called frequently, then it can somewhat slow down the work of other operations that occur simultaneously.
More problems arise when, as a result of list processing, data in a table is often overwritten with new ones. Then distintUntilChanged() no longer helps. If heavy operations occur as a result of table updates (for example, mapping, other queries in the database, network queries, rendering), then frequent updates of this table can greatly slow down the operation of the entire application.
You can detect this problem if you enable the display of executed requests in the logs using the adb command adb shell setprop log.tag.SQLiteStatements VERBOSE or by self-processing via RoomDatabase.QueryCallback
If suddenly a problem is discovered, then you can go in the following ways:
Check the value of a field before updating it in the table.
If a field in a table is updated cyclically, redo the update only at the end of the cycle. For example, when you send a message, the id of the last message in the chat is updated in the database. If several messages were sent (for example, when forwarding), then the id of the last message was rewritten for each message sent. Since we have subscriptions to chat table changes, this overwriting had a significant impact on overall performance.
In the subscription request, specify only the necessary fields so that updating other fields does not cause an unnecessary reaction to updating the table.
Use your own database update subscription system.
Observations from optimization
There are more global optimization methods, for example, changing the database structure: splitting/joining tables, adding indexes, changing field data types. Before making local changes, you need to check that everything is correct with the database structure. It's better to design new tables with performance in mind before problems arise.
Often it is not necessary to create complex algorithms to achieve a good result. To optimize network and database queries, simple tools and approaches are often sufficient.
When optimizing, you should always be guided by measurements. What the android documentation or community advises may not give a guaranteed result in practice.
When optimizing a complex problem, you should not concentrate on squeezing the maximum possible speed of individual sections. The challenge of the performance is to find a balance between speed, readability and security. It is worth following a gradual approach – first simple, safe, readable changes throughout the task and, as necessary, more complex, controversial ones.
Commits with optimizations should be kept to a minimum size. Sometimes it’s tempting to make a lot of changes at once, collect metrics, get a cool result and post a big commit that should make everything better. But with a high probability he will break something. It's better to break changes into small, but not so grandiose, commits.
To be continued
The process of speeding up message sending did not consist only of optimizing the database. We will talk about optimization of the presentation layer in the next article.
