Discussion :

[fafabzh6]

Bonjour,
Suite à différentes questions qui m'ont été posé sur ce problème de comparaison de ces deux techniques (DATA / PROC SQL) je me permets d'ouvrir ce topic pour voir les avantages et les inconvénients de ces deux techniques selon vous, ainsi que pour connaître les différents outils disponibles pour les comparer quantitativement.
Merci

[steelspirit]

Question difficile mais qui mérite bien sa place ici. Comme tu l'a dis, les 2 présentent des avantages et des inconvénients...

Je me lance :

-> Lors d'une jointure (fusion de table, merge...), avec une proc sql, il n'est pas nécessaire de trier les tables.

-> Lors d'une jointure plusieurs à plusieurs (doublons sur les clés de jointure dans les 2 tables) le MERGE de l'étape DATA a un comportement peu orthodoxe ... c'est la fameuse note: MERGE statement has more than one data set with repeats of BY values. La proc sql est donc préférable dans ce cas précis.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
data toto;
	input ID LABEL1 $;
	CARDS;
	1	AA
	1	AB
	1	AC
	1	AD
	2	AE
	2	AF
	2	AG
;
run;
data tata;
	input ID LABEL2 $;
	CARDS;
	1	BA
	1	BB
	2	BC
	2	BD
;
run;
data resultat;
	merge toto tata;
	by ID;
run;
proc print data=resultat;
run;
proc sql;
	select *
	from toto a, tata b
	where a.id=b.id;
quit;

Résultat étape DATA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
                                  Obs    ID    LABEL1    LABEL2
 
                                   1      1      AA        BA
                                   2      1      AB        BB
                                   3      1      AC        BB
                                   4      1      AD        BB
                                   5      2      AE        BC
                                   6      2      AF        BD
                                   7      2      AG        BD

Résultat étape SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
                                   ID  LABEL1          ID  LABEL2
 
                                     1  AA               1  BA
                                     1  AA               1  BB
                                     1  AB               1  BA
                                     1  AB               1  BB
                                     1  AC               1  BA
                                     1  AC               1  BB
                                     1  AD               1  BA
                                     1  AD               1  BB
                                     2  AE               2  BC
                                     2  AE               2  BD
                                     2  AF               2  BC
                                     2  AF               2  BD
                                     2  AG               2  BC
                                     2  AG               2  BD

[green_fr]

Pour moi la réponse est simple : utiliser la proc sql toujours quand il n'est pas prouver qu'il faut utiliser l'étape data.
Et l'étape data est à utiliser :

• pour pouvoir réutiliser les valeurs calculées pour la même ligne (imagine une variable calculée avec plusieurs "case" l'un dans l'autre, et après on veut créer une autre variable, calculée à partir de celle-là)
• pour pouvoir utiliser l'ordre des lignes (c'est une mauvaise pratique en soi, mais parfois il faut se référer à "la ligne précédente")
• pour pouvoir utiliser les fonctionnalités absentes dans SQL (_N_, FIRST., etc.) - très rarement

En ce qui concerne les performances, j'ai l'impression, que la jointure des tables fonctionne très mal sous SAS quelle que soit la méthode utilisée. Je parle des cas de jointure de plusieurs tables (une dizaine) avec beaucoup d'enregistrements dans chacune (disons des millions) et une clause "where" assez forte pour réduire le résultat à quelques enregistrements.
J'ai l'impression que SAS applique mot à mot la règle de multiplication cartésienne avec la sélection postérieur par les critères de "where". Tandis que Oracle et C° sélectionnent d'abord ce qu'il peuvent sélectionner (par exemple, si la clause "where" porte sur la colonne indexée d'une des tables à plusieurs millions d'enregistrements, la première chose à faire est bien évidemment réduire cette table avec la valeur de la clause "where") avant de se lancer sur le produit cartésien. D'où - à mon avis - intérêt d'utiliser (même sous SAS) les moteurs plus performants, tel Oracle et ses collègues RDBMS.

[fafabzh6]

Avez-vous déjà comparé ces deux méthodes en termes de temps de calculs? Ou avez-vous des références ayant déjà faits ces tests?
Connaissez-vous des fonctions SAS permettant permettant de comparer leurs performances (des sortes de marqueurs de début et fin de chronomètre se mettant au début et à la fin de l'exécution)?
Merci

[steelspirit]

Effectivement, il y a plusieurs moyens de tester les performances (Benchmarking) avec SAS.

Voici ma liste non exhaustive :
-> Regarder la LOG les 2 lignes "real time" et "cpu time" nous donne déja une bonne indication.
-> Mettre l'option FULLSTIMER qui ajoute dans la LOG des infos :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
NOTE: PROCEDURE PRINT used (Total process time):       
      real time           0.96 seconds       
      user cpu time       0.01 seconds       
      system cpu time     0.15 seconds       
      Memory                            83k

-> Récupérer le temps au début du traitement et en fin puis faire la soustraction pour voir le temps écoulé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
%let debut=%sysfunc(time());
<code SAS>
%let fin=%sysfunc(time());
%let duree=%sysevalf(&fin-&debut);
%put Temps d execution = &duree;

-> Regarder la charge du PC (ou du serveur) avec les outils de performance fournit par l'OS (cf image ci joint).


Je pense que déja on est pas mal là

Steel

[freneixj]

Bonjour à tous,

Je suis nouveau dans ce forum et j'ai été introduis par mon ami fafabzh6.

Pour en revenir à notre sujet, j'utilise SAS toute la journée dans le cadre de mon boulot, et il évident que si jointure il y a à faire (avec des tables de plusieurs millions d'enregistrements bien sûr), je ne fais jamais (mais vraimen jamais) de fusion sans restriction.

Je réduis à la fois drastiquement le nombre de colonnes balayées (nombre de champs ici : 4 sur les 78 présents) ainsi que le nombre de lignes balayées (par ma petite macro minmax qui va simplement chercher le minimum et le maximum de la variable de fusion).

Je peux vous garantir que contrairement à tous mes collègues qui utilisent que des proc SQL, ma requête va beaucoup plus vite.

MOI :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
%minmax(id_cli,tab1) ;
 
data tab2 ;
merge tab1 ( in = a ) 
      ic.TR_CLIENT ( keep = id_cli nom_cli pnom_cli cod_t_cli where = (&min le id_cli le &max) ) ;
by id_cli ;
if a ;
run ;

EUX :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
proc sql ; 
create table tab2 as
select a.*,
         b.nom_cli,
         b.pnom_cli,
         b.cod_t_cli 
from tab1 as a,
       ic.TR_CLIENT as b
where a.id_cli = b.id_cli ;
quit ;

Cela dit, si des gens connaisent l'envers du décor de manière précise (sous le capeau du moteur j'ai envie de dire), je suis très intéressé pour savoir comment marche ces 2 requêtes, quel est l'ordre des instructions, comment les tables sont parcourues, va-t-elle dans tous les cas parcourir l'ensemble de la table ic.TR_CLIENT malgrè les sélections ....

Je vous remercie par avence de votre aide précieuse.
Julien

[fdesgrand]

Bonjour,

Avec la proc sql, il est possible d'utiliser l'option _METHOD pour afficher le plan d'exécution de la requête.PROC SQL Code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
PROC SQL  _METHOD;
  SELECT  MOVIES.TITLE, RATING, ACTOR_LEADING
    FROM MOVIES,
         ACTORS
      WHERE MOVIES.TITLE = ACTORS.TITLE;
QUIT;
 
 
 
SAS Log Results 
 
NOTE: SQL execution methods chosen are:
      sqxslct
          sqxjhsh
              sqxsrc( MOVIES )
              sqxsrc( ACTORS )

La doc est visible ici :

http://support.sas.com/kb/33/604.html

Autre méthode pour rapprocher deux tables, use_hash, particulièrement efficace si une des deux tables a une petite volumétrie et peut être montée en mémoire.

[olivier.decourt]

Bonjour à tous.
Le sujet est vaste, et les débats promettent d'être agités (pour moi, SQL ou SAS, c'est surtout une affaire de goûts plus que de perfs).
Sur le sujet des performances, justement, SAS propose à partir de la version 9 (ou était-ce la 8.2 ? Je perds le fil des nouveautés par moments) des macros de surveillance de la perf des programmes : ARM (Application Response Management). La doc ici. Perso, je ne m'en suis jamais servi (je repère les temps d'exec avec les %LET déjà décrits), mais s'il y a des spécialistes en métrologie, ils y verront probablement des infos intéressantes.
Je signale aussi qu'il y a une option STIMER dans PROC SQL qui permet d'avoir le temps d'exécution de chaque requête s'il y en a plusieurs dans la même proc.
Ma contribution au débat "qui est le plus rapide" : je n'ai jamais vu que des cas particuliers, et aucune vérité générale. Selon les systèmes d'exploitation, l'organisation des tables, la charge de la machine qui exécute le programme, le match peut changer de vainqueur selon les cas. Donc, à mes yeux, tout le monde a gagné, c'est l'école des fans. Il est certain que plusieurs écritures en SQL ont l'avantage d'être plus "compactes". L'argument de l'absence de tris préalables aux jointures, souvent avancé, n'est pas toujours recevables, puisque 1) il suffit qu'une table soit indexée selon les variables du BY pour ne pas avoir besoin de la trier avant un MERGE et 2) ce qu'on gagne en ne triant pas, on le perd dans la construction du produit cartésien.
Sur ce que fait le SQL qui est compliqué à écrire en SAS : outre la liste déjà pointée par Steel, j'ajouterais la possibilité de faire des stats sur des variables créées en live, d'ajouter des stats à la table de base (SELECT *, MEAN(age) .... totalement interdit en SQL standard, mais autorisé (avec une note) dans SAS). Et puis encore : les requêtes imbriquées, et la création de macro-variables "listes" (INTO ... SEPARATED BY).

Addendum à la suite du post de Julien : tes deux exemples (MOI et EUX) ne sont pas exactement comparables, puisque tu introduis un WHERE et pas tes collègues. J'en profite pour signaler que dans une proc SQL, on peut mettre après les tables du FROM des options de table (KEEP=, DROP=, WHERE=) histoire de limiter la volumétrie entrante. Normalement, on doit gagner du temps (mais vous pouvez vérifier, ça fait longtemps que je n'ai pas fait des tests.

Olivier