Bonnes pratiques pour les performances de SQLite (vues)

Concepts et implémentation de Jetpack Compose

Android est compatible avec SQLite, une base de données SQL particulièrement efficace. Suivez ces bonnes pratiques pour optimiser les performances de votre application afin qu'elle reste rapide à long terme tandis que votre volume de données augmente. En appliquant ces bonnes pratiques, vous limiterez également le risque de rencontrer des problèmes de performances difficiles à reproduire et à résoudre.

Pour des performances plus rapides, suivez ces principes :

• Lire moins de lignes et de colonnes : optimisez vos requêtes pour ne récupérer que les données nécessaires. Réduisez la quantité de données lues à partir de la base de données, car une récupération excessive de données peut affecter les performances.

• Transférer les tâches vers le moteur SQLite : effectuez les opérations de calcul, de filtrage et de tri dans des requêtes SQL. L'utilisation du moteur de requêtes de SQLite peut améliorer considérablement les performances.

• Modifier le schéma de la base de données : concevez votre schéma de base de données pour aider SQLite à créer des plans de requête et des représentations de données efficaces. Ajoutez correctement des indices dans les tables et optimisez leur structure pour améliorer les performances.

De plus, vous pouvez utiliser les outils de dépannage disponibles pour mesurer les performances de votre base de données SQLite et identifier les domaines à optimiser.

Nous vous recommandons d'utiliser la bibliothèque Jetpack Room.

Configurer la base de données pour optimiser les performances

Suivez la procédure décrite dans cette section pour configurer votre base de données afin d'optimiser les performances dans SQLite.

Assouplir le mode de synchronisation

Lorsque vous utilisez la journalisation WAL, chaque commit émet un fsync pour garantir que les données atteignent le disque. Cela améliore la durabilité des données, mais ralentit les commits.

SQLite propose une option pour contrôler le mode synchrone. Si vous activez la journalisation WAL, définissez le mode synchrone sur NORMAL :

Kotlin

// When opening the database
val paramsBuilder: SQLiteDatabase.OpenParams.Builder = SQLiteDatabase.OpenParams.Builder()
paramsBuilder.journalMode = SQLiteDatabase.SYNC_MODE_NORMAL

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL");

Java

// When opening the database
SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder();
paramsBuilder.setJournalMode(SQLiteDatabase.SYNC_MODE_NORMAL);

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL");

Avec ce paramètre, un commit peut être renvoyé avant que les données ne soient stockées sur un disque. En cas d'arrêt d'un appareil, par exemple en cas de panne de courant ou de panique du noyau, les données validées peuvent être perdues. Cependant, en raison de la journalisation, votre base de données n'est pas corrompue.

Si seule votre application plante, vos données atteindront toujours le disque. Pour la plupart des applications, ce paramètre permet d'améliorer les performances sans frais matériels.

Remarque : Si votre application comporte plusieurs bases de données, utilisez le même paramètre synchrone partout où il existe des dépendances de données entre différentes bases de données.

Améliorer les performances des requêtes

Suivez ces bonnes pratiques pour améliorer les performances des requêtes dans SQLite en réduisant les temps de réponse et en optimisant l'efficacité du traitement.

Remarque : De nombreux exemples sur cette page incluent une clause LIMIT. Son utilisation est conseillée, car les requêtes susceptibles de renvoyer de nombreuses lignes affectent les performances lors du renvoi de grands ensembles de données. Les requêtes qui renvoient implicitement un ensemble de données soumis à une contrainte de taille constituent toutefois une exception à cette règle. Par exemple, la recherche sur un champ défini comme UNIQUE ne peut renvoyer qu'une seule ligne ou aucune.

Lire uniquement les lignes dont vous avez besoin

Les filtres vous permettent d'affiner vos résultats en spécifiant certains critères, tels que la plage de dates, le lieu ou le nom. Les limites vous permettent de contrôler le nombre de résultats affichés :

Kotlin

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

Java

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

Lire uniquement les colonnes dont vous avez besoin

Évitez de sélectionner des colonnes inutiles, car cela peut ralentir les requêtes et gaspiller des ressources. Sélectionnez uniquement les colonnes qui sont utilisées.

Dans l'exemple suivant, vous sélectionnez id, name et phone :

Kotlin

// 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)
        // Further processing
    }
}

Java

// 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);
    // Further processing
  }
}

Cependant, vous n'avez besoin que de la colonne name :

Kotlin

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

Java

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

Paramétrer les requêtes

Votre chaîne de requête peut inclure un paramètre qui n'est connu qu'au moment de l'exécution, comme suit :

Kotlin

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
        }
    }
}

Java

@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;
    }
  }
}

Dans le code précédent, chaque requête construit une chaîne différente et ne bénéficie donc pas du cache d'instruction. Chaque appel nécessite que SQLite le compile avant de pouvoir l'exécuter. Vous pouvez remplacer l'argument id par un paramètre et lier la valeur à selectionArgs :

Kotlin

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
        }
    }
}

Java

@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;
    }
  }
}

La requête peut maintenant être compilée une seule fois et mise en cache. La requête compilée est réutilisée entre différentes invocations de getNameById(long).

Attention : Si l'argument d'entrée est un autre objet moins contraint qu'un simple nombre, la concaténation de chaînes peut entraîner une faille de sécurité d'injection SQL. Utilisez toujours des paramètres pour les variables ou les données non fiables.

Utiliser DISTINCT pour les valeurs uniques

L'utilisation du mot clé DISTINCT contribue à améliorer les performances des requêtes en réduisant la quantité de données à traiter. Par exemple, si vous souhaitez ne renvoyer que les valeurs uniques d'une colonne, utilisez DISTINCT :

Kotlin

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

Java

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

Utiliser des fonctions d'agrégation autant que possible

Utilisez des fonctions d'agrégation pour obtenir des résultats agrégés sans données de ligne. Par exemple, le code suivant vérifie s'il existe au moins une ligne correspondante :

Kotlin

// 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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

Java

// 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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

Pour récupérer uniquement la première ligne, vous pouvez utiliser EXISTS() afin de renvoyer 0 si aucune ligne ne correspond et 1 si une ou plusieurs lignes correspondent :

Kotlin

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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

Java

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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

Utilisez des fonctions d'agrégation SQLite dans le code de votre application :

• COUNT : comptabilise le nombre de lignes dans une colonne.
• SUM : ajoute toutes les valeurs numériques d'une colonne.
• MIN ou MAX : détermine la valeur la plus faible ou la plus élevée. Fonctionne pour les colonnes numériques, les types DATE et les types de texte.
• AVG : détermine la valeur numérique moyenne.
• GROUP_CONCAT : concatène des chaînes avec un séparateur facultatif.

Utiliser COUNT() à la place de Cursor.getCount()

Dans l'exemple suivant, la fonction Cursor.getCount() lit toutes les lignes de la base de données et renvoie toutes les valeurs de ligne :

Kotlin

// 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()
    // Use count
}

Java

// 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();
  // Use count
}

Cependant, si vous utilisez COUNT(), la base de données ne renvoie que le nombre de lignes :

Kotlin

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

Java

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

Imbriquer des requêtes au lieu de code

SQL est composable et est compatible avec les sous-requêtes, les jointures et les contraintes de clé étrangère. Vous pouvez inclure le résultat d'une seule requête dans une autre sans avoir à passer par le code de l'application. Cette approche vous évite d'avoir à copier des données à partir de SQLite et permet au moteur de base de données d'optimiser la requête.

Dans l'exemple suivant, vous pouvez exécuter une requête permettant d'identifier la ville qui compte le plus de clients, puis utiliser ce résultat dans une autre requête pour trouver tous les clients de cette ville :

Kotlin

// 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()) {
                // Process inner cursor data
            }
        }
    }
}

Java

// 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()) {
          // Process inner cursor data
        }
    }
  }
}

Pour obtenir le résultat deux fois plus rapidement que dans l'exemple précédent, utilisez une seule requête SQL avec des instructions imbriquées :

Kotlin

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()) {
        // Process cursor data
    }
}

Java

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()) {
    // Process cursor data
  }
}

Vérifier les valeurs uniques en SQL

Si une ligne ne doit être insérée que dans le cas où une valeur de colonne particulière est unique dans la table, il peut être plus efficace d'appliquer cette condition en tant que contrainte de colonne.

Dans l'exemple suivant, une seule requête est exécutée pour valider la ligne à insérer et une autre à insérer :

Kotlin

// 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
    )
)

Java

// 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,
    });

Au lieu de vérifier la contrainte de valeur unique en Kotlin ou Java, vous pouvez le faire en SQL lorsque vous définissez la table :

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

SQLite effectue les mêmes opérations que :

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

Remarque : Une table d'indices est créée pour username, ce qui utilise de l'espace de stockage supplémentaire. Pour découvrir comment interroger une table d'indices, consultez Accélérer les requêtes avec les indices.

Vous pouvez maintenant insérer une ligne et laisser SQLite vérifier la contrainte :

Kotlin

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

Java

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);
}

Remarque : Si vous définissez INTEGER PRIMARY KEY, une contrainte de valeur unique s'applique à cette colonne et n'utilise pas de table d'indices supplémentaire.

SQLite prendre en charge les indices uniques comportant plusieurs colonnes :

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

SQLite valide les contraintes plus rapidement et en impliquant moins de frais que le code Kotlin ou Java. Il est recommandé d'utiliser SQLite plutôt que le code de l'application.

Regrouper plusieurs insertions en une seule transaction

Une transaction valide plusieurs opérations, ce qui améliore non seulement l'efficacité, mais aussi l'exactitude. Pour améliorer la cohérence des données et accélérer les performances, vous pouvez regrouper les insertions par lot :

Kotlin

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

Java

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

Remarque : Seule une transaction d'écriture peut avoir lieu à la fois. Utilisez MoreExecutors.newSequentialExecutor(Executor) pour sérialiser les écritures.