[SQL] Ecriture / Optimisation SQL [Résolu]

[jerome_pdv2]

Hello,

je souhaite faire une proc SQL un peu complexe.

J'ai un fichier contenant une variable identifiant ID (qui n'est pas unique) correspondant à une "version" d'un même objet mais légèrement différente contenu dans la variable TEXTE.
J’ai une fonction de similarité « similarite(TEXTE1,TEXTE2) » qui renvoie un nombre décimal de 0 à 1.

Je met ci-dessous ce que j’aimerais faire en une seule proc SQL , je pense que c’est faisable mais je ne sais pas par quel bout commencer

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
 
/*** calcul du produit cartésien et évaluation de la fonction de similarité***/
proc sql;
CREATE TABLE identifie 
AS SELECT tab1.ID AS ID1,tab1.TEXTE AS TEXTE1,
tab2.ID AS ID2,tab2.TEXTE AS TEXTE2,
similarite(tab1.TEXTE,tab2.TEXTE) AS simil
FROM Table1 AS tab1,Table2 AS tab2
/*where similarite(tab1.TEXTE,tab2.TEXTE)>0.5*/
;
quit;
 
/*** classe par id et similitude décroissante***/
proc sort DATA=identifie ; BY ID1 descending simil ;run ;
 
/*** récupère la similarité des versions différentes pour un même ID ***/
DATA resultat1; SET identifie;
IF ID1=ID2 AND TEXTE1^=TEXTE2;run;
 
/*** récupère la ligne correspondant à TEXTE2+ID2 ayant le plus fort indice de similarité vis à vis de ID1+TEXTE1  avec ID1 différent de ID2 **/
 
DATA resultat2; SET identifie (WHERE=(ID1^=ID2));
BY ID1 descending simil;
IF first.simil;
run;
 
/*** concatène les deux tables***/
DATA resultat; SET resultat1 resultat2;run;

La raison pour laquelle je veux faire tout en une seule proc SQL, c’est que le code ci-dessus me fait passer par une table monstrueusement grosse (plusieurs milliards de croisement pour la table identifie) alors que j’en ai a peine 100 ou 200 000 à récupérer et qui me seront vraiment utiles.
Déjà que le temps de calcul est important (environ 14h, les fichiers Table1 et Table2 sont les copies d’un même fichier d’environ 60 000 lignes ) mais il est sûr que je ne peux stocker la table "identifie" quelque part, je n’ai pas suffisamment de mémoire disque (elle ferait plus de 100 Go…).
D’ou la ligne que j’ai mis en remarque « where similarite(tab1.TEXTE,tab2.TEXTE)>0.5 » où je sélectionnais que les combinaisons avec une forte liaison (>0.5), cependant en faisant ça je n’ai pas exactement la table idéale dont je souhaiterais disposer et qui est décrite dans le code au dessus, car je récupérais quelques 10 000 000 de croisement, mais certains ID on une variable TEXTE si différente des autres qu'aucune variable TEXTE des autres ID avait un indice de similarité >0.5 à la variable TEXTE d'Id1 et du coup je perdais le max(simil) par rapport aux autres Id vu que ce max était inférieur à 0,5.

Si vous avez une idée pour tout mettre ça dans une seule SQL de sorte à ce que cela passe avec une occupation acceptable du DD , le résultat attendu peut éventuellement être réduit à la seule table résultat2, la table résultat1 étant très rapide à calculer à part, bon si je peux tout avoir dans la même SQL je suis tout de même preneur bien sûr.

[sasadm]

Bonjour jerome_pdv2?

Ma solution ne fera pas de miracle mais (si elle n'explose pas les ressources de la machine) réglera ton souci d'espace disque.

On se débarasse déjà de resultat1 en faisant un join classique (et rapide).
Puis on crée resultat2 en une seule procédure qui risque de durer...

j'ai fait un petit jeu de donnée pour m'assurer de la validité de la syntaxe :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
DATA table1;
input ID TEXTE;
cards;
1 2
1 3
2 9
2 18
;
run;
 
 
DATA table2;
input ID TEXTE;
cards;
1 4
1 9
2 8
2 7
;
run;
 
/* resultat1 */
proc sql;
CREATE TABLE resultat1 
AS
SELECT tab1.ID AS ID1 
		, tab1.TEXTE AS TEXTE1
		, tab2.ID AS ID2
		, tab2.TEXTE AS TEXTE2
		, 1/(1+abs(tab1.TEXTE-tab2.TEXTE))  AS simil 
FROM Table1 AS tab1 JOIN Table2 AS tab2
ON   tab1.ID= tab2.ID AND tab1.TEXTE ^= tab2.TEXTE ;
;
quit;
 
 
/* resultat2 */
 
proc sql;
CREATE TABLE resultat2 
AS SELECT tab1.ID AS ID1,tab1.TEXTE AS TEXTE1,
tab2.ID AS ID2,tab2.TEXTE AS TEXTE2,
1/(1+abs(tab1.TEXTE-tab2.TEXTE)) AS simil
FROM Table1 AS tab1,Table2 AS tab2
WHERE tab1.ID NE tab2.ID
GROUP BY tab1.ID
HAVING  calculated simil=max(calculated simil)
;
quit;

[jerome_pdv2]

Rapide ! Merci !

La table "resultat2" se construit bien comme je le souhaite c'est parfait, ça va m'éviter le débordement du disque dur... !

Par contre pas très rapide le code

En fait c'est essentiellement la fonction "similarite" qui est longue à calculer et qui fait perdre du temps CPU, et de toute façon il reste nécessaire de l'évaluer au cours du calcul sur la totalité des croisements.

Par rapport au code que j'avais (mais qui ne faisait pas exactement ce que je voulais mais presque) le code de resultat2 va être de l'ordre de 50% moins rapide, je vais arriver aux 20h de calcul, mais c'est le prix de l'exactitude !

Comme il ne devra passer qu'une fois par an tout au plus.... (une fois que c'est calculé, c'est calculé...), ce n'est donc pas bien grave, de plus je lance ça sur 3 cessions SAS indépendantes les unes des autres, et sur 1/3 du premier fichier à chaque fois, ce qui veut dire que j'aurais mon résultat demain matin.

Parfait ! Et encore merci !

[jerome_pdv2]

hm hm

Un "OUT OF RESSOURCE" a fait planter mes trois cessions SAS...

Pas eu le temps de copier la log de mes cessions, elles sont KO...

Edit : copie écran d'une log entre deux vapeurs de ma cession serveur

[sasadm]

Salut,

Augmente la taille de ta work (option memsize je crois) et évite de lancer les 3 sessions en même temps.

[jerome_pdv2]

ok, je vais voir,

enfin l'informatique m'a annoncé qu'il y avait eu une interruption du serveur hier soir ceci explique peut-être cela...

Je vais essayer également un code alternatif.

[s_a_m]

Passer par des vues intermédiaires au lieu de tables, cela réduit considérablement la taille du fichier mais ne garantie pas une bonne performance quant à la réutilisation de cette vue pour des jointures avec d’autres tables/vues.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
DATA table1;
input ID TEXTE;
cards;
1 2
1 3
2 9
2 18
;
run;
 
 
DATA table2;
input ID TEXTE;
cards;
1 4
1 9
2 8
2 7
;
run;
 
/* resultat1 */
proc sql;
CREATE VIEW resultat1 
AS
SELECT tab1.ID AS ID1 
		, tab1.TEXTE AS TEXTE1
		, tab2.ID AS ID2
		, tab2.TEXTE AS TEXTE2
		, 1/(1+abs(tab1.TEXTE-tab2.TEXTE))  AS simil 
FROM Table1 AS tab1 JOIN Table2 AS tab2
ON   tab1.ID= tab2.ID AND tab1.TEXTE ^= tab2.TEXTE ;
;
quit;
 
 
/* resultat2 */
 
proc sql;
CREATE TABLE resultat2 
AS SELECT tab1.ID AS ID1,tab1.TEXTE AS TEXTE1,
tab2.ID AS ID2,tab2.TEXTE AS TEXTE2,
1/(1+abs(tab1.TEXTE-tab2.TEXTE)) AS simil
FROM Table1 AS tab1,Table2 AS tab2
WHERE tab1.ID NE tab2.ID
GROUP BY tab1.ID
HAVING  calculated simil=max(calculated simil)
;
quit;

Sans oublier qu'une vue ne peut être mise à jour.

[jerome_pdv2]

Merci s_a_m

finalement j'ai changé mon fusil d'épaule, je pensais que ce serait le plus rapide et adapté en SQL, mais cela ne le semble pas tout à fait vu les problèmes rencontrés.
Je suis passé aux objet hash, avec un gain de 30% semble t il, et là normalement plus de risque de débordement de la work.

Je lancerais tout cela lundi.

Merci aux participants.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
DATA identifie;
length TEXTE_n $45 ID_n $6 cle $52 max 6.2 TEXTE2 $45 ID2 $6;
SET ref1;
 
IF _n_=1  then do;
declare hash h0(dataset:"ref2",ordered:"yes");
h0.definekey("cle");
h0.definedata("ID_n");
h0.definedata("TEXTE_n");
h0.defineDone();
call missing (cle);
end;
 
declare hiter ih0("h0");
rc=ih0.first();
max=0;TEXTE2='';ID2='';
do while (rc=0);
simil=similarity2(TEXTE,TEXTE_n);
IF simil>max AND ID^=ID_n then do;max=simil;TEXTE2=TEXTE_n;ID2=ID_n;end;
 
rc=ih0.next();
 
end;
DROP cle ID_n TEXTE_n rc simil;
output;
run;

[s_a_m]

tu peux aussi créer des vues en étape DATA. sa te fera gagner de l'espace disque.

Code :

1
2
3
4
 
DATA test /VIEW=test;
SET sashelp.class;
run;

[jerome_pdv2]

Les view ça doit-être recalculé à chaque fois qu'on les appelle non ?

Sinon j'ai optimisé ma fonction "similarité" en fait je l'ai codé en étape data et laissé tombé la fcmp afin de minimiser les appels de fonction substr (j'avais dans les 200 000 000 000 appels ) à présent je n'en ai plus que pour 2h de calculs.

Sinon plus de débordement de la work.

[datametric]

oui à chaque appel c'est comme si tu exécutais le code.

tu avais essayé les fonctions de type
SOUNDEX
SPEDIS
COMPLEV
COMPGED

?

[jerome_pdv2]

non je n'utilise pas ces fonctions, mais une autre que j'ai codé consistant à comptabiliser le nombre de paire communes aux deux chaines de caractères.
Là je fais encore des appels à des substr(string,2*i+1,2) pour i=0 à nbpaires2 , j'aimerais bien que ça me coute encore moins cher en CPU, j'avais essayé en mettant les paires successives dans un array mais ça prend au final plus de temps.
J'avais de l'ordre de 2*N*N*L*L appels à des substr(),
j'en ai plus que N*L+N*N*L auxquels s'ajoutent N*N*L appels à des arrays environs (avec des chaines deux fois plus longues, mais que j'extrais par deux caractères consécutifs), j'aimerais bien linéariser plus, mais je ne crois pas que ce soit possible.

Pour encore optimiser le substr, il me faudrait quelque chose de rapide qui extrait et coupe les deux premiers caractères.
Peut-être en faisant un substr fixe substr(string,1,2) suivi d'un substr modifiant la variable string=substr(string,3);
De sorte à toujours récupérer les deux premières chaines sur une chaine devenant de plus en plus courte (espaces mis à part...je ne sais pas si SAS fait la différence mais j'en doute fortement )

Enfin mes dernières optimisations aboutissent à un temps de traitement de 1h20-1h30 contre 15h initialement avec la fcmp

[datametric]

jette un oeil sur les fonctions alors. Je les utilisais avec bcp de succès.

Sinon je me demande si en montant la table en mémoire cela n’accélérait pas les permutations. Un peu sur le principe de l'IML qui travaille vit pour cette raison.

[jerome_pdv2]

oui je connais ces fonctions, j'utililse cette fonctions parce que les autres ne me donne pas satisfaction comme je le souhaiterais,
par contre la proc IML, que je n'ai jamais utilisé serait peut être une solution...j'ai 53 000 x 53 000 séries variables à comparer mais qui peuvent être découpées en N x 53 000 séries voir N x M séries avec N et M plus petits

Par contre ça necessite de définir des matrices de dimension 4 (croisement des deux variables + 2 séries par croisement)

L'opération élementaire serait de savoir combien de termes sont égaux dans les séries, les séries étant classées par ordre croissant.

Qu'appelle tu monter une table en mémoire ? mon code actuel réalise le produit cartésien d'une table classique par un objet hash