Brusque ralentissement requête SQL

Bonjour,
Dans un de nos vieux programmes, il y a deux requêtes SQL lancées automatiquement le 1er du mois depuis 10 ans, qui prenaient approximativement (en fonction des sociétés traitées) 1 à 5 secondes pour s'exécuter.
Brusquement, cette nuit, ces requêtes se sont mises à tourner à une vitesse anormale, le programme passant à près de 20mn pour une seule société.
Le système (V5R4) est à jour de ses PTF, aucune n'a été appliquée depuis le mois dernier. Il n'y a aucun incident hardware. Ni les fichiers, ni les programmes n'ont changé depuis longtemps.
Alors j'y perds mon latin. Je ne comprends pas ce qui arrive, et surtout pourquoi ces requêtes s'exécutent aussi lentement. J'aurais surement meilleur temps de traiter çà en RPG pur, tellement c'est lent !
Alors, si quelqu'un a une idée, ou mieux une explication, ce serait le pied. ;-)
Exemple d'une des deux requêtes :

Code:

---

1 2 3 4 5

Update articl set arcmmo = (select round(sum(stquan/12), 0) from stagen where strefe = arrefe and ((staafa * 100) + stmmfa) > :LimBasse) where arrefe in (select strefe from stagen where ((staafa * 100) + stmmfa) > :LimBasse)

---

Bonjour.

A première vue et en se basant sur ce que tu dis à propos du hard, surtout quant à l'état des disques, je penserais à un problème de volumétrie des tables concernées ou plus généralement sur les disques, (autres tables, spool, etc...)
J'essayerais de faire un RGZPFM sur les tables ARTICL et STAGEN avec KEYFILE(*FILE), puis un RCLDBXREF ou carrément un RCLSTG.
Penser à vérifier l'occupation des disques et au %ASP.

Merci pour la réponse.

L'ASP est loin d'être surchargé, il est à 24% d'occupation.
Je ne l'ai pas précisé, mais j'ai déjà effectué un RGZPFM sur les fichiers, au cas où. Sans résultat.
Le RCLDBXREF ne renvoie aucune erreur. J'envisage un RCLSTG.

Toutefois, j'ai l'impression que le problème est ailleurs. Après des tests, je pense que c'est simplement l'évolution de la base de donnée qui a entrainé ce problème. Je vais remonter les fichiers du mois dernier sur une bibliothèque de test, et lancer la requête sur cette bibliothèque. Si tout se passe bien, j'aurais confirmation que c'est l'apport de nouvelles données (même si peu important), qui a contribué à cette dégradation.
Car je soupçonne que c'est l'optimiseur de requête qui se prend les pieds dans le tapis. En clair, jusqu'à présent il utilisait les bons chemins d'accès parce que la proportion/nombre d'enregs, étaient dans les bonnes fourchettes. Là, avec les nouveaux enregs ajoutés, il doit arriver à une conclusion différente, et prendre une autre option, qui s'avère catastrophique.
Je vais donc tenter de modifier la requête, et il faudrait, mais le temps me manque car il y a de nombreux programmes associés, que je modifie le fichier STAGEN pour utiliser des dates au lieu (le fichier est très ancien) de zones (année, mois, jour).


Edit :
Les tests sur les sauvegardes du mois dernier montrent une différence monumentale de temps de traitement, malgré un simple ajout de 1000 enregistrements au fichier STAGEN (total 113.000, croisés à 41.000 enregs dans le fichier ARTICL d'origine).
Après modification de la requête (sélection sur les zones STAAFA et STMMFA de manière individuelle et non dans un calcul), les temps sont redevenus normaux, le programme s'est exécutée en à peine 2 secondes...
Je m'étonne quand même que l'optimiseur ait pu utiliser une méthode d'accès si gourmande (10 bonnes minutes à 75% de CPU), en lieu et place d'une méthode réellement ultra-rapide. Même en accès RPG pur j'aurais été bien plus vite que cette requête SQL. C'est pourtant hyper efficace sur les traitement de masse d'habitude...
Bref, si quelqu'un a une bonne explication, et/ou des conseils à me fournir, je suis preneur. Je vais attendre 1 jour ou 2, et je mettrai le sujet en résolu.

Bonjour,

Pour comprendre en détail, il faudrait avoir les plans d’exécution de la requête longue qui a duré 20min ainsi que celle qui a duré 2s pour comparer les mécanismes utilisés par l'optimiseur.

Par contre, de façon générale, pour aider l'optimiseur à utiliser des index il faut éviter les sélections sur valeurs calculées, c'est à dire préférer cette syntaxe :

Code:

---

1 2 3 4 5 6

Update articl set arcmmo = (select round(sum(stquan/12), 0) from stagen where strefe = arrefe and (staafa > :LimBasseA or (staafa = :LimBasseA and stmmfa > :LimBasseM)) where arrefe in (select strefe from stagen where (staafa > :LimBasseA or (staafa = :LimBasseA and stmmfa > :LimBasseM))

---

Ou, si nécessaire, créer un index dérivé avec (staafa * 100) + stmmfa.

Dans tous les cas, seule le plan d’exécution pourra fournir les informations utiles : index utilisés, index recommandés ...

Bonjour,

Pour avoir un vision de ce que propose l'optimiseur, tu peux utiliser Visual Explain qui te montre la stratégie d'accès utilisée. Tu peux même avoir :

• Les étapes couteuses, en CPU, en nombre d'enregistrements traités...
• Le moteur SQL utilisé (SQE ou CQE, à vérifier dans ton cas)
• Des préconisations sur les index à créer (et les créer automatiquement)
• Des préconisations sur les données statistiques qu'il faudrait générer

Tu peux juste demander l'explicitation, sans l'exécution, c'est à dire ne pas retourner les enregistrements mais avoir les données de l'optimiseur.
En utilisant Visual Explain sur les différentes requêtes que tu évoques, tu devrais avoir une idée de ce qui se passe, tout du moins si le problème vient de SQL (il peut aussi y avoir d'autres causes : verrouillages, par exemple !).
Pour ceux qui ne connaissent pas Visual Explain
En deux mots, il s'agit d'une fonction intégrée à System i Navigator (logiciel non facturable, à installer avec toutes ses options à partir d'IBM i Access for Windows). Dans la partie Base de données, il existe un éditeur de script SQL qui permet de taper des requêtes et de les exécuter. Attention au séparateur Bibliothèque/objet (on dirait plutôt Schéma/Table en SQL) qui est le . à la place de notre traditionnel /.
Un bouton Visual Explain permet de disposer, en graphique, de toutes les données concernant cette requête.
Ci dessous l'illustration d'une simple requête : SELECT * FROM qsys2.syscolumns.
Pièce jointe 157763
Pièce jointe 157764

OK, on arrive donc à la même conclusion.
J'ai décidé de passer du temps à modifier le fichier pour passer sur une zone date unique. Je vais y perdre une bonne journée avec tous les programmes à adapter, mais au moins j'aurais plus de facilités à traiter tout çà dans le futur, et les programmes seront plus clairs.
Merci.

Edit : Je n'avais pas pensé à Visual Explain, c'est un tort de ma part. Je vais désormais tester mes requêtes avec cet outil, pour les optimiser.