Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1586 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGBD/SQL] MàJ Utilisation CASE et Auto Join

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[SGBD/SQL] MàJ Utilisation CASE et Auto Join

n°2231147
Linoa1fleu​r
Posté le 18-06-2014 à 10:29:07  profilanswer
 

Bonjour  :hello:  
 
J'utilise mysql via workbench pour effectuer des calculs sur des bdd plutôt lourdes.  
Généralement, tout fonctionne mais là je bloque...  [:devoircivique:4]  
 
Je dois faire tourner une procédure stockée qui s'appuie sur les données de la table X (14 millions de lignes, 1,2Go), calcule 1 champ et recopie les champs de la table X + le champ calculé dans la table Y.
 
J'ai testé la procédure sur une extraction de 200 lignes, et ça tourne en quelques secondes (6 sec). Sauf que quand je la lance sur la table principale, ça n'en finit pas... Au bout de 60k secondes, je l'ai stoppée...
J'ai l'impression que ça ne tourne pas au final.  
 
Une idée de comment optimiser le process ? laisser tourner plus longtemps pourrait fonctionner à votre avis ?
 
 :jap:

Message cité 1 fois
Message édité par Linoa1fleur le 19-06-2014 à 11:01:43
mood
Publicité
Posté le 18-06-2014 à 10:29:07  profilanswer
 

n°2231156
Yonel
Monde de merde !
Posté le 18-06-2014 à 10:51:15  profilanswer
 

Linoa1fleur a écrit :

Bonjour  :hello:  
 
J'utilise mysql via workbench pour effectuer des calculs sur des bdd plutôt lourdes.  
Généralement, tout fonctionne mais là je bloque...  [:devoircivique:4]  
 
Je dois faire tourner une procédure stockée qui s'appuie sur les données de la table X (14 millions de lignes, 1,2Go), calcule 1 champ et recopie les champs de la table X + le champ calculé dans la table Y.
 
J'ai testé la procédure sur une extraction de 200 lignes, et ça tourne en quelques secondes (6 sec). Sauf que quand je la lance sur la table principale, ça n'en finit pas... Au bout de 60k secondes, je l'ai stoppée...
J'ai l'impression que ça ne tourne pas au final.  
 
Une idée de comment optimiser le process ? laisser tourner plus longtemps pourrait fonctionner à votre avis ?
 
 :jap:


 
Mmh vu les statistiques sur un petit échantillon (6 secondes pour 200 lignes !) pas étonnant que ça mette des plombes à s'exécuter sur des millions. Essaye avec une règle de trois pour voir, tu vas être étonné du temps théorique je pense...
 
A mon avis ça sent la procédure pas optimisée du tout. Tu utilises une logique de set (pas de ligne à ligne) ? Tu as des indexes en place ?

n°2231157
Yonel
Monde de merde !
Posté le 18-06-2014 à 10:55:52  profilanswer
 

Quand je fais la règle de trois :
200 lignes en 6 secondes
donc :
420 000 secondes pour 14 millions de lignes ! Ca fait pratiquement 5 jours...

n°2231159
Yonel
Monde de merde !
Posté le 18-06-2014 à 11:01:42  profilanswer
 

A mon avis le problème de ta procédure stockée vient d'un algorithme ligne par ligne alors qu'on attendrait typiquement ce genre de logique :
INSERT INTO Y
SELECT FROM X
 
Je pense que tu es dans le cas : "To perform an insert using the values clause" sur cette page http://wiki.lessthandot.com/index. [...] Avoid_Them

n°2231160
Linoa1fleu​r
Posté le 18-06-2014 à 11:12:22  profilanswer
 

Yes, j'ai pas osé faire une règle de 3 parce que je me doutais que ça faisait peur + je pensais pas que c'était linéaire...
 
Effectivement, le lien paraît totalement approprié  :jap: .  
Ma procédure :
fetch  
if  
set  
else
set  
set  
end if
set  
insert into  
 
Faut que je prenne le temps de comprendre le wiki...

n°2231162
Yonel
Monde de merde !
Posté le 18-06-2014 à 11:19:36  profilanswer
 

Oui donc effectivement tu as un souci dans la logique de ton algorithme qui est trop coûteuse.
 
Jette un oeil au wiki, tu es exactement dans la situation décrite. Cette page est une mine d'or pour se débarrasser des curseurs inutiles. On devrait la faire lire à tous les étudiants en informatique !
 
Une fois que tu auras ton nouvel algo, il faudra peut-être aussi ajouter quelques indexes. Parce qu'une table de 14 millions de lignes risque d'en avoir besoin.
 
Essaye d'avoir les 200 lignes qui tournent en 0 seconde déjà.

n°2231165
Linoa1fleu​r
Posté le 18-06-2014 à 11:31:20  profilanswer
 

Yonel a écrit :

Oui donc effectivement tu as un souci dans la logique de ton algorithme qui est trop coûteuse.
 
Jette un oeil au wiki, tu es exactement dans la situation décrite. Cette page est une mine d'or pour se débarrasser des curseurs inutiles. On devrait la faire lire à tous les étudiants en informatique !
 
Une fois que tu auras ton nouvel algo, il faudra peut-être aussi ajouter quelques indexes. Parce qu'une table de 14 millions de lignes risque d'en avoir besoin.
Essaye d'avoir les 200 lignes qui tournent en 0 seconde déjà.


 
Oui ça a l'air d'être la même situation, sauf que je ne comprends même pas les exemples  [:syr01]  
 
Le problème est que je n'ai jamais fait d'informatique, j'ai pompé cette procédure à un collègue et l'ai réadapté en essayant tant bien que mal de la comprendre... [:leuen:1]  
 
Pour les index, pas de souci, il y en a déjà (tous les champs avec jointure sont indexés).  
 
Bon, je vais envoyer le lien de l'article à mon chef et voir ce qu'il en dit ! Merci Yonel  :)
 
Edit : bon j'ai à peu près compris.
 
Sauf que j'ai une condition if else et je ne sais pas comment la maintenir sans le curseur => quand je change de numéro client, il faut remettre à zéro le compteur.

Message cité 1 fois
Message édité par Linoa1fleur le 18-06-2014 à 11:38:44
n°2231167
Yonel
Monde de merde !
Posté le 18-06-2014 à 11:49:38  profilanswer
 

Linoa1fleur a écrit :


 
Edit : bon j'ai à peu près compris.
 
Sauf que j'ai une condition if else et je ne sais pas comment la maintenir sans le curseur => quand je change de numéro client, il faut remettre à zéro le compteur.


 
Pour cette situation en particulier il faudrait :
1) Faire un auto-join sur la table et récupérer la ligne précédente
2) Utiliser l'instruction CASE qui te permettra de gérer le cas où le numéro de client de la table est différent du numéro de client précédent (grâce à l'auto-join en 1.)
 
Ce sera sans doute du chinois si t'as jamais fait d'informatique, mais quelqu'un qui a déjà fait du SQL devrait comprendre.

n°2231168
Linoa1fleu​r
Posté le 18-06-2014 à 11:51:55  profilanswer
 

Yonel a écrit :


 
Pour cette situation en particulier il faudrait :
1) Faire un auto-join sur la table et récupérer la ligne précédente
2) Utiliser l'instruction CASE qui te permettra de gérer le cas où le numéro de client de la table est différent du numéro de client précédent (grâce à l'auto-join en 1.)
 
Ce sera sans doute du chinois si t'as jamais fait d'informatique, mais quelqu'un qui a déjà fait du SQL devrait comprendre.


 
 :jap:  
 
Je vais faire quelques recherches pour comprendre ton chinois  :o

n°2231322
Linoa1fleu​r
Posté le 19-06-2014 à 10:04:25  profilanswer
 

Bonjour,
 
J'ai fait quelques recherches sur le CASE que je comprends maintenant. Mais il me manque l'auto join que je n'arrive pas à saisir.  
Voici la structure de ma table (merci de ne pas quoter) :
 

Spoiler :


Champ1 bigint
Champ2 float
Champ3 float
Champ4 float (le champ à calculer)
 
Champ1 / Champ2 / Champ3 / Champ4
1 / a / x / k=x
1 / b / x / l=k-b
1 / c / x / m=l-c
1 / d / x / n=m-d
1 / e / x / o=n-e
4 / f / y / p=y
4 / g / y / q=p-g
9 / h / z / r=z
9 / i / z / s=r-i
9 / j / z / t=s-j


 
De ce que j'ai compris, la requête donnerait (sans l'auto join) :
 

Code :
  1. UPDATE
  2. SET champ4= CASE
  3. WHEN champ1= champ1 ligne au dessus THEN champ4 ligne au dessus - champ2
  4. ELSE champ3
  5. FROM matable


 
Est-ce que j'ai bon sur la logique ?
Comment mettre une auto jointure dans ce cas ?  
 
Merci beaucoup  :)

mood
Publicité
Posté le 19-06-2014 à 10:04:25  profilanswer
 

n°2231416
gpl73
Posté le 20-06-2014 à 13:58:55  profilanswer
 

Si j'ai bien compris ton soucis de auto jointure...
je ne sais pas si ta BD va aimer mais tu as un truc dans le genre...
 
tu as une table y
 ID   F1  C2  
 10   a    1  
 11   b    2  
 12   c    3  
 13   d    4  
 14   e    5  
 15   e    6  
 16   e    7  
et tu fais l'update suivant :
ici j'ai simplifié : je prends un ID pour avoir la ligne précédente... tu as juste à adapter à ton cas...,  ainsi que le calcul aussi...)
C'est l'esprit qui compte :)
 
update y as a                                                        
set C2 =case when a.f1 = (select b.f1 from y as b                    
where a.id = b.id+1) then (select c.c2 from y as c where a.id =      
c.id+1) + a.c2 else a.c2 *-1 end    
                                 
tu obtiens donc :
ID   F1  C2  
10   a    1-
11   b    2-
12   c    3-
13   d    4-
14   e    5-
15   e   11  
16   e   13
 
Le sql c'est "simple" il faut écrire ce que tu as besoin :pt1cable:


---------------
mieux vaut être un con au chaud, qu'un con gelé lol
n°2231472
Linoa1fleu​r
Posté le 20-06-2014 à 18:18:39  profilanswer
 

Merci pour ton aide.  
 
Je regarde ton code (et j'essaye de comprendre  :o ) et je vois si ça marche  :jap:

n°2231601
Yonel
Monde de merde !
Posté le 23-06-2014 à 10:54:23  profilanswer
 


 
L'idée est pas mal, mais je pense que ça fonctionnera pas tellement bien. Parce qu'en fait ça ne fonctionne que si les Id se suivent, sont uniques, et que tu n'as pas de "trou". Ce qui est très rare dans la pratique.
 
Sur les dernière versions de SQL Server/Oracle, tu as la fonction LAG qui te permet d'accéder directement à la ligne précédente de ta table (et LEAD pour accéder à la ligne suivante). http://msdn.microsoft.com/fr-fr/library/hh231256.aspx
 
Sous MySQL, pas de bol ça n'existe pas. Donc il faut le simuler.
Linoa1fleur, essaye de jeter un oeil à ces pages :
http://stackoverflow.com/questions [...] n-in-mysql
http://stackoverflow.com/questions [...] n-in-mysql
http://forums.mysql.com/read.php?61,279277,279659
 
Mais il faut être un peu "calé" pour comprendre ce genre de logique en SQL, tu as pas des gens dans ta boite qui font du SQL un peu avancé (pas à base de curseurs mais en gardant une logique de set) ?
 
Par ailleurs pourquoi tu fais un UPDATE ? Je croyais que tu essayais de faire un INSERT dans Y ?


Message édité par Yonel le 23-06-2014 à 11:00:53
n°2231611
Linoa1fleu​r
Posté le 23-06-2014 à 12:26:58  profilanswer
 

Je suis pas revenue mettre à jour mon avancement. En fait, j'ai fait un simple test (au cas où...) : faire tourner la procédure de départ sur ma table avec un autre ordinateur.  
Résultat = 45min... Soit mon ordi a un souci, soit ma config de workbench n'est pas la bonne.  
 
Bon du coup je vais m'en contenter pour cette fois. J'ai bien compris par contre que le curseur n'est pas l'idéal. Et j'irais voir tes liens Yonel, quand j'aurais un peu de temps pour pousser un peu.  
 
Merci à vous 2 pour votre aide !

n°2231631
gpl73
Posté le 23-06-2014 à 21:11:51  profilanswer
 

#yonel, je fais un update suite au post de linoa à 10:25...
Concenant l'ID, c'était pour pas me "compliquer" la vie...
C'est l'idée qui contait...
tu as aussi je crois aussi la fonction ou le statment fetch one row only...
à voir...
Il y a des posts ici qui traitent ceci... j'y ai meme participé... mais je ne l'ai pas retrouvé lol


Message édité par gpl73 le 23-06-2014 à 21:15:57

---------------
mieux vaut être un con au chaud, qu'un con gelé lol

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGBD/SQL] MàJ Utilisation CASE et Auto Join

 

Sujets relatifs
Erreur install SQL 2008 R2[VB6] Problème de lien d'une base SQL Server vers Access
SQL et les Update multi lignesMoyen le plus rapide de copier une grosse table SQL en mémoire ?
Procédure Stocké & PHPBase SQL et VB Excel
Interraction Bdd Sql et utilisateurProblème PHP/SQL
[PL SQL] Aide sur requetePassage de paramètre entre procédure stockée et script Php
Plus de sujets relatifs à : [SGBD/SQL] MàJ Utilisation CASE et Auto Join


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR