Best practices for SQLite performance

Android offers built-in support for SQLite, an efficient SQL database. Follow these best practices to optimize your app's performance, ensuring it remains fast and predictably fast as your data grows. By using these best practices, you also reduce the possibility of encountering performance issues that are difficult to reproduce and troubleshoot.

To achieve faster performance, follow these performance principles:

• Read fewer rows and columns: Optimize your queries to retrieve only the necessary data. Minimize the amount of data read from the database, because excess data retrieval can impact performance.

• Push work to SQLite engine: Perform computations, filtering, and sorting operations within the SQL queries. Using SQLite's query engine can significantly improve performance.

• Modify the database schema: Design your database schema to help SQLite construct efficient query plans and data representations. Properly index tables and optimize table structures to enhance performance.

Additionally, you can use the available troubleshooting tools to measure the performance of your SQLite database to help identify areas that require optimization.

We recommend using the Jetpack Room library.

Configure the database for performance

Follow the steps in this section to configure your database for optimal performance in SQLite.

Enable Write-Ahead Logging

SQLite implements mutations by appending them to a log, which it occasionally compacts into the database. This is called Write-Ahead Logging (WAL).

Enable WAL unless you are using ATTACH DATABASE.

Relax the synchronization mode

When using WAL, by default every commit issues an fsync to help ensure that the data reaches the disk. This improves data durability but slows down your commits.

SQLite has an option to control synchronous mode. If you enable WAL, set synchronous mode to NORMAL:

db.execSQL("PRAGMA synchronous = NORMAL")

db.execSQL("PRAGMA synchronous = NORMAL");

In this setting, a commit can return before the data is stored in a disk. If a device shutdown occurs, such as on loss of power or a kernel panic, the committed data might be lost. However, because of logging, your database isn't corrupted.

If only your app crashes, your data still reaches the disk. For most apps, this setting yields performance improvements at no material cost.

Define efficient table schemas

To optimize performance and minimize data consumption, define an efficient table schema. SQLite constructs efficient query plans and data, leading to faster data retrieval. This section provides best practices for creating table schemas.

Consider INTEGER PRIMARY KEY

For this example, define and populate a table as follows:

CREATE TABLE Customers(
  id INTEGER,
  name TEXT,
  city TEXT
);
INSERT INTO Customers Values(456, 'John Lennon', 'Liverpool, England');
INSERT INTO Customers Values(123, 'Michael Jackson', 'Gary, IN');
INSERT INTO Customers Values(789, 'Dolly Parton', 'Sevier County, TN');

The table output is as follows:

The column rowid is an index that preserves insertion order. Queries that filter by rowid are implemented as a fast B-tree search, but queries that filter by id are a slow table scan.

If you plan on doing lookups by id, you can avoid storing the rowid column for less data in storage and an overall faster database:

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

Your table now looks as follows:

Since you don't need to store the rowid column, id queries are fast. Note that the table is now sorted based on id instead of insertion order.

Accelerate queries with indexes

SQLite uses indexes to accelerate queries. When filtering (WHERE), sorting (ORDER BY), or aggregating (GROUP BY) a column, if the table has an index for the column, the query is accelerated.

In the previous example, filtering by city requires scanning the entire table:

SELECT id, name
WHERE city = 'London, England';

For an app with a lot of city queries, you can accelerate those queries with an index:

CREATE INDEX city_index ON Customers(city);

An index is implemented as an additional table, sorted by the index column and mapped to rowid:

Note that the storage cost of the city column is now double, because it's now present in both the original table and the index. Since you are using the index, the cost of added storage is worth the benefit of faster queries. However, don't maintain an index that you're not using to avoid paying the storage cost for no query performance gain.

Create multi-column indexes

If your queries combine multiple columns, you can create multi-column indexes to fully accelerate the query. You can also use an index on an outside column and let the inside search be done as a linear scan.

For instance, given the following query:

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

You can accelerate the query with a multi-column index in the same order as specified in the query:

CREATE INDEX city_name_index ON Customers(city, name);

However, if you only have an index on city, the outside ordering is still accelerated, while the inside ordering requires a linear scan.

This also works with prefix inquiries. For example, an index ON Customers (city, name) also accelerates filtering, ordering, and grouping by city, since the index table for a multi-column index is ordered by the given indexes in the given order.

Consider WITHOUT ROWID

By default, SQLite creates a rowid column for your table, where rowid is an implicit INTEGER PRIMARY KEY AUTOINCREMENT. If you already have a column that is INTEGER PRIMARY KEY, then this column becomes an alias of rowid.

For tables that have a primary key other than INTEGER or a composite of columns, consider WITHOUT ROWID.

Store small data as a BLOB and large data as a file

If you want to associate large data with a row, such as a thumbnail of an image or a photo for a contact, you can store the data either in a BLOB column or in a file, and then store the file path in the column.

Files are generally rounded up to 4 KB increments. For very small files, where the rounding error is significant, it's more efficient to store them in the database as a BLOB. SQLite minimizes filesystem calls and is faster than the underlying filesystem in some cases.

Improve query performance

Follow these best practices to improve query performance in SQLite by minimizing response times and maximizing processing efficiency.

Read only the rows you need

Filters let you narrow down your results by specifying certain criteria, such as date range, location, or name. Limits let you control the number of results you see:

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

Read only the columns you need

Avoid selecting unneeded columns, which can slow down your queries and waste resources. Instead, only select columns that are used.

In the following example, you select id, name, and phone:

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT id, name, phone
    FROM customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(1)
        // ...
    }
}

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name, phone
    FROM customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(1);
    ...
  }
}

However, you only need the name column:

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

Parameterize queries with SQL Cards, not with String concatenation

Your query string might include a parameter that is only known at runtime, such as the following:

fun getNameById(id: Long): String? 
    db.rawQuery(
        "SELECT name FROM customers WHERE id=$id", null
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery(
      "SELECT name FROM customers WHERE id=" + id, null)) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

In the preceding code, every query constructs a different string, and thus doesn't benefit from the statement cache. Each call requires SQLite to compile it before it can execute. Instead, you can replace the id argument with a parameter and bind the value with selectionArgs:

fun getNameById(id: Long): String? {
    db.rawQuery(
        """
          SELECT name
          FROM customers
          WHERE id=?
        """.trimIndent(), arrayOf(id.toString())
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery("""
          SELECT name
          FROM customers
          WHERE id=?
      """, new String[] {String.valueOf(id)})) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

Now the query can be compiled once and cached. The compiled query is reused between different invocations of getNameById(long).

Iterate in SQL, not in code

Use a single query that returns all targeted results, instead of a programmatic loop iterating on SQL queries to return individual results. The programmatic loop is about 1000 times slower than a single SQL query.

Use DISTINCT for unique values

Using the DISTINCT keyword can improve the performance of your queries by reducing the amount of data that needs to be processed. For example, if you want to return only the unique values from a column, use DISTINCT:

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

Use aggregate functions whenever possible

Use aggregate functions for aggregate results without row data. For example, the following code checks whether there is at least one matching row:

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
}

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """, null)) {
  if (cursor.moveToFirst()) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

To only fetch the first row, you can use EXISTS() to return 0 if a matching row does not exist and 1 if one or more rows match:

db.rawQuery("""
    SELECT EXISTS (
        SELECT null
        FROM Customers
        WHERE city = 'Paris';
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM Customers
      WHERE city = 'Paris'
    );
    """, null)) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

Use SQLite aggregate functions in your app code:

• COUNT: counts how many rows are in a column.
• SUM: adds all numerical values in a column.
• MIN or MAX: determines the lowest or highest value. Works for numeric columns, DATE types, and text types.
• AVG: finds the average numerical value.
• GROUP_CONCAT: concatenates strings with an optional separator.

Use COUNT() instead of Cursor.getCount()

In the following example, the Cursor.getCount() function reads all the rows from the database and returns all the row values:

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    val count = cursor.getCount()
}

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id
    FROM Customers;
    """, null)) {
  int count = cursor.getCount();
  ...
}

However, by using COUNT(), the database returns only the count:

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
}

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

Nest queries instead of code

SQL is composable and supports subqueries, joins, and foreign key constraints. You can use the result of one query in another query without going through app code. This reduces the need to copy data from SQLite and lets the database engine optimize your query.

In the following example, you can run a query to find which city has the most customers, then use the result in another query to find all the customers from that city:

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        val topCity = cursor.getString(0)
        db.rawQuery("""
            SELECT name, city
            FROM Customers
            WHERE city = ?;
        """.trimIndent(),
        arrayOf(topCity)).use { innerCursor ->
            while (innerCursor.moveToNext()) {
                ...
            }
        }
    }
}

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """, null)) {
  if (cursor.moveToFirst()) {
    String topCity = cursor.getString(0);
    try (Cursor innerCursor = db.rawQuery("""
        SELECT name, city
        FROM Customers
        WHERE city = ?;
        """, new String[] {topCity})) {
        while (innerCursor.moveToNext()) {
          ...
        }
    }
  }
}

To get the result in half the time of the previous example, use a single SQL query with nested statements:

db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
        SELECT city
        FROM Customers
        GROUP BY city
        ORDER BY COUNT (*) DESC
        LIMIT 1;
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToNext()) {
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
      SELECT city
      FROM Customers
      GROUP BY city
      ORDER BY COUNT(*) DESC
      LIMIT 1
    );
    """, null)) {
  while(cursor.moveToNext()) {
    ...
  }
}

Check uniqueness in SQL

If a row must not be inserted unless a particular column value is unique in the table, then it might be more efficient to enforce that uniqueness as a column constraint.

In the following example, one query is run to validate the row to be inserted and another to actually insert:

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT EXISTS (
        SELECT null
        FROM customers
        WHERE username = ?
    );
    """.trimIndent(),
    arrayOf(customer.username)
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        throw AddCustomerException(customer)
    }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    arrayOf(
        customer.id.toString(),
        customer.name,
        customer.username
    )
)

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM customers
      WHERE username = ?
    );
    """, new String[] { customer.username })) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    throw new AddCustomerException(customer);
  }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    new String[] {
      String.valueOf(customer.id),
      customer.name,
      customer.username,
    });

Instead of checking the unique constraint in Kotlin or Java, you can check it in SQL when you define the table:

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE
);

SQLite does the same as the following:

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

Now you can insert a row and let SQLite check the constraint:

try {
    db.execSql(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

try {
  db.execSQL(
      "INSERT INTO Customers VALUES (?, ?, ?)",
      new String[] {
        String.valueOf(customer.id),
        customer.name,
        customer.username,
      });
} catch (SQLiteConstraintException e) {
  throw new AddCustomerException(customer, e);
}

SQLite supports unique indexes with multiple columns:

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

SQLite validates constraints faster and with less overhead than Kotlin or Java code. It is a best practice to use SQLite rather than app code.

Batch multiple insertions in a single transaction

A transaction commits multiple operations, which improves not only efficiency but also correctness. To improve data consistency and accelerate performance, you can batch insertions:

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSql(
            "INSERT INTO Customers VALUES (?, ?, ...)",
            arrayOf(customer.id.toString(), customer.name, ...)
        )
    }
} finally {
    db.endTransaction()
}

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

Use troubleshooting tools

SQLite provides the following troubleshooting tools to help measure performance.

Use SQLite's interactive prompt

Run SQLite on your machine to run queries and learn. Different Android platform versions use different revisions of SQLite. To use the same engine that's on an Android-powered device, use adb shell and run sqlite3 on your target device.

You can ask SQLite to time queries:

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

You can ask SQLite to explain how it intends to answer a query by using EXPLAIN QUERY PLAN:

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

The previous example requires a full table scan without an index to find all customers from Paris. This is called linear complexity. SQLite needs to read all the rows and only keep the rows that match customers from Paris. To fix this, you can add an index:

sqlite> CREATE INDEX Idx1 ON Customers(city);
sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SEARCH test USING INDEX Idx1 (city=?

If you're using the interactive shell, you can ask SQLite to always explain query plans:

sqlite> .eqp on

For more information, see Query Planning.

SQLite Analyzer

SQLite offers the sqlite3_analyzer command-line interface (CLI) to dump additional information that can be used to troubleshoot performance. To install, visit the SQLite Download Page.

You can use adb pull to download a database file from a target device to your workstation for analysis:

adb pull /data/data/<app_package_name>/databases/<db_name>.db

SQLite Browser

You can also install the GUI tool SQLite Browser on the SQLite Downloads page.

Android logging

Android times SQLite queries and logs them for you:

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR
```### Perfetto tracing

### Perfetto tracing {:#perfetto-tracing}

When [configuring Perfetto](https://perfetto.dev/docs/concepts/config), you may
add the following to include tracks for individual queries:

```protobuf
data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}