UPDATE avec sous-sélection TRES lent [Résolu]

[Sankasssss]

Bonjour,

j'ai cherché à faire une requête update avec sous sélection pour remplir une colonne mais cette requête met à mal notre AS400.
Voici la requête concerné :

Code :

1
2
3
4
5
6
7
8
9
10
11
UPDATE rembtest.amuaide AS ds0 SET datefin = (
     SELECT
     max(ds1.dsas0dfde)
     FROM clilandta.dsas000 AS ds1
     WHERE
     ds0.ni = ds1.dsas0n010
     AND ds1.dsas0dec LIKE ('1%') AND ds1.dsas0eta = '3  '
     AND ds1.dsas0sera = 'SAN' AND ds1.dsas0aide LIKE '%AMU%'
     AND ds1.dsas0ddde < 20110700 AND ds1.dsas0dfde > 20110000
)
;

Quand je dis qu'elle met à mal l'AS400, c'est qu'elle utilise 60% de l'AS400 pendant très longtemps.
La table à mettre à jour ne contient que +- 2500 enregistrements, elle est composée de 6 colonnes et personne ne l'utilise à part moi.

Après plus d'une minute d’exécution, j'ai fait un select sur la table d'update pour voir le nombre de ligne déjà mise à jour :

Code :

SELECT COUNT(*) FROM rembtest.amuaide WHERE datefin > 0

J'en étais à un peu moins de 100 lignes affectées.
J'ai pu me rendre compte en actualisant plusieurs fois le résultat que la requête update affectait environs une ligne par seconde

Donc j'aimerais savoir ce que j'ai fais de mal dans ma requête qui ne me semble pas compliquée pour avoir un tel résultat

[bernard59139]

Bonjour

A priori, et avec les info que tu donnes, je dirais que c'est le sous-select qui consomme les ressources.

Sur la table clilandta.dsas000 AS ds1, je vérifierai qu'un index existe sur

• dsas0eta
• dsas0sera
• dsas0ddde
• dsas0dfde
• et sur dsas0dec

toutes les colonnes n'ont pas besoin d'être dans un index, mais cela dépend des données.

a+

[pdz74]

Bonsoir,

Le plus important c'est le prédicat de corrélation. Vérifie la présence d'un index sur ds1.dsas0n010. Sans cela, oups... Sachant que le meilleur index possible serait un index multi-colonnes avec ds1.dsas0n010 en première colonne et les autres colonnes citées par bernard59139 ensuite. Si possible, les colonnes avec prédicat d'égalité d'abord, ensuite les colonnes avec like, > ou <. Evidemment, c'est la théorie, ensuite tout dépend de la grosseur des colonnes, de leur pouvoir discriminant, ..., bref des différentes statistiques.

A contrario, si tu as plusieurs index mono-colonne sur les différentes colonnes de la requête, le risque est que DB2 fasse du multiple-index, ce qui, dans le cas présent, s'avèrerait certainement désastreux.

Je ne suis pas spécialiste as400 (perso, c'est zos), mais j'imagine que tu peux faire un explain de ta requête, cela devrait te mener vers la bonne piste.

Tiens nous au courant.

Bon week.

[bernard59139]

scromeleu, j'avais laissé de coté la colonne ds1.dsas0n010
merci pour la précision

[Sankasssss]

Tout d'abord un très grand merci pour votre intervention à tout les deux .

Voici le suivi, en effet il n'y a pas d'index sur ces colonnes.
Malheureusement il ne s'agit pas de table provenant de notre société et je ne peux y toucher.
Pour faire ce que vous me suggériez, j'ai copié la table dans une bibliothèque qui nous appartient. De la j'ai réessayer sans mettre d'index pour test et là miracle, plus aucun problème. La requête est effectuée quasi immédiatement.

Y aurait-il un problème pour faire des liaisons entre des bibliothèques différentes?

[K2R400]

Envoyé par Sankasssss

Y aurait-il un problème pour faire des liaisons entre des bibliothèques différentes?

Que veux-tu dire par là ?

Si tu ne peux pas ajouter d'index (d'ailleurs je ne vois pas pourquoi, même si cette table comme tu dis ne t'appartiens pas) vérifie que ta table ne possède pas trop d'enregistrements supprimés, ils sont quand même lus lors d'une requête. Ensuite, duplique la table QSYS/QAQQINI dans QUSRSYS et mets la valeur à *YES au lieu de *DEFAULT pour la ligne IGNORE_DERIVED_INDEX et tout va rentrer dans l'ordre.

[Sankasssss]

Ce que je veux dire c'est que lorsque j'essaye de faire un update d'une table se trouvant dans la biblio rembtest avec une sélection d'une table qui se trouve dans la biblio CLILANDTA, cela prend énormément de temps alors que quand c'est au sein de la même biblio il n'y a plus de problème...

Si tu ne peux pas ajouter d'index (d'ailleurs je ne vois pas pourquoi, même si cette table comme tu dis ne t'appartiens pas)

C'est parce que le contrat de maintenance du logiciel stipule que nous ne touchons pas à leur environnement.

vérifie que ta table ne possède pas trop d'enregistrements supprimés, ils sont quand même lus lors d'une requête.

L'application ne supprime jamais d'enregistrement dans cette table.
Par contre il est vrais qu'il y a énormément d'enregistrement.
Mais je ne comprend pas qu'en copiant la table dans l'autre biblio, et avec l'intégralité de ces lignes avec un CPYF, cela améliore grandement les performance. C'est pour cela que je me posais la question précédente :

Y aurait-il un problème pour faire des liaisons entre des bibliothèques différentes?

Je testerai dès demain le changement de IGNORE_DERIVED_INDEX dans QUSRSYS, j'espère que tu vises juste
Merci pour ton intervention

[pdz74]

Je ne connais pas AS400, donc je ne me prononcerai pas sur des soucis de bib.

Par contre, concernant le fait que tu ne doives pas toucher à du DDL livré par un presta extérieur. Toutes les sociétés achètent des logiciels à l'extérieur. Les seuls objets intouchables, ce sont les vues et les tables, cad ce qui est accédé dans les programmes. Par contre, tout le reste, tu es libre d'en faire ce que tu veux, par exemple changer le nom des databases, des tablespaces, des stogroups, des paramètres techniques, ... Et surtout, si tu te rends compte qu'il manque des index, il est super important de les créer et d'en prévenir le fournisseur : loin de mettre en doute une quelconque clause juridique d'un contrat, tu leur rendras un fier service car il y a de fortes chances pour que cet index puisse servir à d'autres clients du même fournisseur : tu le verras donc apparaitre dans la version suivante livrée par le presta. Donc, surtout n'hésite pas à intervenir sur du DDL livré de l'extérieur. Par expérience, je peux te dire que de nombreux logiciels développés sous DB2 par certaines sociétés, le sont sans l'apport de DBA et des fois, ça craint...

Faut faire vite et pas cher, la qualité, basta !!! Tu me diras, c'est également la nouvelle devise de ma société, mais ceci est un autre problème...

[K2R400]

Envoyé par Sankasssss

Mais je ne comprend pas qu'en copiant la table dans l'autre biblio, et avec l'intégralité de ces lignes avec un CPYF, cela améliore grandement les performance.

Tout simplement parce que il doit exister des index dérivés attachés à la table originale (des logiques avec des clauses Select/omit pointant sur ta table) ce qui pousse à utiliser le vieux moteur SQL appelé CQE. En faisant un CPYF, tu dupliques la table sans ces index dérivés.
En modifiant le fichier QAQQINI avec IGNORE_DERIVED_INDEX à *YES cela devrait résoudre ton problème, c'est SQE (au lieu de CQE) qui prendra la main.

Toutefois je suis à 1000% d'accord avec pdz74, il faut favoriser la création d'index quand nécessaire. Car sans index, tu finis par un scan de table.
Si tu es tiède sur le sujet, créé une nouvelle biblio et créé tes index dans cette biblio. En cas d'upgrade de version de ton progiciel, tu pourras éventuellement supprimer cette biblio.

[Sankasssss]

Merci pour votre intervention malheureusement je n'ai pas encore pu tester car cette semaine a été surchargée.
Jusqu'à aujourd'hui j’espérais avoir une minute mais ça n'a pas été le cas.

En effet il y a plein de vue sur cette table et pas d'index.
Je dois avouer que ma connaissance des bases de données se limite au requête SQL plus ou moins basique et nous n'avons pas dans notre société de DB administrateur. Nous apprenons donc sur le tas quand nous avons du temps et malheureusement la plupart du temps chez nous (comme pour cette discussion).

J'ai apprécié votre idée de créé des index dans une bibliothèque séparée.
Je testerai cela dès que possible avec la modification de IGNORE_DERIVED_INDEX.

Concernant la société, c'est un vieux logiciel (2000) et il n'y a pas de gestion d'utilisateur sur la BD, pas de fichier de journalisation, pas de trigger. Par contre il y a énormément de vue logique...

Concernant ces vue logique, je connais leurs intérêt pour y accéder via RPG mais est-ce que leurs utilisations avec des requêtes améliore les performances?

[K2R400]

Envoyé par Sankasssss

Concernant ces vue logique, je connais leurs intérêt pour y accéder via RPG mais est-ce que leurs utilisations avec des requêtes améliore les performances?

En fait ce ne sont pas des vues, mais des index réduits assez souvent (index contenant une clause WHERE traduction dans le monde AS/400 : Logiques avec des Select/Omit). iSeries Navigator les réprésentes comme une vue, mais ce sont quand même des pseudo index.
Avant de créer d'autres Index (les index sont plus performants que des logiques) focalise toi sur le IGNORE_DERIVED_INDEX.
Car si un logique "touche" ta table et qu'il contient des Select/Omit (index réduits) et que tu n'es pas en V6R1 mini, c'est le vieux, vieux, très vieux moteur CQE qui va exécuter ta requête. En modifiant la valeur IGNORE_DERIVED_INDEX le moteur ne tiendra plus compte de ces index réduits et le nouveau moteur SQE prendra la main. Ce dernier peut offrir des performances jusqu'à 30 fois supérieures.
A partir de la V6R1, la valeur par défaut de IGNORE_DERIVED_INDEX est à *YES.

[Sankasssss]

Bonjour,

profitant d'une minute de répit, j'ai essayé en changeant IGNORE_DERIVED_INDEX.

Pour se faire, j'ai copié la table QSYS/QAQQINI dans QUSRSYS et mi la valeur à *YES au lieu de *DEFAULT pour la ligne IGNORE_DERIVED_INDEX du fichier QUSRSYS/QAQQINI .

Mais il n'y a aucun changement (même requête).

Je me pose donc des questions :
- Faut-il un redémarrage serveur pour prendre en compte ce changement.
- Comment sait-il qu'il doit prendre en compte le fichier de QUSRSYS à la place QSYS ?

Pour info, je lance mes requêtes à partir d'un programme externe au session 400 (SQL View)

J'aurais voulu aussi savoir si vous aviez de la documentation sur ces fichiers de configuration 400 car j'ai fais quelque recherche sur http://publib.boulder.ibm.com/infoce...v5r4/index.jsp
mais je n'ai rien trouvé de bien formateur...

Merci

[EDIT]
P.S. : Je vais mettre des index sur le fichier dans une biblio séparée comme vous me l'avez conseillé mais je voulais d'abord voir si ceci fonctionnait...
[/EDIT]

[punkoff]

Salut,

http://publib.boulder.ibm.com/infoce...ifyqaqqini.htm

Concernant le fait de redémarer ton job je ne sais pas, mais si tu as lancé ton programme avant de dupliqué le qaqqini il y a des chances qu'il faille soit redémarer ton job soit faire un chgqrya.

Concernant la doc sur ces fichiers..... y a une formation chez IBM où ils expliquent tout ca http://www.www-304.ibm.com/jct03001c...seCode=OL40ZGB

En cherchant un peu sur internet tu devrai pouvoir tomber sur des reliquats de cours pas très officiel...

Sinon essaie de regarder les redbooks dès fois il y a en a de très complet.

[K2R400]

Si tu sais utiliser iSeries Navigator, copie/colle ta requête dedans et demande un explain :
On connaitra sur quel QAQQINI il se base, quel moteur est utilisé, quels index sont utilisés etc... et le plan d'accès.

Une petite question bête :
rembtest.amuaide et clilandta.dsas000 sont bien des tables ou des vues SQL ? et non pas des fichier logiques...

[Sankasssss]

Bonjour,

@punkoff : Merci pour le lien, j'ai beaucoup de mal à naviguer dans le site d'IBM... Concernant la formation, j'aimerais beaucoup, malheureusement je crains que se soit trop chère pour notre administration qui rogne tout les budgets

@K2R400 :
Oui il s'agit bien de table et non de fichier logique

j'ai fais un explain via iSeries Navigator via le menu viual Explain => Explication (Que je ne connaissais pas, MERCI ). Et la il me donne bien :
QUERY ENGINE USED = CQE
REASON SQE WAS NOT USED = DERIVED KEY OR SELECT/OMIT INDEX EXISTS

et sur la table principale DSAS il me conseille bien de créer des index :
Liste de colonnes de clé pour index recommandé = DSAS0DEC, DSAS0ETA, DSAS0SERA, DSAS0AIDE, DSAS0DDDE, DSAS0DFDE

Donc je vais créer des index comme vous me l'aviez si bien dit précédemment

[punkoff]

Sur votre explain plan, quand vous sélectionnez "sélection finale" vous avez à droite plein de description.

En particulier un label nommé "Options INI" avec dedans : "Bibliothèque d'option des requêtes".
Cette valeur est bien positionnée à "QUSRSYS" ?

[Sankasssss]

J'ai donc un dessin avec :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Mise à jours
      ^
      |
Sélection
^        ^
|          -------------------------
|                                      |
Scannage de TABLE           Fusion de sous-requête
                                       ^
                                       |
                                    Groupage
                                       ^
                                        |
                                     Scannage de TABLE

et quand je clique sur "Mise à jours", dans le panel de droite, sous "Information relatives à l'environnement pour l'instruction" j'ai :
Table d'options de requête = QAQQINI
Bibliothèque d'options de requête = *N

Merci pour vos interventions rapide

[punkoff]

bizarre,

Dans les options de votre fenetre "Execution de requete SQL", allez dans options => Modification des attributs de requête => et coché la case de "Utilisation du fichier d'options de requête situé dans le schéma suivant" => QUSRSYS

Et retenter.

Quand vous avez copié votre fichier QAQQINI, l'avez-vous bien fait via un CRTDUPOBJ ? Avez vous des droits de lecture dessus ?

[Sankasssss]

Non je n'ai pas fais la copie avec CRTDUPOBJ mais avec CPYF
Cela peut-il être la cause?

[Sankasssss]

J'ai enlevé le fichier et je l'ai reconstruis avec CRTDUPOBJ et maintenant les performances sont au rendez-vous et il utilise bien SQE.

Un très grand merci à vous pour vos précieuses interventions.