Le bon PLAN d'InterBase

Henry Cesbron Lavau

Pour ceux qui utilisent déjà InterBase, cet article démonte le fonctionnement des requêtes d'InterBase et indique comment en améliorer l'efficacité.

- Titre : Le bon PLAN d'InterBase

- Auteur : Henry Cesbron Lavau

- Parution : 9 octobre 2002

- Mise à jour : 27 avril 2020

- Licence : Copyright ® 2002 Henry Cesbron Lavau. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. 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

- 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

- II. Comment InterBase optimise un ordre SQL

- III. L'optimisation des requêtes SQL

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

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

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

- III-C-1. Sous-requêtes corrélées

- III-C-2. UNION

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

- III-D-1. Utiliser une table au lieu de constantes

- III-D-2. Utiliser UNION ou UNION ALL

- III-D-3. Forcer InterBase à faire une lecture séquentielle

- IV. Conclusion

I - Actions préalables

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

Voyez http://community.borland.com/article/interbase/makeibscream.pdf ou l'article de Sylvain James.

Également, veiller à :

- élargir la page par défaut de 512 à 4096, sauf cas particulier ;

- ne pas utiliser l'extension .GDB : c'est une extension de fichiers système introduite sous Windows XP fréquemment interrogés par XP, ce qui peut produire un ralentissement. (Mais on devra cependant laisser ISC4.GDB.)

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

- Si le parcours doit être souvent fait dans les deux sens, indexer également en DESCENDING. La nécessité du double indexage est due à la compression des préfixes (voir plus bas).

- Si une clé porte sur plusieurs colonnes, utiliser un index multiple selon l'ordre des colonnes de la clé le plus souvent utilisée.

- Si cet ordre est quelconque, indexer plutôt chaque colonne séparément.

I-C - Écrire correctement les ordres SQL

En particulier placer les conditions sur les fichiers joints dans la clause JOIN (et non dans la clause WHERE).

Exemple : on souhaite connaître les clients de la base ayant fait l'objet d'une facturation depuis le 1er janvier 2002.

La première action à effectuer pour avoir de bonnes performances est de mettre le numéro de client numclient en index dans factures.numclient (clients.numclient est un index dans clients dès lors qu'il s'agit d'une clé primaire. En réalité, dans un dessin correct de base, il y a dans toute table une clé primaire qui n'est jamais vue et une clé qui est vue : dans ce cas, clients.numclient doit aussi avoir été mis explicitement mis en index dès lors qu'il n'est pas la clé primaire). factures.datefacture est aussi un index.

Les index étant mis en place, la requête :

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

sera avantageusement réécrite

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

Ne demander que les colonnes nécessaires.

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

Ceci sera discuté plus bas.

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

IB 6.0 et antérieure renvoient en effet la longueur maximale d'un VarChar, indépendamment de la longueur utilisée (mais les Blobs ne peuvent être manipulés dans les procédures stockées et les triggers).

I-G - Utiliser les IBX

- IBSQL en premier lieu (unidirectionnel).

- Sinon IBDataSet (ou IBQuery [+ IBUpdateSQL]).

- Éviter IBTable.

I-H - Utiliser des VIEW

Ceci évite la phase de transcription, car les énoncés des vues sont stockés sous la forme BLR (voir ci-dessous).

Mais le plan reste recalculé à chaque fois, car il est optimisé en fonction des données.

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

Par exemple, après le backup de la base (qui peut être fait pendant que des utilisateurs travaillent), faites une restauration de la base (qui nécessite que les utilisateurs soient déconnectés). Ceci aura l'avantage de reconstruire tous les index et d'en rééquilibrer les b?arbres (arbres binaires : le rééquilibrage permet de réduire le nombre d'interrogations internes sur un index pour trouver la bonne donnée).

Pour effectuer ces opérations, voici par exemple, trois commandes batch faisant appel aux utilitaires gfix et gbak livrés avec InterBase :

1. La première commande vérifie l'état de la base et répare d'éventuels problèmes ;

2. La deuxième commande crée un fichier .gbk qui contient les données et les métadonnées, mais pas les index ;

3. La troisième crée une nouvelle base complète à partir du fichier .gbk :

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

II - Comment InterBase optimise un ordre SQL

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 - L'optimisation des requêtes SQL

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

Prenez par exemple la requête simple suivante :

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

Le plan produit naturellement par InterBase est

PLAN (E NATURAL)

Un moyen simple de connaître le plan utilisé par InterBase est de lancer la requête dans IBConsole et de cliquer sur l'onglet Plan.

InterBase lira chaque rangée de la table pour ne retourner que celles qui satisfont la clause WHERE.

Dans la suite de la discussion, même si nous faisons appel à la base de données EMPLOYEE pour des raisons de simplicité, nous nous plaçons en fait dans le cas où la table contient au moins 100 000 lignes (et non moins de 100 dans la base fournie avec InterBase).

Si un index de la base ne se trouve pas dans le plan utilisé par InterBase, vérifiez :

- dans le cas d'un index composé, que WHERE fait appel à toutes les colonnes de l'index (ou au n premières colonnes) ;

- que l'index est suffisamment sélectif. Sinon, InterBase préférera un plan NATURAL.

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

Comme InterBase utilise la compression des préfixes (si AAAAAA est suivi de AAAABB, InterBase stocke quelque chose comme AAAAAA et 4BB) :

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

pourra utiliser l'index, tandis que

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

n'utilisera pas l'index.

Ceci est vrai aussi dans tous les cas d'utilisation d'une requête paramétrée :

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

ne pourra pas utiliser l'index même si le paramètre LAST_ NAME vaut 'St%'.

Dans ce cas, on a intérêt à utiliser STARTING_WITH, car InterBase saura alors utiliser l'index

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

à noter que CONTAINING ne peut jamais utiliser d'index pour les mêmes raisons.

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

Prenons l'exemple suivant, construit pour les besoins de la démonstration :

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

Le plan produit par InterBase est :

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

On remarque que l'on a deux plans : un pour la sous-requête et un pour la requête principale. En effet, les sous-requêtes et UNION feront intervenir plusieurs plans. Étudions chacun de ces cas.

III-C-1 - Sous-requêtes corrélées

Dans la mesure du possible, évitez les sous-requêtes corrélées. En effet, une sous-requête corrélée est réexécutée pour chaque rangée de la requête principale.

Pour éviter de vous trouver dans cette situation, faites plutôt appel à un JOIN. Ceci aura pour effet de n'exécuter la requête qu'une seule fois au lieu d'une fois par rangée de la requête principale.

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

sera beaucoup plus efficace, car le plan devient

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

III-C-2 - UNION

On aura évidemment au moins deux plans. La seule indication à retenir est d'utiliser si possible UNION ALL au lieu de UNION. En effet, UNION élimine les lignes en double, ce qui oblige à trier chaque requête. L'appel à UNION ALL peut considérablement améliorer les performances.

Dans

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

on remplacera avantageusement UNION par UNION ALL, avec le même résultat puisqu'un employé ne peut se trouver que dans l'une des deux requêtes (ou il en a, ou il n'en a pas).

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

InterBase n'est pas efficace dans le cas où, dans une sous-requête, une colonne indexée fait appel à une liste de constantes : il traitera la sous-requête comme si elle était corrélée, alors qu'elle ne l'est pas.

Par exemple,

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

ou

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

donneront toutes deux le plan :

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

Après vous être assuré que le problème de lenteur vient de la présence d'une liste (en comparant la vitesse à celle d'une requête avec l'égalité à un seul item), vous pouvez contourner la difficulté de trois manières.

III-D-1 - Utiliser une table au lieu de constantes

En créant une table SELECTED_EMPLOYEES qui contient le nom des deux employés 'Nelson' et 'Young', on résout le problème.

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

III-D-2 - Utiliser UNION ou UNION ALL

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

sera beaucoup plus rapide, notamment sur une base volumineuse.

III-D-3 - Forcer InterBase à faire une lecture séquentielle

En mettant

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

le plan devient

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

car, du fait de la concaténation, LAST_NAME n'est plus reconnu comme index. C'est un de ces cas contre-intuitifs où l'utilisation d'un index ralentirait l'exécution de la requête. Mais ici, ce n'est efficace que si la table de la sous-requête est de petite taille. Mieux vaut donc utiliser l'une des deux méthodes précédentes.

IV - Conclusion

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