SQL : Gros problème de lenteur sur un UPDATE

SQL : Gros problème de lenteur sur un UPDATE - SQL/NoSQL - Programmation

Marsh Posté le 18-01-2006 à 10:34:36    

Bonjour, j'ai une requête "UPDATE" qui tape dans deux tables moyennement volumineuses, et qui met un temps fou (plus de 15 minutes) pour mettre à jour à peine une dizaine de lignes...
 
SGBD : Oracle 8.1.7
 
Structure des tables impactées :
 
PRO :
Table des produits
24424 lignes (3214 avec CODSOC = 2)
Champs :
CODSOC : code société
CODPRO : code produit
...
CODZN15 : champ libre varchar2, contenant une date au format "YYYYMMDD" ou " " si vide (un espace)
 
clé primaire : CODSOC, CODPRO
index que je viens de créer : (pas très utile, parceque un count(*) pour CODSOC = 2 and CODZN15 = ' ' retournait 37 en 0,6 secondes)

Code :
  1. CREATE INDEX WI_PROZN15 ON PRO
  2. (CODSOC, CODZN15)
  3. LOGGING
  4. TABLESPACE IDXGNX1
  5. PCTFREE    10
  6. INITRANS   2
  7. MAXTRANS   255
  8. STORAGE    (
  9.             INITIAL          24K
  10.             NEXT             192K
  11.             MINEXTENTS       1
  12.             MAXEXTENTS       120
  13.             PCTINCREASE      10
  14.             FREELISTS        1
  15.             FREELIST GROUPS  1
  16.             BUFFER_POOL      DEFAULT
  17.            )
  18. NOPARALLEL;


 
EVE :
Table des évènements
187570 lignes (186327 avec CODSOC = 2)
Champs :
CODSOC : code société
ACHVTE : Achat/Vente (flag)
TYPEVE : Type d'évènement (commande, devis, livraison, etc.)
NUMEVE : Numéro d'évènement
...
DATEVE : Date de l'évènement
CODETA : Statut de l'évènement
 
clé primaire : CODSOC, ACHVTE, TYPEVE, NUMEVE
 
EVP :
Table des postes des évènements (lignes)
494172 lignes (484530 avec CODSOC = 2)
Champs :
CODSOC : code société
ACHVTE : Achat/Vente (flag)
TYPEVE : Type d'évènement (commande, devis, livraison, etc.)
NUMEVE : Numéro d'évènement
NUMPOS : Numéro de ligne du poste
...
CODPRO : Code produit
clé primaire : CODSOC, ACHVTE, TYPEVE, NUMEVE, NUMPOS
index utile : CODSOC, ACHVTE, TYPEVE, NUMEVE, CODPRO
 
EVL :
Table des sous-postes des évènements (ventillation datées)
498399 lignes (488706 avec CODSOC = 2)
Champs :
CODSOC : code société
ACHVTE : Achat/Vente (flag)
TYPEVE : Type d'évènement (commande, devis, livraison, etc.)
NUMEVE : Numéro d'évènement
NUMPOS : Numéro de ligne du poste
NUMLIG : Numéro de ligne du sous-poste
...
CODPRO : Code produit
clé primaire : CODSOC, ACHVTE, TYPEVE, NUMEVE, NUMPOS, NUMLIG
index utile : CODSOC, ACHVTE, TYPEVE, NUMEVE, CODPRO
 
 
Ce que doit faire la requête :
 
Mettre à jour le champ CODZN15 de PRO avec la date de la première commande passée sur ce produit
Critères :
CODSOC = 2 (société qui utilise ce champ)
ACHVTE = 'V' (vente)
TYPEVE = 'CDV' (Commande de vente)
CODETA = 'V', 'S', 'I' (Validé, Soldé ou Incident -c'est pas parcequ'on a un problème avec la commande que le produit n'a pas été commandé-)
CODZN15 = ' ' (Si le produit a déjà été commandé avant, ça sert à rien de retrouver la date)
 
La requête originale :

Code :
  1. update pro set codzn15= (select
  2.             nvl(min(eve.dateve),' ')
  3.          from
  4.      evl,eve
  5.    where
  6.    eve.codsoc=2
  7.   and     eve.achvte='V'
  8.   and     eve.typeve='CDV'
  9.   and     (eve.codeta='V' or eve.codeta='S' or eve.codeta='I')
  10.   --
  11.   and evl.codsoc=eve.codsoc
  12.   and evl.achvte=eve.achvte
  13.   and evl.typeve=eve.typeve
  14.   and evl.numeve=eve.numeve
  15.   and evl.codpro=pro.codpro)
  16. where
  17.  codsoc=2 
  18. and  codzn15=' ';


A priori, passer par EVL ne sert à rien, puisque :
1) Un cadencement d'un poste ne peux pas porter sur un produit différent que le poste dont il est issu
2) La société ne fait de toute façon pas de cadancement
3) On récupère la date de la commande, pas celle du cadancement
4) EVL est plus volumineuse que EVP sans apporter plus d'infos dans notre cas
 
J'ai donc modifié la requête :

Code :
  1. update pro set codzn15= (select
  2.             nvl(min(eve.dateve),' ')
  3.          from
  4.      evp,eve
  5.    where
  6.    eve.codsoc=2
  7.   and     eve.achvte='V'
  8.   and     eve.typeve='CDV'
  9.   and     (eve.codeta='V' or eve.codeta='S' or eve.codeta='I')
  10.   --
  11.   and evp.codsoc=eve.codsoc
  12.   and evp.achvte=eve.achvte
  13.   and evp.typeve=eve.typeve
  14.   and evp.numeve=eve.numeve
  15.   and evp.codpro=pro.codpro)
  16. where
  17.  codsoc=2 
  18. and  codzn15=' ';


 
En gros, avec création de l'index et changement de la requête, je suis passé de...
Un temps interminable qui se termine par un rollback à... là même chose.
 
Pourtant, y'a que 37 lignes à mettre à jour !
 
Dans le doute, l'autre jour j'ai profité de la nuit pour rebooter le server, il n'y a donc pas de lock dans la base.
L'outil qui utilise la base, pourtant lourdement consommateur en requêtes, travaille très vite, donc ce n'est pas non plus un problème de performances du serveur.
 
Et là je sèche un peu, je sais pas trop quoi faire pour améliorer la chose.
Je pourrais rajouter une clause WHERE dans ma requête pour ne chercher la date de première commande que pour les produits qui ont été commandés, mais vu qu'il n'y a en tout que 37 lignes (et ça fait une semaine que ça n'a pas tourné !) je doute que ça change quoi que ce soit... :/

Reply

Marsh Posté le 18-01-2006 à 10:34:36   

Reply

Marsh Posté le 18-01-2006 à 11:00:07    

Comprends pas...
 

Code :
  1. select count(*)
  2. from eve
  3. where eve.codsoc = 2
  4. and eve.achvte = 'V'
  5. and eve.typeve = 'CDV'
  6. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  7. => 56799 en 1 seconde
  9. select count(*)
  10. from evp, eve
  11. where eve.codsoc = 2
  12. and eve.achvte = 'V'
  13. and eve.typeve = 'CDV'
  14. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  15. and evp.codsoc = eve.codsoc
  16. and evp.achvte = eve.achvte
  17. and evp.typeve = eve.typeve
  18. and evp.numeve = eve.numeve
  19. => 145897 en 5 secondes
  21. select count(*)
  22. from pro, evp, eve
  23. where eve.codsoc = 2
  24. and eve.achvte = 'V'
  25. and eve.typeve = 'CDV'
  26. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  27. and evp.codsoc = eve.codsoc
  28. and evp.achvte = eve.achvte
  29. and evp.typeve = eve.typeve
  30. and evp.numeve = eve.numeve
  31. and pro.codsoc = evp.codsoc
  32. and pro.codpro = evp.codpro
  33. => 145897 en 5 secondes
  35. select count(*)
  36. from pro, evp, eve
  37. where eve.codsoc = 2
  38. and eve.achvte = 'V'
  39. and eve.typeve = 'CDV'
  40. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  41. and evp.codsoc = eve.codsoc
  42. and evp.achvte = eve.achvte
  43. and evp.typeve = eve.typeve
  44. and evp.numeve = eve.numeve
  45. and pro.codsoc = evp.codsoc
  46. and pro.codpro = evp.codpro
  47. and pro.codzn15 = ' '
  48. => 1 en 5 secondes
  50. select pro.codpro, min(eve.dateve)
  51. from pro, evp, eve
  52. where eve.codsoc = 2
  53. and eve.achvte = 'V'
  54. and eve.typeve = 'CDV'
  55. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  56. and evp.codsoc = eve.codsoc
  57. and evp.achvte = eve.achvte
  58. and evp.typeve = eve.typeve
  59. and evp.numeve = eve.numeve
  60. and pro.codsoc = evp.codsoc
  61. and pro.codpro = evp.codpro
  62. group by pro.codpro
  63. => 2008 lignes en 6 secondes
  65. select pro.codpro, min(eve.dateve)
  66. from pro, evp, eve
  67. where eve.codsoc = 2
  68. and eve.achvte = 'V'
  69. and eve.typeve = 'CDV'
  70. and (eve.codeta = 'V' or eve.codeta = 'S' or eve.codeta = 'I')
  71. and evp.codsoc = eve.codsoc
  72. and evp.achvte = eve.achvte
  73. and evp.typeve = eve.typeve
  74. and evp.numeve = eve.numeve
  75. and pro.codsoc = evp.codsoc
  76. and pro.codpro = evp.codpro
  77. and pro.codzn15 = ' '
  78. group by pro.codpro
  79. => 1 ligne en 5 secondes


 
Pourtant, l'update dure 1 heure et 6 minutes d'après le log de la procédure (qui ne fait QUE cette requête !)
J'ai jamais eu le courrage d'attendre la fin dans TOAD pour vérifier, mais ça dépasse les 30 minutes.

Reply

Marsh Posté le 18-01-2006 à 11:30:46    

J'ai relancé un test d'update, ça marche pas mieu... J'attends depuis 30 minutes que TOAD me rende la main pour coller la requête que j'ai testé :D

Reply

Marsh Posté le 18-01-2006 à 11:50:31    

y'a du mieux :bounce:
 
45 minutes ! :D
 

Code :
  1. update pro set codzn15=    (select
  2.                                nvl(min(eve.dateve),' ')
  3.                          from
  4.                         evp,eve
  5.                    where
  6.                       eve.codsoc=2
  7.                   and     eve.achvte='V'
  8.                   and     eve.typeve='CDV'
  9.                   and     eve.codeta in ('V', 'S', 'I')
  10.                   --
  11.                   and    evp.codsoc=eve.codsoc
  12.                   and    evp.achvte=eve.achvte
  13.                   and    evp.typeve=eve.typeve
  14.                   and    evp.numeve=eve.numeve
  15.                   and    evp.codpro=pro.codpro)
  16.       where
  17.            codsoc=2 
  18.       and     codzn15=' '
  19.   and exists (select null from evp, eve where eve.codsoc = 2 and eve.achvte = 'V' and eve.typeve = 'CDV' and eve.codeta in ('V', 'S', 'I')
  20.            and evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve and evp.codpro = pro.codpro);


 
Comprends pas :sweat:

Reply

Marsh Posté le 18-01-2006 à 12:11:25    

Code :
  1. select * from pro
  2.       where
  3.            codsoc=2 
  4.       and     codzn15=' '
  5.   and exists (select null from evp, eve where eve.codsoc = 2 and eve.achvte = 'V' and eve.typeve = 'CDV' and eve.codeta in ('V', 'S', 'I')
  6.            and evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve and evp.codpro = pro.codpro);


 
14 minutes... Comprends pas.

Reply

Marsh Posté le 18-01-2006 à 13:47:28    

t'as essayé de faire ça en PL/SQL plutôt ?
 
BEGIN
  FOR r IN (ton SELECT avec toutes les conditions pour filtrer un maximum)
  LOOP
    UPDATE ...
  END LOOP;
END;
 
D'ailleurs, t'avais pas trouvé une syntaxe plus sympa pour les update, genre UPDATE (SELECT ...) SET ... WHERE EXISTS (...) ?

Reply

Marsh Posté le 18-01-2006 à 13:54:10    

cf mon avant-dernier post pour la syntaxe "optimisée"
 
c'est clair, je divise le temps par deux... m'enfin bon :/
 
sinon, je préfère ne pas utiliser de PL à cet endroit, afin d'éviter de faire une transaction qui risque de durer longtemps (et foutre le bronx dans la base pendant ce temps).
 
tant pis, je vais laisser la requête telle quelle en attendant d'avoir une révélation :spamafote:
 
elle se lance à 20h30 le soir. Elle emmerde les américains pendant toute la soirée, et après elle a jusqu'à 4h du matin pour tourner, heure à laquelle le serveur stoppe Oracle et l'appli pour faire un backup à froid. Au départ elle était programmée à 3H et du coup à chaquefois elle terminait pas :D
 
chose étonnant cependant : avant, c'était super rapide, et du jour au lendemain, "pouf" !
 
pourtant, ni la requête ni la structure de la base n'a changé ! par contre  les données, oui. étrange...

Reply

Marsh Posté le 18-01-2006 à 14:12:57    

La base est en RULE ou COST-BASED ?
Peut-être des stats pas à jour ...
 
Donne-nous les EXPLAIN PLANs également ... si les stats sont à la rue, ça doit faire des table scans non voulus :D
 
PS: la solution en PL/SQL devrait prendre quelques secondes puisque ton SELECT sur les 3 tables impliquées met ce temps là !

Reply

Marsh Posté le 18-01-2006 à 14:28:28    

Tu peux me filer la structure des tables pour le explain plan ? J'ai pas le MDP system, et du coup je ne peux pas utiliser les tables existantes.
 
Sinon, à priori, vu les résultats des select, mes stats ont l'air à jour... :/
 
PS : Ouais, d'accord avec la vitesse du PL. Mais vu que l'update était rapide aussi et qu'il s'est mis d'un coup à mettre des plombes, je ne peux pas partir du principe qu'il va tout le temps durer pas longtemps... Deplus, c'est un bon gros *.BAT des familles, qui lance la requête, et du coup je veux limiter les risques d'erreurs et mélanges de langage (pas pratique de faire de l'injection SQL depuis un fichier .BAT), sinon je vais jamais m'en sortir.
Et pour des raisons de standardisation, je ne veux pas stocker le PL dans la base.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed