Android : 5 astuces pour optimiser une base SQLite

Vous avez une application Android embarquant une base de données SQLite ?

Voici 5 astuces pour optimiser les lectures, mises à jour, insertions et suppressions répétitives de données dans une base de données SQLite

1) Utilisez une transaction globale

Si vous avez l'habitude d'utiliser des transactions, la première astuce consiste à gérer une transaction globale pour l'ensemble des insertions ou des mises à jour de données.
Cela permet de réduire le nombre de transactions exécutées et de réduire les temps d'exécution pour l'ensemble des requêtes.

db.beginTransaction();
try {

    // insC)rez vos donnC)es ici

    db.setTransactionSuccessful();

} finally {
    db.endTransaction();
}

2) Désactivez l'Auto-commit

Par défaut si vous n'utilisez pas de transaction, la base réalise un "commit" à chaque exécution d'une requête SQL.
Pour éviter cela, vous pouvez désactiver la fonctionnalité "auto-commit" de la base pour une requête répétitive.

db.getConnection().setAutoCommit(false);

 // insC)rez vos donnC)es ici

db.getConnection().commit();
db.getConnection().setAutoCommit(true);

Ce type d'optimisation peut permettre de rendre un UPDATE 60 fois plus rapide.

3) Pensez asynchrone

La deuxième astuce consiste à configurer la base pour que les écritures de fichiers soient réalisées de manière asynchrone à chaque insertion ou modification de données.

Pour cela, la commande SQL suivante doit être exécutée.

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

Un chapitre de la documentation de SQLite décrit précisément ce paramètre peu connu.

4) Utilisez des Statements pour les requêtes répétitives

Vous avez besoin de réaliser une requête dans un while ou un for ? Utilisez des "statements" ! Vous diviserez pas trois le temps d'exécution d'une simple requête.
La réutilisation d'un "statement" permet d'éviter à la base de données de recalculer le plan d'exécution de la requête.

Au lieu de :

for (int i = 0; i < limit; i++) {
 comm.CommandText = "insert into test values (" + i.ToString() + ")";
 comm.ExecuteNonQuery();
}

Préférez plutôt :

SQLiteParameter param = comm.CreateParameter();
comm.CommandText = "insert into test values (?)";
comm.Parameters.Add(param);

for (int i = 0; i < limit; i++) {
   param.Value = i;
   comm.ExecuteNonQuery();
}

L'effort de développement n'est pas compliqué et sur des longues itérations vous constaterez un bénéfice important.

5) Utilisez des index

Avez-vous pensé à créer des index sur les données que vous mettez à jour ou sur les données que vous consultez ?
Les index sont primordiaux pour limiter le nombre de blocs de données que la base doit lire pour répondre à une requête SQL.

Si vous avez des doutes sur une requête, n'hésitez pas à utiliser la commande SQL "EXPLAIN" pour comprendre réellement ce que fait la base de données pour retourner le résultat de la requête.
En cas de "full-scan", identifiez les index manquants et n'hésitez pas à créer un index pour estimer le gain de performance gagné par cet index.

---