Android SQLite Performance Analysis

As an Android preset database module, it is necessary to have a deep understanding of SQLite and find some optimization directions. Here, we have tested and analyzed the performance and memory of SQLite, compared the execution performance and memory usage of different operations, and roughly listed it here as a summary.

1. Basic Architecture

First, let's take a look at the main architecture of SQLite (see the SQLite Definitive Guide). We need to focus on the Compiler and Backend modules. Because a virtual machine exists, Compiled Statement has the value because it can reduce the pre-Compilation Time and directly run on VDBE. The Pager on the Backend end is an important data manager who determines the performance of data operations and memory usage.

I will not go into details here. For details, refer to the SQLite-related documents.

2. Performance

This test is based on suminii9103 and Google Nexus S1. Use the Python script and adb command to operate the Android app database through Intent.

I. SELECT Operation

First, we observe the peak distribution of the SELECT statement under different records. It can be seen that there is an increasing trend on the whole. As SQLite officially said, It is not suitable for storing a large amount of data and must control the number of records.

In addition, the performance of the SELECT operation depends on the number of queried columns.@ XingyunFurther confirmation is related to the content size of the returned column, that is, the content and quantity of the column must be weighed.

Ii. About transactions

Transactions are an important technique to improve the performance of SQLite operations. In particular, SQLite implements WAL, so that transactions and SELECT are not mutually exclusive and the application performance is greatly improved. See Appendix 2.

The following is the average value of the three types of operations when transactions are not used:

After transactions are used, the performance of each operation is greatly reduced.

However, the problem is that the transaction commit time will become longer. This time requires a new peak value. In addition, we need to divide the time into different operations:

Therefore, transactions must be submitted in a timely manner. In addition, although Compiled Statement is conducive to performance, it is not as direct as using transactions.

Iii. Operational performance of different models

First, let's look at the performance of the average value of the SELECT Operation on two models. We can see that the volatility on i9103 is relatively large, while Nexsus S1 remains stable:

Scatter chart, you can observe the time distribution:

Let's look at the performance of the other three operations (both the upper and lower parts are false and true, indicating that transactions are not used and transactions are used respectively ):

Using WAL can greatly improve performance, but it also has a side effect. It will cause two data queries, which can be understood as one in the db file and one in the wal file. For more information, see <SQLite Definitive Guide> The last chapter. WAL is merged into the master database only when it is full pages. This time is called a checking point and can be configured. Calculated based on the default Page Size: 1024, that is, when the WAL file approaches 1 MB, check. If no active transactions use these pages, they will be committed. The size of WAL has different effects on SELECT.

The following is the test data that WAL has an impact on the query performance peak (tested on i9103). true and false in the figure indicate whether a WAL file exists.

* The average value varies by 1 ms, but the peak value is really worth noting.

To avoid too much WAL, you can choose to call SQLiteDatabase: disableWriteAheadLoggingMethod () to forcibly merge it into the master database file. This call will be executed asynchronously within SQLite and will not significantly block users' threads.

* In addition, SQLiteDatabase: query () is only an encapsulation of SQLiteDatabase: rawQuery (). It is better to directly use SQLiteDatabase: rawQuery () instead of a string assembly process ().

3. Memory

SQLite stores data in pages. By default, a Page contains 1024 bytes and is organized by B-Tree (Table is organized by B + Tree ):

Lookaside is the memory management technology of SQLite applications. It optimizes the memory usage efficiency. The main idea is to first allocate a whole block of memory, divide it into several slots, and then use SQLite as needed. The idea of many small memory splitters is the same. For details, see Appendix 1.

Another explanation of Page Cache Overflow is that the data recorded in a Page cannot be stored in a Page, and another additional Page space is used. This is the Overflow Page.

Android also has a universal dumpsys. Using dumpsys meminfo, you can view the memory used by SQLite in a process. For example:

SQL

Heap: 265 MEMORY_USED: 265

PAGECACHE_OVERFLOW: 73 MALLOC_SIZE: 46

DATABASES

Pgsz dbsz Lookaside (B) cache Dbname

4 60 17 199/114/1 webviewCache. db

1/541/1 (pooled #1) webviewCache. db

The three values of. cache are:

The number of Page Cache hits, the number of missed pages, and the number of Page Cache. In the Android source codeSQLiteDebug. javaAndActivityThread. javaFind the details.

. Page size, the unit of db size is KBytes, and Lookaside (B) refers to the number of Lookaside slots used.

For Heap and Overflow Pages, SQLiteDatabase memory recovery may not be so timely, you can call SQLiteDatabase: releaseMemory () for active release.

* PRAGMA of SQLite can be used to directly obtain information that cannot be obtained through SQLiteDatabase. Of course, if SQLite does not support this function, an exception is thrown. For details, see Appendix 4.

Reprinted please note the Source: http://blog.csdn.net/horkychen SQLite is a very exquisite system, it is worth studying.

Reference

1. SQLite Dynamic Memory Allocation

2. Write-Ahead Logging or SQLite WAL Mechanism

3. SQLite Definitive Guide (here are two articles about architecture and Overflow page .)

4. PRAGMA Statement

5. Official documents

6. NEC Research Report on Memory Operation Performance on Android