Le bon PLAN d'InterBase

Avant d'améliorer les performances d'InterBase par les plans, il faut obtenir les meilleures performances par quelques actions préalables.

I-A. Installer correctement InterBase et tirer le meilleur parti de son architecture▲

I-B. Indexer les colonnes apparaissant souvent dans les conditions de sélection▲

I-C. Écrire correctement les ordres SQL▲

SELECT DISTINCT *
FROM clients
INNER JOIN factures ON (factures.numclient = clients.numclient)
WHERE (factures.datefacture > '1/1/2002')

SELECT DISTINCT *
FROM clients
INNER JOIN factures ON (factures.numclient = clients.numclient)
  AND (factures.datefacture > '1/1/2002')

I-D. Éviter SELECT * dans la mesure du possible▲

I-E. N'utiliser ORDER BY que lorsque cela est requis▲

I-F. Utiliser un BLOB plutôt qu'un VarChar(32000)▲

I-G. Utiliser les IBX▲

I-H. Utiliser des VIEW▲

I-I. Faire de la maintenance régulière▲

echo Réparation de la base ...
gfix -m -i -user SYSDBA -password masterkey C:\Program~1\IBData.gdb
echo Extraction des données ...
gbak -g -b -z -v -l -user SYSDBA -password masterkey C:\Program~1\IBData.gdb C:\Program~1\IBData.gbk
echo Régénération automatique des données ...
gbak -c -v -z -r -user SYSDBA -password masterkey C:\Program~1\IBData.gbk C:\Program~1\IBData.gdb

Avant de vouloir améliorer InterBase en lui disant ce qu'il doit faire, il est essentiel de comprendre ce qu'il fait spontanément.

La requête SQL est d'abord condensée par InterBase sous une représentation interne appelée BLR (Binary Language Representation de taille maximum 48 Ko).

Ensuite InterBase estime le moyen le plus rapide pour la traiter : par l'utilisation d'index et la recherche de la séquence optimale d'interrogation. C'est ce qu'on appelle la préparation du plan.

Le plan peut être (partiellement) contrôlé par l'instruction SQL PLAN qui fait partie de l'instruction SELECT comme indiqué dans la documentation SQL fournie avec InterBase dans le fichier LangRef.pdf :

SELECT ... [TRANSACTION ...]
[DISTINCT | ALL]
[INTO ...]
FROM ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...]
[UNION ...]
[PLAN <plan_expr>]
[ORDER BY ...]
[FOR UPDATE ...]

<plan_expr> = [JOIN | [SORT] [MERGE]] ({<plan_item> | <plan_expr>} [, <plan_item> | <plan_expr>} ...])
<plan_item> = {table | alias} {NATURAL | INDEX (<index> [, <index> ...]) | ORDER <index>}

Exemple :

SELECT * FROM clients PLAN clients ORDER clients_1 ORDER BY clients

Cependant, il est recommandé de ne pas utiliser habituellement de plans explicites : cela peut paraître curieux dans un article sur les plans. En effet, InterBase sait choisir le plan optimal en fonction de métadonnées, mais aussi des données.

Sont prises en compte la taille des tables et la sélectivité des colonnes et des index.

Par exemple :

• a colonne qui est une clé primaire possède une sélectivité maximale : chaque rangée est clairement identifiée par la clé ;
• en revanche, la colonne Sexe qui ne peut prendre que deux valeurs possède une sélectivité très faible.

Avant de vouloir regarder les rares cas où vous devriez indiquer un plan, notez les précautions suivantes pour développer efficacement votre application Client/Serveur :

• faites votre développement avec une base remplie de données réelles : vous aurez une meilleure idée des performances ;
• développez avec la base de données sur un serveur et vous sur un poste client : vous prendrez en compte le temps de réponse du réseau.

Pour mieux comprendre comment optimiser le processus de requête, voyons maintenant les étapes que parcourt InterBase :

1. La requête est transcrite en BLR ;
2. À partir du BLR, InterBase optimise la requête. L'optimisation est toujours faite au moment de la préparation ;
3. La requête est exécutée : le serveur lit les rangées et exécute les opérations requises ;
4. Le client rapatrie (Fetch) les rangées. (Le serveur n'envoie rien tant que le client ne le demande) ;
5. Au cours de l'exécution de la requête sur le client, les gestionnaires d'événements s'exécutent.

La transcription et l'optimisation (étapes 1 et 2) sont généralement très rapides.

Donc la première chose à faire lorsqu'une requête ne démarre pas rapidement est de la tester en dehors du contexte de l'application, par exemple dans le SQL Monitor d'IBConsole. Si la rapidité revient, c'est qu'il y a un problème dans l'application et pas dans InterBase. (À noter que la préparation peut occasionnellement prendre du temps du fait que le travail effectué sur le serveur nécessite le blocage des métadonnées).

Dans cette comparaison entre l'exécution dans l'application et l'exécution hors de l'application, faites attention à comparer des situations identiques : souvent les moniteurs SQL ne font remonter que les rangées visibles et non leur totalité (FetchAll).

Il faut bien distinguer le temps d'exécution de la requête et le temps de rapatriement des enregistrements : par exemple, exécuter une requête avec ORDER BY est plus rapide, mais en rapatrier les données est plus lent, alors qu'une requête sans ORDER BY sera plus lente à être exécutée, mais le rapatriement sera plus rapide, car les rangées seront lues dans l'ordre où elles sont stockées physiquement sur le disque. Si bien qu'on peut avoir intérêt à exécuter la requête sans ORDER BY et à établir l'ordre de présentation des rangées sur le poste client. Un outil comme InterBase PLANalyzer (cité en haut) permet de faire la différence entre ces deux temps.

Si la requête produit un nombre important de rangées, demandez-vous si le client a besoin de toutes ces rangées immédiatement. Restreignez la requête (généralement à l'aide des clauses WHERE et JOIN) au strict minimum nécessaire.

Prenez en compte le nombre de rangées qu'InterBase doit lire : par exemple une clause WHERE dont les rangées ne comportent pas d'index oblige InterBase à lire l'ensemble de la base pour en extraire les rangées satisfaisant la clause WHERE.

Là encore, InterBase PLANalyzer (cité en haut) vous indique le nombre de rangées qu'InterBase devra lire pour exécuter la requête.

Vous pouvez donc avoir avantage à créer des index.

Notez enfin qu'InterBase n'utilisera un index que s'il offre une sélectivité suffisante (voir plus haut).

La phase 3 se déroule :

• selon l'ordre naturel : PLAN (EMPLOYEE NATURAL) ;
• ou selon l'ordre d'un index : PLAN (EMPLOYEE INDEX (NAMEX,RDB$PRIMARY7)) qui optimise un WHERE ou un JOIN ;
• ou PLAN (EMPLOYEE ORDER NAMEX) qui optimise un ORDER BY.

InterBase stocke chaque index, ici NAMEX et RDB$PRIMARY7 sous forme d'un bitmap (au sens de tableau de bits) puis effectue un AND booléen entre les deux bitmaps.

Dans le cas où un index (NAMEX par exemple) serait utilisé pour WHERE ou JOIN d'une part, et ORDER BY d'autre part, c'est ORDER BY qui l'emporte.

Nous verrons plus bas quand il y a lieu d'intervenir sur cette phase par une commande PLAN explicite.

Lors de la phase 4, si votre réseau ralentit le rapatriement des données, vous pouvez envisager les actions suivantes :

1. Faites un upgrade vers la version 6.5 d'InterBase : le protocole de transmission a été optimisé ;
2. Envoyez des données compressées ;
3. Cachez sur le poste client les tables qui ne changent pas (notamment certaines tables LookUp).

La phase 5 se déroule sur le client, et vous avez la maîtrise de ce que vous avez programmé dans le gestionnaire d'événements.

III-A. Optimiser l'utilisation d'un index dans une clause WHERE▲

SELECT *
FROM
  EMPLOYEE E
WHERE
  E.PHONE_EXT = :PhoneExt;

PLAN (E NATURAL)

III-B. Optimiser LIKE, STARTING WITH, et CONTAINING▲

SELECT
  EMP_NO, FIRST_NAME, LAST_NAME
FROM
  EMPLOYEE
WHERE
  LAST_ NAME LIKE 'St%';

SELECT
  EMP_NO, FIRST_NAME, LAST_NAME
FROM
  EMPLOYEE
WHERE
  LAST_ NAME LIKE '%t%';

SELECT
  EMP_NO, FIRST_NAME, LAST_NAME
FROM
  EMPLOYEE
WHERE
  LAST_ NAME LIKE :LastName;

SELECT
  EMP_NO, FIRST_NAME, LAST_NAME
FROM
  EMPLOYEE
WHERE
  LAST_ NAME STARTING WITH :LastName;

III-C. Optimiser une requête avec des plans multiples▲

SELECT *
FROM
  EMPLOYEE E
WHERE
  E.PHONE_EXT = (SELECT E2.DEPT_NO
                 FROM
                   EMPLOYEE E2
                 WHERE
                   E2.EMP_NO = E.DEPT_NO);

PLAN (E2 INDEX (RDB$PRIMARY7))
PLAN (E NATURAL)

SELECT *
FROM
  EMPLOYEE E
  INNER JOIN EMPLOYEE E2
    ON (E2.DEPT_NO = E.PHONE_EXT) AND (E2.EMP_NO = E.DEPT_NO)

PLAN JOIN (E2 NATURAL,E INDEX (RDB$FOREIGN8))

SELECT FULL_NAME, PHONE_EXT
FROM
  EMPLOYEE
WHERE PHONE_EXT IS NOT NULL
UNION
SELECT FULL_NAME, CAST('Sans' AS VARCHAR(4))
FROM
  EMPLOYEE
WHERE PHONE_EXT IS NULL

III-D. Listes de constantes et sous-requêtes▲

SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO IN (SELECT EMP_NO
             FROM
               EMPLOYEE
             WHERE
               LAST_NAME IN ('Nelson', 'Young'));

SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO IN (SELECT EMP_NO
             FROM
               EMPLOYEE
             WHERE
               (LAST_NAME = 'Nelson') OR (LAST_NAME = 'Young'));

PLAN (EMPLOYEE INDEX (RDB$PRIMARY7,NAMEX,NAMEX))
PLAN (EMPLOYEE_PROJECT NATURAL)

SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO IN (SELECT EMP_NO FROM SELECTED_EMPLOYEES);

SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO = (SELECT EMP_NO FROM EMPLOYEE
            WHERE LAST_NAME = 'Nelson')
UNION ALL
SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO = (SELECT EMP_NO FROM EMPLOYEE
            WHERE LAST_NAME = 'Young');

SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO IN (SELECT EMP_NO FROM EMPLOYEE
             WHERE LAST_NAME || '' IN ('Nelson', 'Young'));

PLAN (EMPLOYEE INDEX (RDB$PRIMARY7))
PLAN (EMPLOYEE_PROJECT NATURAL)

En conclusion, on retiendra que l'on a généralement intérêt à laisser InterBase générer lui-même le plan d'exécution d'une requête.

Indiquer un PLAN contraint InterBase à un seul mode d'exécution. De plus, la commande PLAN fait appel aux noms des index générés par InterBase (RDB$PRIMARY74 par exemple). La recréation de la base peut faire varier ce nom. Ceci dit, il est des cas où un PLAN imposé peut améliorer les performances : il est alors souhaitable de vérifier l'efficacité du PLAN imposé après reconstruction complète de la base. De plus, lorsque la sélectivité des index évolue, il faudra surveiller le gain de performance de la requête par rapport à celle qu'InterBase effectue sans PLAN.

En étudiant pourquoi il vaut mieux laisser InterBase établir son propre plan, cet article vous aura montré diverses techniques permettant d'obtenir de vos requêtes les meilleures performances.

Interbase

• Ma première base InterBase
• Delphi 6 et InterBase 6
• dbExpress avec Delphi 6
• Delphi 6 Relationnel
• Le bon PLAN d'InterBase

MDA (Model Driven Architecture)

• MDA
• Programmez avec les diagrammes de Delphi 7
• Les objets métiers avec ModelMaker de Delphi 7
• Les Design Patterns avec ModelMaker de Delphi 7

Autres articles

• Les transactions
• Mise à jour de Delphi 6