Quand je travaille avec SQLite, je le vois souvent comme un moteur de base de données simple, embarqué, léger, et donc naturellement rapide. En pratique, c’est vrai tant que le volume reste raisonnable, que les requêtes sont bien écrites, et surtout que les index sont correctement pensés.

Le problème, de mon point de vue, commence quand une application grandit. Une table qui contenait quelques centaines de lignes en contient soudain plusieurs dizaines de milliers, puis plusieurs millions. Une requête autrefois instantanée devient lente. Un écran de consultation prend une seconde, puis trois, puis dix. Très souvent, la cause est la même : un table scan.

Dans mon nouveau projet AuditStock, j’ai justement eu besoin de cette approche. Sur ce projet, qui manipule énormément de données de stock, d’historique et d’agrégation produit/dépôt, j’ai rapidement constaté qu’il ne suffisait pas que SQLite soit léger pour que tout reste rapide dans la durée. Dès que les données grossissent, les requêtes mal indexées finissent par se voir.

Dans mes projets .NET qui utilisent EF Core avec SQLite, j’aime donc beaucoup utiliser un intercepteur pour détecter automatiquement les requêtes problématiques. C’est précisément l’objectif de SqliteScanBusterInterceptor.

Dans cet article, je vais expliquer en détail :

  • ce qu’est un table scan,
  • pourquoi c’est très mauvais pour les performances,
  • comment SqliteScanBusterInterceptor fonctionne,
  • pourquoi EXPLAIN QUERY PLAN est indispensable,
  • comment bien optimiser les index dans SQLite,
  • et comment transformer cette détection en vraie discipline de performance.

Pourquoi SQLite n’est pas magique

Pour moi, SQLite est un excellent moteur relationnel. Il offre de nombreux avantages :

  • aucun serveur séparé à installer,
  • déploiement extrêmement simple,
  • bonnes performances sur de nombreux scénarios,
  • très faible coût opérationnel,
  • excellent choix pour les applications locales, embarquées ou des outils métiers.

Mais je me répète souvent une chose : SQLite n’est pas magique.

Comme tout moteur SQL, il doit :

  • lire des pages de données,
  • filtrer des lignes,
  • trier des résultats,
  • faire des jointures,
  • exploiter ou non des index.

Si une requête est mal conçue ou si les index sont absents, SQLite devra travailler beaucoup plus que nécessaire, et je finis presque toujours par le payer plus tard.

Le moteur est rapide, mais il ne peut pas compenser un mauvais plan d’exécution.

Qu’est-ce qu’un table scan

Un table scan signifie que le moteur parcourt une table de manière large, souvent en lisant toutes les lignes, pour trouver celles qui correspondent à la requête.

Exemple concret dans ce projet :

SELECT *
FROM ProductsByWarehousesHistories
WHERE ProductByWarehouseId = @productByWarehouseId;

Si ProductByWarehouseId n’est pas indexé, SQLite peut être contraint de lire toutes les lignes de ProductsByWarehousesHistories une à une afin de retrouver l’historique d’un couple produit/dépôt.

Au lieu d’aller directement au bon endroit, le moteur inspecte toute la table.

C’est exactement le type de situation que j’essaie d’éviter sur les tables volumineuses.

Pourquoi un table scan est très mauvais pour la performance

À mes yeux, un table scan est souvent l’un des pires signaux de performance sur une base SQLite utilisée par une application métier.

1. Le coût augmente avec la taille de la table

Sans index adapté, la requête doit parcourir un nombre de lignes proportionnel à la taille de la table.

Sur 100 lignes, l’impact est minime. Sur 100 000 lignes, cela devient notable. Sur 5 millions de lignes, cela devient critique.

Autrement dit, une requête basée sur un scan vieillit très mal avec la croissance des données.

2. Beaucoup de lectures inutiles

Quand SQLite scanne une table, il lit de nombreuses lignes qui ne seront finalement pas retournées.

Exemple : si une requête renvoie 12 lignes mais doit en inspecter 500 000, presque tout le travail effectué est inutile.

3. Dégradation du temps de réponse utilisateur

Un scan impacte directement :

  • la vitesse de chargement des écrans,
  • la réactivité d’une API,
  • la fluidité globale de l’application,
  • la capacité à supporter davantage d’utilisateurs et de données.

4. Pollution du cache

En lisant beaucoup de données non pertinentes, le moteur consomme du cache pour des pages peu utiles. Cela dégrade aussi les performances d’autres requêtes.

5. Effet multiplicateur

Le danger réel d’un scan n’est pas seulement sa lenteur unitaire. C’est surtout sa répétition.

Une requête lente appelée :

  • dans chaque chargement de page,
  • plusieurs fois par écran,
  • pour chaque utilisateur,
  • sur une table qui grossit,

finit par devenir un goulet d’étranglement majeur.

6. Risque de fausse impression au début du projet

Beaucoup de requêtes paraissent rapides au début parce que la base contient peu de données. Cela donne une illusion de sécurité. Puis, dès que la volumétrie augmente, les problèmes apparaissent brutalement.

C’est la raison pour laquelle j’essaie de traquer les scans tôt, même quand tout semble encore rapide.

Pourquoi les index sont fondamentaux dans SQLite

Un index permet à SQLite d’accéder beaucoup plus rapidement aux lignes pertinentes sans parcourir toute la table.

Sans index :

  • la base cherche en lisant largement,
  • le coût est élevé,
  • la montée en charge se dégrade.

Avec un bon index :

  • la base navigue directement vers la zone utile,
  • le nombre de lectures chute,
  • la requête devient plus stable dans le temps.

Pour moi, l’idée importante n’est pas seulement d’avoir des index.

Il faut avoir les bons index, c’est-à-dire des index alignés sur les vraies requêtes de l’application.

Pourquoi utiliser un intercepteur EF Core

Quand j’utilise EF Core, je trouve qu’il est très facile de perdre de vue les requêtes réellement exécutées. Le code LINQ semble propre, mais la base exécute du SQL concret. Et c’est ce SQL réel que j’ai besoin d’analyser.

Un intercepteur EF Core permet de :

  • observer les commandes SQL exécutées,
  • mesurer leur durée,
  • enrichir la journalisation,
  • détecter des patterns dangereux,
  • surveiller la santé des requêtes au fil du temps.

Dans le cas de SQLite, l’approche que je trouve la plus efficace consiste à rejouer la requête avec EXPLAIN QUERY PLAN pour inspecter le plan choisi par le moteur.

C’est précisément la logique implémentée par SqliteScanBusterInterceptor.

Présentation de SqliteScanBusterInterceptor

Le rôle de SqliteScanBusterInterceptor, tel que je le conçois ici, est simple :

détecter automatiquement les requêtes SQLite lentes qui provoquent un scan, puis me les signaler dans les logs.

Le principe général est le suivant :

  1. au début d’une exécution SQL, l’intercepteur mémorise l’instant de départ,
  2. après l’exécution, il lance EXPLAIN QUERY PLAN sur la requête,
  3. il inspecte le plan pour détecter la présence de SCAN,
  4. il calcule la durée,
  5. si la requête est à la fois lente et en scan, il écrit un warning détaillé.

Je trouve que c’est une excellente stratégie, car elle évite de noyer les logs avec tous les scans, y compris ceux qui n’ont pas d’impact réel.

Le code montré utilise un seuil par défaut de 500 ms :

_threshold = threshold ?? TimeSpan.FromMilliseconds(500);

L’idée, pour moi, est donc de ne remonter que les cas réellement coûteux.

Analyse détaillée du code

Voici le code concerné :

public sealed class SqliteScanBusterInterceptor : DbCommandInterceptor

Cette classe hérite de DbCommandInterceptor, ce qui me permet d’intervenir à différents moments du cycle de vie d’une commande SQL EF Core.

1. Suivi du départ de la requête

Le code utilise un dictionnaire concurrent :

private readonly ConcurrentDictionary<Guid, long> _startTicks = new();

Ce dictionnaire mémorise, pour chaque CommandId, l’instant de départ de la requête.

La méthode TrackStart est appelée depuis :

  • ReaderExecuting
  • ReaderExecutingAsync

Cela permet de capturer le point de départ juste avant l’exécution effective.

2. Protection contre la récursion

Le code contient :

private static readonly AsyncLocal<bool> _suppress = new();

C’est un point que je trouve très important.

Comme l’intercepteur exécute lui-même une commande SQL de diagnostic via EXPLAIN QUERY PLAN, il faut absolument éviter que cette commande soit elle-même interceptée, ce qui provoquerait une récursion infinie.

Le flag _suppress évite ce problème dans le flux asynchrone courant.

3. Exclusion des commandes EXPLAIN

La méthode :

private static bool IsExplain(DbCommand cmd)
    => cmd.CommandText.TrimStart().StartsWith("EXPLAIN", StringComparison.OrdinalIgnoreCase);

permet d’ignorer explicitement les commandes déjà liées à EXPLAIN.

C’est, à mon sens, une deuxième barrière de sécurité contre les diagnostics auto-référents.

4. Mesure à la fin de l’exécution

Quand la commande est exécutée, la méthode :

ReaderExecutedAsync

appelle :

HandleExecutedAsync(command, eventData, cancellationToken);

C’est là que, concrètement, le diagnostic réel a lieu.

5. Vérification du type de connexion

Le code vérifie d’abord :

if (command.Connection is not SqliteConnection)

Je trouve cela cohérent, puisque la logique EXPLAIN QUERY PLAN ici cible SQLite.

6. Exécution de EXPLAIN QUERY PLAN

La méthode ExplainQueryPlanAsync reconstruit une commande à partir de la commande originale :

  • même connexion,
  • même transaction,
  • même timeout,
  • mêmes paramètres,
  • même SQL, préfixé par EXPLAIN QUERY PLAN.

Pour moi, c’est un bon point de conception, car le diagnostic porte sur la vraie requête, pas sur une approximation.

7. Détection du scan

Le code analyse ensuite le texte retourné :

if (explain.Contains("SCAN", StringComparison.OrdinalIgnoreCase))

Si le plan contient SCAN, la requête est considérée comme suspecte.

8. Corrélation avec la durée

Le code ne loggue pas immédiatement. Il récupère d’abord l’instant de départ, calcule la durée, puis compare avec le seuil.

Je trouve que c’est une très bonne approche, car :

  • un scan sur une minuscule table peut être acceptable,
  • un scan lent sur un chemin chaud mérite en revanche une action immédiate.

9. Log d’alerte

Le warning produit contient :

  • le SQL exécuté,
  • le plan d’exécution,
  • l’information qu’un table scan a été détecté.

Ce genre de log m’est extrêmement utile en diagnostic réel.

Exemple de message :

SQLite query with table scan detected.
 SQL: SELECT ...
 EXPLAIN QUERY PLAN:
SCAN ProductsByWarehousesHistories

10. Nettoyage en cas d’échec

Les méthodes :

  • CommandFailed
  • CommandFailedAsync

suppriment proprement la trace de départ du dictionnaire.

Cela évite, selon moi, d’accumuler un état incohérent.

Schémas Mermaid

Flux général de l’intercepteur

flowchart TD
    A[Commande SQL EF Core] --> B[ReaderExecuting ou ReaderExecutingAsync]
    B --> C[TrackStart]
    C --> D[Exécution de la requête SQLite]
    D --> E[ReaderExecutedAsync]
    E --> F[HandleExecutedAsync]
    F --> G{Connexion SQLite ?}
    G -- Non --> H[Warning technique ou sortie]
    G -- Oui --> I[EXPLAIN QUERY PLAN]
    I --> J{Le plan contient SCAN ?}
    J -- Non --> K[Fin]
    J -- Oui --> L[Calcul de la durée]
    L --> M{Durée >= seuil ?}
    M -- Non --> K
    M -- Oui --> N[LogWarning avec SQL et plan]

Vue séquentielle

sequenceDiagram
    participant App as Application
    participant EF as EF Core
    participant Interceptor as SqliteScanBusterInterceptor
    participant Db as SQLite
    participant Log as ILogger

    App->>EF: Exécute une requête LINQ / SQL
    EF->>Interceptor: ReaderExecutingAsync
    Interceptor->>Interceptor: TrackStart(CommandId)
    EF->>Db: Requête SQL réelle
    Db-->>EF: Résultat
    EF->>Interceptor: ReaderExecutedAsync
    Interceptor->>Db: EXPLAIN QUERY PLAN + requête
    Db-->>Interceptor: Plan d'exécution
    Interceptor->>Interceptor: Recherche de SCAN
    Interceptor->>Interceptor: Calcul de la durée
    alt Scan détecté et durée au-dessus du seuil
        Interceptor->>Log: LogWarning
    else Cas acceptable
        Interceptor-->>EF: Fin sans warning
    end

Différence entre scan et index

flowchart LR
    A[Requête WHERE ProductByWarehouseId = ?] --> B{Index sur ProductByWarehouseId ?}
    B -- Non --> C[SCAN complet de ProductsByWarehousesHistories]
    C --> D[Lecture de nombreuses lignes]
    D --> E[Filtrage après lecture]
    E --> F[Temps de réponse élevé]
    B -- Oui --> G[SEARCH via index]
    G --> H[Accès ciblé aux lignes utiles]
    H --> I[Moins de lecture]
    I --> J[Temps de réponse réduit]

Schéma de données simple

erDiagram
    PRODUCTS ||--o{ PRODUCTS_BY_WAREHOUSES : aggregates
    WAREHOUSES ||--o{ PRODUCTS_BY_WAREHOUSES : stores
    PRODUCTS_BY_WAREHOUSES ||--o{ PRODUCTS_BY_WAREHOUSES_HISTORIES : snapshots

    PRODUCTS {
        guid Id
        string Name
    }

    WAREHOUSES {
        guid Id
        string Name
    }

    PRODUCTS_BY_WAREHOUSES {
        guid Id
        guid ProductId
        guid WarehouseId
        decimal PhysicalStock
        decimal AverageDailyConsumption
        decimal AverageLeadTimeDays
    }

    PRODUCTS_BY_WAREHOUSES_HISTORIES {
        guid Id
        guid ProductByWarehouseId
        int DayId
        decimal PhysicalStock
        decimal AverageDailyConsumption
    }

Lire EXPLAIN QUERY PLAN

SQLite fournit EXPLAIN QUERY PLAN pour donner une vue simplifiée du plan d’exécution.

Exemple :

EXPLAIN QUERY PLAN
SELECT *
FROM ProductsByWarehousesHistories
WHERE ProductByWarehouseId = @productByWarehouseId;

Exemple défavorable

SCAN ProductsByWarehousesHistories

Quand je vois cela, je sais immédiatement que SQLite parcourt la table.

Exemple favorable

SEARCH ProductsByWarehousesHistories USING INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId (ProductByWarehouseId=?)

Quand je vois cela, je sais que SQLite utilise un index pour retrouver les lignes.

Exemple encore meilleur sur certains scénarios

SEARCH ProductsByWarehousesHistories USING COVERING INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId_DayId (ProductByWarehouseId=?)

Dans certains cas, l’index contient déjà toutes les colonnes utiles à la requête, ce qui m’évite une relecture de la table.

Exemples de requêtes lentes et de corrections par index

Exemple 1 : filtre simple sans index

Requête

SELECT *
FROM ProductsByWarehousesHistories
WHERE ProductByWarehouseId = @productByWarehouseId;

Symptôme

Sans index sur ProductByWarehouseId, SQLite peut faire un scan complet.

Correction

CREATE INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId
ON ProductsByWarehousesHistories(ProductByWarehouseId);

Effet attendu

On passe potentiellement de :

SCAN ProductsByWarehousesHistories

à :

SEARCH ProductsByWarehousesHistories USING INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId (ProductByWarehouseId=?)

Exemple 2 : filtre et tri

Requête

SELECT *
FROM ProductsByWarehousesHistories
WHERE ProductByWarehouseId = @productByWarehouseId
ORDER BY DayId DESC;

Mauvais index

CREATE INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId
ON ProductsByWarehousesHistories(ProductByWarehouseId);

Je considère que cet index aide pour le filtre, mais pas forcément assez pour le tri.

Meilleur index

CREATE INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId_DayId
ON ProductsByWarehousesHistories(ProductByWarehouseId, DayId DESC);

Pourquoi c’est mieux

À mon sens, cet index épouse mieux la structure de la requête :

  • égalité sur ProductByWarehouseId,
  • tri sur DayId DESC.

SQLite peut alors réduire le coût du tri, voire l’éviter.

Exemple 3 : filtre composite

Requête

SELECT *
FROM ProductsByWarehouses
WHERE ProductId = @productId
  AND WarehouseId = @warehouseId;

Index recommandé

CREATE INDEX IX_ProductsByWarehouses_ProductId_WarehouseId
ON ProductsByWarehouses(ProductId, WarehouseId);

Pourquoi cet index est pertinent

Il suit le pattern d’accès réel :

  • filtre exact sur ProductId,
  • filtre exact sur WarehouseId.

C’est typiquement le genre d’index que je mettrais en place pour faire disparaître un scan sur une page qui cherche la fiche de stock d’un produit dans un dépôt donné.

Exemple 4 : ordre des colonnes mal choisi

Requête

SELECT *
FROM ProductsByWarehouses
WHERE ProductId = @productId
  AND WarehouseId = @warehouseId;

Mauvais index

CREATE INDEX IX_ProductsByWarehouses_CreationDate_ProductId
ON ProductsByWarehouses(CreationDate, ProductId);

Cet index ne correspond pas bien au prédicat principal.

Meilleur index

CREATE INDEX IX_ProductsByWarehouses_ProductId_WarehouseId
ON ProductsByWarehouses(ProductId, WarehouseId);

Je considère que l’ordre des colonnes dans un index composite est crucial.

Exemple 5 : LIKE difficilement indexable

Requête

SELECT *
FROM Products
WHERE Name LIKE '%usb%';

Problème

Même avec un index sur Name, le moteur peut ne pas l’utiliser efficacement si le motif commence par %.

Conséquence

SQLite peut réaliser un scan.

Alternatives

  • préférer une recherche par préfixe quand c’est possible,
  • normaliser la recherche métier,
  • envisager SQLite FTS pour la recherche textuelle avancée.

Comment bien penser ses index avec SQLite

Avec SQLite, je ne crois pas du tout à l’idée de créer des index partout. Pour moi, il faut une vraie stratégie.

1. Partir des requêtes réelles

Quand j’optimise une base, je commence par observer les requêtes qui comptent vraiment :

  • celles exécutées souvent,
  • celles qui chargent les écrans critiques,
  • celles utilisées dans les API les plus sollicitées,
  • celles qui traitent les tables volumineuses.

2. Indexer les colonnes de filtrage fréquentes

Les colonnes souvent présentes dans les WHERE sont, pour moi, des candidates naturelles.

Exemples courants :

  • ProductId,
  • WarehouseId,
  • Status,
  • CreatedOn,
  • TenantId,
  • CategoryId.

3. Penser en termes de patterns d’accès

Je n’indexe jamais une table abstraitement. J’indexe un usage.

Par exemple, pour :

WHERE TenantId = @tenantId
  AND Status = @status
  AND CreatedOn >= @createdOn
ORDER BY CreatedOn DESC

un index possible peut être :

CREATE INDEX IX_Orders_TenantId_Status_CreatedOn
ON Orders(TenantId, Status, CreatedOn DESC);

4. Soigner l’ordre des colonnes dans l’index

L’ordre n’est pas cosmétique. À mes yeux, il conditionne l’utilité réelle de l’index.

En général, il faut placer d’abord les colonnes les plus structurantes pour le filtrage exact, puis celles utilisées pour les plages ou le tri.

5. Éviter la sur-indexation

Chaque index supplémentaire a un coût :

  • espace disque,
  • temps d’insertion,
  • temps de mise à jour,
  • temps de suppression.

J’essaie donc de viser des index utiles, pas des index partout.

6. Mesurer après chaque changement

Après ajout d’un index, je vérifie toujours :

  • le nouveau plan,
  • la disparition du scan,
  • le gain réel de performance,
  • l’absence d’effet secondaire significatif sur les écritures.

Cas concret autour de SqliteScanBusterInterceptor

Dans ce projet, le cas concret que je trouve le plus parlant consiste à afficher l’historique journalier d’un enregistrement ProductByWarehouse via l’entité ProductByWarehouseHistory, exposée dans le DbContext par ProductsByWarehousesHistories.

Une page métier peut vouloir afficher les derniers instantanés de stock pour un couple produit/dépôt.

Requête typique

SELECT *
FROM ProductsByWarehousesHistories
WHERE ProductByWarehouseId = @productByWarehouseId
ORDER BY DayId DESC;

Plan défavorable possible

SCAN ProductsByWarehousesHistories
USE TEMP B-TREE FOR ORDER BY

Cela signifie généralement deux problèmes :

  • scan large de la table,
  • tri coûteux dans une structure temporaire.

Index adapté

CREATE INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId_DayId
ON ProductsByWarehousesHistories(ProductByWarehouseId, DayId DESC);

Plan amélioré attendu

SEARCH ProductsByWarehousesHistories USING INDEX IX_ProductsByWarehousesHistories_ProductByWarehouseId_DayId (ProductByWarehouseId=?)

Dans ce scénario, SqliteScanBusterInterceptor m’aide beaucoup à faire émerger automatiquement ce type de régression.

On peut aussi avoir un deuxième cas concret sur ProductsByWarehouses lui-même, lorsqu’on cherche la ligne correspondant à un produit dans un dépôt donné :

SELECT *
FROM ProductsByWarehouses
WHERE ProductId = @productId
  AND WarehouseId = @warehouseId;

Sans index composite adapté, SQLite peut là aussi scanner inutilement une table qui grossit avec le nombre de couples produit/dépôt.

Ne pas oublier l’optimisation régulière de la base

Pour moi, détecter les scans avec SqliteScanBusterInterceptor est essentiel, mais ce n’est pas la seule action à mener. Dans ce projet, il est aussi important d’optimiser régulièrement la base SQLite, ce qui est déjà prévu via SqliteDbContextFactory.OptimizeDatabase(..).

Cette méthode exécute notamment :

PRAGMA optimize;
ANALYZE;
VACUUM;
PRAGMA quick_check;

Chaque commande a son intérêt :

  • PRAGMA optimize; aide SQLite à appliquer certaines optimisations internes,
  • ANALYZE; met à jour les statistiques utiles au query planner,
  • VACUUM; réorganise le fichier de base et récupère l’espace,
  • PRAGMA quick_check; vérifie rapidement l’intégrité logique de la base.

Autrement dit, je considère que bien indexer est indispensable, mais qu’il faut aussi entretenir régulièrement SQLite pour que le moteur continue de prendre de bonnes décisions et que le fichier de base reste sain.

La bonne approche, selon moi, est donc double :

  1. détecter les requêtes qui scannent trop avec SqliteScanBusterInterceptor,
  2. exécuter périodiquement l’optimisation de la base via SqliteDbContextFactory.OptimizeDatabase(..).

Pourquoi SqliteScanBusterInterceptor est une excellente approche

À l’usage, cet intercepteur m’apporte plusieurs bénéfices importants.

1. Il observe la réalité

Au lieu de raisonner théoriquement, j’observe la vraie requête SQL exécutée par EF Core.

2. Il détecte les problèmes tôt

Je peux repérer les scans en développement, en recette ou en préproduction, avant qu’ils deviennent des incidents visibles en production.

3. Il relie plan et durée

Ce n’est pas seulement un détecteur de SCAN. C’est un détecteur de scans réellement coûteux.

4. Il rend la dette de performance visible

Les problèmes de perf restent souvent invisibles tant qu’aucun signal automatique ne les remonte. Pour moi, l’intercepteur change vraiment cela.

5. Il impose une discipline d’indexation

Dès qu’un warning apparaît, je me pose immédiatement les questions suivantes :

  • faut-il réécrire la requête,
  • faut-il ajouter un index,
  • faut-il revoir l’ordre des colonnes dans un index existant,
  • faut-il réduire la projection,
  • faut-il limiter les données lues.

Cas où un scan peut être acceptable

J’essaie malgré tout de ne pas tomber dans le dogmatisme.

Tous les scans ne sont pas forcément dramatiques.

Un scan peut être acceptable dans certains cas :

  • table minuscule,
  • table de référence quasi statique,
  • opération rare d’administration,
  • traitement batch non sensible à la latence,
  • absence d’index réellement pertinent.

Mais sur les chemins critiques de l’application, je considère qu’un scan doit toujours rester suspect tant qu’il n’a pas été explicitement justifié.

Bonnes pratiques d’utilisation en projet

1. Activer l’intercepteur tôt

Plus je détecte les scans tôt, moins leur correction me coûte cher.

2. Surveiller les hot paths

J’accorde une attention particulière aux écrans, requêtes et API les plus utilisés.

3. Corriger le SQL ou l’index, pas seulement le symptôme

Parfois le vrai problème est :

  • un SELECT * inutile,
  • un tri mal placé,
  • un Include excessif,
  • une requête LINQ qui génère un SQL peu optimal,
  • un mauvais ordre de colonnes dans l’index.

4. Journaliser avec assez de contexte

Un bon warning doit idéalement contenir :

  • la requête SQL,
  • le plan,
  • la durée,
  • éventuellement le contexte métier si disponible.

5. Tester avec de la volumétrie réaliste

Je me méfie toujours des requêtes qui semblent excellentes sur une base de développement vide, car elles peuvent devenir catastrophiques sur des volumes réels.

6. Réévaluer régulièrement les index

Avec le temps, les usages changent. Les index doivent suivre l’évolution fonctionnelle de l’application.

7. Exécuter la maintenance SQLite régulièrement

Dans ce dépôt, je pense qu’il faut aussi lancer régulièrement la maintenance prévue dans SqliteDbContextFactory.OptimizeDatabase(..) afin de garder des statistiques à jour et une base saine. Une bonne indexation sans ANALYZE, PRAGMA optimize ou VACUUM exécutés périodiquement finit souvent par perdre une partie de son efficacité.

Mini check-list d’optimisation SQLite

Avant de considérer une requête comme saine, je vérifie :

  • les colonnes du WHERE sont-elles indexées,
  • l’ordre des colonnes de l’index correspond-il à la requête,
  • le ORDER BY peut-il être servi par l’index,
  • le plan montre-t-il SEARCH plutôt que SCAN,
  • la projection évite-t-elle les colonnes inutiles,
  • la requête a-t-elle été testée sur une volumétrie crédible,
  • l’intercepteur ne remonte plus d’alerte sur ce scénario,
  • la maintenance SQLite via SqliteDbContextFactory.OptimizeDatabase(..) est-elle exécutée régulièrement.

Retour d’expérience sur AuditStock

Dans AuditStock, mon retour d’expérience est assez clair : SQLite fonctionne très bien tant que je garde une vraie discipline sur les requêtes, les index et la maintenance de la base.

Ce que j’ai trouvé intéressant sur ce projet, c’est que les problèmes ne viennent pas forcément de requêtes extraordinairement complexes. Très souvent, la dégradation vient plutôt d’un scénario très banal : une page qui charge un historique, une recherche sur une entité agrégée, ou une lecture répétée sur une table qui grossit tranquillement au fil du temps.

Dans AuditStock, les entités comme ProductsByWarehouses et ProductsByWarehousesHistories sont de bons exemples. Au début, tout semble fluide. Puis, à mesure que l’on accumule des instantanés, de l’historique ou des couples produit/dépôt, on commence à sentir que certaines lectures deviennent plus coûteuses que prévu. C’est exactement le genre de contexte où un table scan peut rester discret pendant un moment, puis devenir pénalisant.

Ce que j’en retiens, c’est que le vrai gain ne vient pas seulement d’un bon moteur de base de données, mais du fait de rendre visibles les mauvaises décisions du query planner au bon moment. C’est pour cela que SqliteScanBusterInterceptor me paraît particulièrement utile dans AuditStock : il transforme un problème diffus de performance en signal concret, lisible et exploitable.

J’ai aussi constaté qu’il ne suffit pas d’ajouter un index une fois pour toutes en espérant que le sujet soit réglé. Sur un projet vivant comme AuditStock, les usages changent, les écrans évoluent, les volumes montent et les patterns d’accès se déplacent. Un index pertinent aujourd’hui peut devenir insuffisant demain, ou simplement ne plus être le plus adapté.

L’autre leçon importante, dans AuditStock, concerne la maintenance régulière de SQLite. Le fait d’avoir une méthode dédiée comme SqliteDbContextFactory.OptimizeDatabase(..) est, pour moi, une très bonne chose. Le triptyque PRAGMA optimize, ANALYZE et VACUUM, complété par PRAGMA quick_check, joue un rôle très concret : il aide SQLite à rester performant, à conserver des statistiques utiles et à éviter que la base ne se dégrade silencieusement avec le temps.

Si je devais résumer mon retour d’expérience sur AuditStock en une phrase, ce serait celle-ci : SQLite est excellent dans ce type de projet, à condition de le traiter comme une vraie base de données de production, avec observation, indexation réfléchie et entretien régulier.

Conclusion

Pour moi, SqliteScanBusterInterceptor matérialise une idée très saine : la performance doit être observée automatiquement, pas supposée.

Dans SQLite, je considère qu’un table scan est souvent l’un des signaux les plus dangereux pour la scalabilité réelle d’une application. Tant que les tables sont petites, le problème peut rester invisible. Mais lorsque la volumétrie augmente, l’absence d’index adaptés devient rapidement un frein majeur.

Si je devais résumer le message clé, je dirais ceci :

  • un table scan est souvent très mauvais pour la performance,
  • il faut prendre EXPLAIN QUERY PLAN au sérieux,
  • il faut concevoir les index à partir des requêtes réelles,
  • il faut utiliser des exemples métier réels, comme ProductsByWarehouses et ProductsByWarehousesHistories dans ce projet,
  • il faut corriger les scans avant qu’ils deviennent des incidents,
  • il faut entretenir régulièrement la base avec SqliteDbContextFactory.OptimizeDatabase(..),
  • et un intercepteur comme SqliteScanBusterInterceptor est une excellente manière d’industrialiser cette vigilance.

En pratique, j’estime que chaque requête importante devrait pouvoir répondre à deux questions :

  1. quel est son plan d’exécution SQLite,
  2. quels index la rendent réellement efficace.

Si le plan contient SCAN sur une requête significative, je considère qu’il faut généralement agir.

Pour moi, c’est souvent le signal clair qu’il est temps d’optimiser sérieusement les index SQLite.