Accès par clé SQL d’un fichier non trié

ses chemins d’accès.
Pour ces tests, j’ai ajouté une clause Order By MasterId à  la déclaration du curseur
en figure 1 (souvenez-vous que MasterId est la clé primaire du fichier). L’optimiseur
a déterminé que le plan d’accès le plus efficace pour cette requête était un tri
ODP (Open Data Path).

En bref, il s’agit de lire tout le fichier et de le trier dans un fichier temporaire
qui sera ensuite lu dans l’ordre d’arrivée pour transmettre les enregistrements
à  l’application. L’optimiseur a déterminé le plan d’accès le plus efficaceLe débit
maximum obtenu pour ce test a été de 29.850 enregistrements par minute en utilisant
un Fetch multilignes. Un Fetch monoligne n’en a traité que 22.780. J’ai mesuré
le temps nécessaire pour l’instruction Open Cursor et pour la boucle Fetch séparément,
et constaté que le Open Cursor (qui fait le tri et produit le fichier temporaire)
a pris environ 135 secondes dans tous les cas.
Rien d’étonnant à  cela puisque tout le fichier est trié indépendamment de la manière
dont les enregistrements sont atteints dans le programme, et aucun des paramètres
NbrRcds ou SeqOnly n’influence ce type d’accès. Ce qui était inattendu, ce sont
les performances similaires de la boucle Fetch elle-même lorsque la taille de
Fetch est passée de 2 à  1.408. Le débit de Fetch (sans compter le temps Open Cursor)
n’a varié que d’environ 83.000 à  90.000 enregistrements par minute, sans relation
étroite avec la taille de Fetch. Une taille de Fetch de 1 a produit un débit de
Fetch de 62.000 enregistrements par minute, nettement plus lent que des tailles
de Fetch plus grandes, indiquant l’intérêt d’utiliser une taille de Fetch d’au
moins 2.

Comme les tests RPG se sont exécutés plus rapidement en utilisant un chemin d’accès
par clé, je me suis demandé comment SQL se comporterait avec un accès par clé
au lieu d’un tri ODP. Bien qu’avec SQL on ne puisse pas définir explicitement
un chemin d’accès par clé pour ce type d’accès, on peut forcer indirectement l’optimiseur
de requête à  utiliser un chemin d’accès par clé. L’astuce consiste à  ajouter une
clause Optimize For n Rows à  la déclaration de curseur, où n est un petit nombre
par rapport à  la taille du fichier. J’ai utilisé la déclaration de curseur de
la figure 7.

Figure 7 Déclaration de curseur SQL pour accès séquentiel avec clé en utilisant un chemin d'accès par clé

Declare MasterTable Cursor
     With hold
      For Select *
                 From          Master
                 Order By    MasterId
                 For             Fetch Only
                 Optimize    For 1000 Rows

Figure 9 Déclaration du curseur SQL pour l'accès par clé direct

Declare MasterTable Cursor
     With hold
      For Select *
                 From    Master
                 Where  MasterId = :SlcMasterId
                 For       Fetch Only

Je m’attendais à  des résultats légèrement inférieurs à  ceux des résultats RPG
de la figure 6, pensant que le process d’I/O sous-jacent serait similaire. Mais
les résultats contenaient plusieurs surprises. Quand j’ai utilisé les pools de
mémoire partagée *Calc, les performances n’étaient pas constantes : des tests
identiques successifs ont donné des résultats allant de 12.000 à  25.000 enregistrements
par minute. Les résultats n’ont pas non plus suivi la taille de Fetch multiligne
(même si tous les tests de Fetch multiligne ont été plus rapides qu’avec un Fetch
monoligne). Cette irrégularité s’explique probablement par le fait qu’un Fetch
multiligne entraîne un blocage du programme qui, nous l’avons vu, produisait des
résultats inconstants avec les tests RPG dans les mêmes conditions. Toutefois,
les performances étaient nettement plus lentes qu’avec l’accès par clé RPG parce
qu’une très petite taille de transfert était utilisée (environ 5 ou 6 Ko par rapport
aux transferts de 49 Ko utilisés dans les tests RPG les plus rapides).

J’ai soumis le sujet de la taille de transfert aux développeurs UDB DB2 de Rochester
et ils ont produit un ensemble de PTF (qui devrait être bientôt disponible ; attendez-vous
à  SF60167, SF60168, SF60169 et SF60170 pour le produit 5769SS1) qui qualifiaient
la valeur de NbrRcds pour certains types d’accès SQL, dont l’accès séquentiel
en utilisant un chemin d’accès par clé (Avant ces PTF, toutes les requêtes SQL
ignoraient le paramètre NbrRcds.) Après avoir installé ces PTF, j’ai pu augmenter
la taille de transfert en indiquant une valeur NbrRcds. Avec NbrRcds(176), j’ai
obtenu des performances de 42.400 enregistrements par minute en utilisant un Fetch
multiligne d’une ligne, et 33.313 enregistrements par minute en utilisant un Fetch
monoligne.

Quand j’ai utilisé un Fetch multiligne de plus d’une ligne, la taille de transfert
résultante n’était pas constante pour la même valeur de NbrRcds ; ainsi, dans
des tests répétés utilisant NbrRcds(88), j’ai observé des tailles de transfert
allant de 42 Ko à  107 Ko. Pis encore, le nombre de demandes de transfert n’était
pas constant d’une exécution à  l’autre (allant d’environ 1.500 à  84.000. Par conséquent,
les performances ont elles aussi grandement varié) d’environ 6.700 à  plus de 116.000
enregistrements par minute. Il semble que ce soit une autre manifestation d’inconstances
similaires constatées avec des benchmarks RPG comparables lorsque le blocage des
enregistrements du programme était utilisé avec les pools de mémoire *Calc.

D’après ces résultats, quand on utilise SQL I/O et qu’on dispose d’une mémoire
suffisante, il vaut mieux utiliser une clause Optimize qui impose l’utilisation
d’un chemin d’accès par clé au lieu d’un tri ODP. Mais, avant de recourir à  cette
technique, il faut avoir installé les nouveaux PTF de base de données mentionnés
précédemment. Si vous exécutez des pools de mémoire *Calc, il faut utiliser un
Fetch multiligne qui extrait une ligne unique. Ensuite, définissez une commande
OvrDbf avec un paramètre NbrRcds résultant dans une taille de transfert de 32
Ko au moins.

Avec les pools de mémoire *Fixed, je n’ai constaté aucune inconstance des performances,
et en utilisant les nouveaux PTF, un paramètre NbrRcds(176) et un Fetch multiligne
pour 1.408 lignes, j’ai pu dépasser 117.000 enregistrements par minute, approchant
ainsi les meilleures performances RPG avec des pools de mémoire *Fixed. Je suggère
donc que si l’on a un très grand fichier non trié à  traiter séquentiellement par
clé et si on peut l’exécuter quand le système n’est pas très sollicité, il vaut
mieux régler les pools de mémoire sur *Fixed afin de pouvoir obtenir le débit
bien supérieur qui est possible.