Le bon PLAN d'InterBase

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

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

1. 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 :

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

Egalement veiller à :

  • élargir la page par défaut de 512 à 4096, sauf cas particuliers.
  • ne pas utiliser l'extension .GDB : c'est une extension de fichiers systèmes introduite sous Windows XP fréquemment interrogés par XP, ce qui peut produit un ralentissement. (Mais on devra cependant laisser ISC4.GDB)

1-2. Indexer les colonnes apparaissant souvent dans les conditions de sélection

  • Si le parcourt 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é.
  • Si cet ordre est quelconque, indexer plutôt chaque colonne séparément.

1-3. Ecrire correctement les ordres SQL

En particulier placer les conditions sur les fichiers joins 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 1° 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 indexs étant mis en place, la requête

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

sera avantageusement réécrite

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

1-4. Eviter SELECT * dans la mesure du possible

Ne demander que les colonnes nécessaires.

1-5. N'utiliser ORDER BY que lorsque cela est requis

Ceci sera discuté plus bas.

1-6. 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).

1-7. Utiliser les IBX

  • IBSQL en premier lieu (unidirectionnel)
  • sinon IBDataSet (ou IBQuery [+ IBUpdateSQL]).
  • Eviter IBTable

1-8. 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.

1-9. 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 indexs 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, 3 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 meta-données, mais pas les indexs.
  3. La troisième crée une nouvelle base complète à partir du fichier .gbk
 
Sélectionnez
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

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

 
Sélectionnez
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 :

 
Sélectionnez
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éta-données, mais aussi des données.

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

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 2 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. A 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. (A 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éta-donné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'indexs 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 indexs.

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

3. L'optimisation des requêtes SQL

3-1. Optimiser l'utilisation d'un index dans une clause WHERE

Prenez par exemple la requête simple suivante :

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

Le plan produit naturellement par InterBase est

 
Sélectionnez
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

3-2. 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)

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

pourra utiliser l'index, tandis que

 
Sélectionnez
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

 
Sélectionnez
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

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

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

3-3. Optimiser une requête avec des plans multiples

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

 
Sélectionnez
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 :

 
Sélectionnez
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. Etudions chacun de ces cas :

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

 
Sélectionnez
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

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

3-3-2. UNION

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

Dans

 
Sélectionnez
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).

3-4. 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,

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

ou

 
Sélectionnez
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 :

 
Sélectionnez
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 3 manières :

3-4-1. Utiliser une table au lieu de constantes

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

 
Sélectionnez
SELECT *
FROM
  EMPLOYEE_PROJECT
WHERE
  EMP_NO IN (SELECT EMP_NO FROM SELECTED_EMPLOYEES);

3-4-2. Utiliser UNION ou UNION ALL

 
Sélectionnez
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.

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

En mettant

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

le plan devient

 
Sélectionnez
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.

4. 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 indexs 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 indexs é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 meilleurs performances.

Interbase

MDA (Model Driven Architecture)

Autres articles

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

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.