SAS Optimisation Transaction Ajout et Mise à jour

Avant propos

Cet article à pour de présenter une démarche d’optimisation pour un traitement de type Transaction pour l’ajout ou la mise à jour de données SAS.

Ce genre de problématique étant récurrent, il m’a semblé utile de vous fournir l’exemple détaillé ci-dessous.

Cet exemple se base sur des faits réels rencontrés lors d’une mission d’optimisation pour le compte du client « La Redoute », lors de mon passage au service Consulting de chez SAS Institute. Le chiffres fournis sont approximatifs et n’engage en rien la responsabilité de qui que ce soit.

Problème :

Redoute : 25 000 000 de lignes de commandes par jour et un temps de traitement sous SAS MVS avoisinant les 22 heures.

Bien sur, panique et demande d’intervention pour un audit d’optimisation.

Étude cynématique : par téléphone, par courrier et par internet réception des lignes de commande stockées dans DB2. La nuit Unload DB2 vers un fichier plat pour incorporation dans SAS. Lecture des lignes de commandes, tentative de mise à jour de la base SAS avec les lignes de commandes et en cas de rejet ajout de celles-ci dans la base SAS. Temps de traitement abominable.

Synthèse des Solutions

Deux cas se présentent :

1. On connait sous DB2 la nature de la transaction « ligne de commande » ajout ou modification
2. On ne la connait pas

Dans ces deux cas une solution performante existe.

La première la plus simple, ne sera pas détaillé dans cet article, simplement on transmet issu de DB2 dans l’unload une variable de type flag avec A pour ajout et M pour mise à jour. Sous SAS on éclate le fichier en deux tables SAS une pour les ajouts qui seront traités par la PROC APPEND et l’autre pour les mises à jour qui seront traités avec une étape DATA / MERGE.

Pour le deuxième cas un peu plus pointu, voici ci-dessous un exemple de programme fournissant une solution optimisée pour ces traitements transactions AJOUT / MAJ.

Plutôt que le programme SAS, je vous fournit le compte rendu d’exécution sur une machine de base et exécuté sous Windows Vista SAS V9.

Machine : Sans marque AMD 7750 x2, RAM 4 Go, HD SATA 500 Go, Windows Vista 64 bits SAS V 9.00.

Attention les temps d’exécution peuvent paraître excellent, mais la machine n’est pas partagée et les tables d’exemple sont petites et surtout elles n’ont que 6 variables, dans la vraie vie c’est plutôt de 200 à 500 variables.

Toutefois, La Redoute avec cette solution à ramenée son temps de traitement de 22 à moins de 3 heures.

Tables d’exemple :

• Base : toto1 – 5 000 000 de lignes et 6 variables
• Transaction : toto2 – 525 000 lignes et 6 variables
• TP – MAJ : maj – 175 001 lignes et 6 variables
• TP – Ajout : add – 349 999 lignes et 6 variables
• Temps d’exécution total réel (elaps) : 9.53 secondes

Extrait de la Log :

379  /* ****************************************** */
380  /* Site : http://www.formations-sas.fr - 2009 */
381  /* SAS V9 : Optimisation                      */
382  /*                                            */
383  /* Comment optimiser un traitement de         */
384  /* transaction en SAS.                        */
385  /*                                            */
386  /* Auteur : Pascal MAUBERT                    */
387  /* ****************************************** */
388
389  options compress=no bufno=8 bufsize=9220 fullstimer msglevel=i;
390
391  /* Création de la table de base exemple */
392  data toto1(index=(i /unique));
393   do i = 1 to 5000000;
394      j=i*2;
395      y=j*3.14;
396      orig='A';
397      k=i*j;
398      total=sum(of i j y k);
399      output;
400   end;
401  run;

Note: La table WORK.TOTO1 a 5000000 observations et 6 variables.
Note: Index simple i défini.
Note: L'étape DATA used (Total process time):
 temps réel                   3.96 secondes
 temps processeur utilisateur 2.48 secondes
 temps processeur système     0.99 secondes
 Mémoire                            68348k

402
403  /* Création de la table de Transaction exemple */
404  data toto2(index=(i /unique));
405   do i = 1500000 to 12000000 - 1 by 20;
406      aj=i*2/2;
407      ay=aj*3.14/3.14;
408      aorig='B';
409      ak=i*aj/aj;
410      total=sum(of i aj ay ak);
411      output;
412   end;
413  run;

Note: La table WORK.TOTO2 a 525000 observations et 6 variables.
Note: Index simple i défini.
Note: L'étape DATA used (Total process time):
 temps réel                   0.32 secondes
 temps processeur utilisateur 0.23 secondes
 temps processeur système     0.09 secondes
 Mémoire                            12940k

414
415  /*  Début du traitement hebdomadaire, chez vous sur les unload DB2 */
416  /*  Création des tables MAJ et ADD */
417  data maj
418       add(rename=(aj=j aorig=orig ay=y ak=k));
419   merge toto1(in=a keep=i)
420         toto2(in=b);
421   by i;
422   if b and a     then output maj;
423   if b and not a then output add;
424   else delete;
425  run;

INFO : Index i sélectionné pour traitement de la clause BY.
INFO : Index i sélectionné pour traitement de la clause BY.
Note:  5000000 observations copiées de la table WORK.TOTO1.
Note:  525000 observations copiées de la table WORK.TOTO2.
Note: La table WORK.MAJ a 175001 observations et 6 variables.
Note: La table WORK.ADD a 349999 observations et 6 variables.
Note: L'étape DATA used (Total process time):
 temps réel                   3.89 secondes
 temps processeur utilisateur 3.58 secondes
 temps processeur système     0.29 secondes
 Mémoire                            268k

426
427  /* Traitement de la table MAJ */
428  data toto1;
429   set maj(drop=aj ay ak); /* Le drop si Variables non MAJ */
430   modify toto1 key=i /unique ;
431   i=i;
432   *j=aj;
433   *y=ay;
434   orig=aorig;
435   *k=ak;
436   if _iorc_=0 then replace;
437   else do; _error_=0; output; end;
438  run;

Note:  175001 observations copiées de la table WORK.MAJ.
Note: Table WORK.TOTO1 mise à jour.  175001 observations recopiées, 0 observations ajoutées et 0 observations supprimées.
Note: L'étape DATA used (Total process time):
 temps réel                   0.90 secondes
 temps processeur utilisateur 0.60 secondes
 temps processeur système     0.26 secondes
 Mémoire                            151k

439
440  /*  Traitement de la table ADD */
441  Proc append data=add base=toto1;
442  run;

Note: Ajout de WORK.ADD to WORK.TOTO1.
INFO : Processus d'ajout rapide d'un moteur en cours d'utilisation.
INFO: la méthode de lecture par bloc du moteur est en cours d'utilisation.
INFO: la méthode d'écriture par bloc du moteur est en cours d'utilisation.
Note:  349999 observations copiées de la table WORK.ADD.
Note: 349999 observations added.
Note: La table WORK.TOTO1 a 5349999 observations et 6 variables.
Note: La procédure APPEND used (Total process time):
 temps réel                   0.46 secondes
 temps processeur utilisateur 0.31 secondes
 temps processeur système     0.15 secondes
 Mémoire                            8639k

443
444  /* ****************************************** */
445  /* Site : http://www.formations-sas.fr - 2009 */
446  /* Auteur : Pascal MAUBERT                    */
447  /* ****************************************** */

Et maintenant selon la formule consacrée : A vos claviers