Point sur les performances

En travaillant directement sur la base ORACLE vous gagnerez grandement en performance. Voyons comment :

SAS Guide : une interface vers la base de données ORACLE

Comme détenteur d’un accès permanent aux données du SNDS, vous vous connectez au portail pour utiliser le logiciel SAS Guide.

Cependant, les données à analyser sont stockées sur une base ORACLE, et SAS se comporte avant tout comme une interface vers cette base.

Si vous êtes prêt à attendre 5 minutes devant une session qui ne répond pas, vous pouvez obtenir la liste des tables de la base ORACLE en ouvrant le répertoire ORAVUE dans la fenêtre Serveurs :

En effet, ORAVUE donne accès à 2828 tables et l’interface graphique qui produit l’arborescence ne doit pas être optimisée pour afficher autant d’éléments…

Revenons sur l’architecture client-serveur dans laquelle SAS vous permet de vous connecter à la base de données ORACLE.

Si le client SAS sert d’interface pour accéder aux précieuses données, il possède son propre langage simili-SQL. De plus, une fois les données mises en forme, SAS pourra appliquer différentes méthodes statistiques. À moins que vous ne préfériez utiliser le logiciel RStudio…

Mon premier programme SAS

L’objectif est de récupérer la liste des bénéficiaires qui habitent à Paris.

La table IR_BEN_R contient la liste des bénéficiaires qui ont utilisé leur carte vitale depuis 2013, elle contient le champ BEN_RES_DPT qui rapporte le département de la dernière adresse connue du bénéficiaire.

Il est indiqué dans le schéma plus haut que SAS possède un simili-SQL permettant d’écrire des requêtes. Utilisons-le :

proc sql;

  create table  ORAUSER.PARISIENS as
  select * from ORAVUE.IR_BEN_R
  where BEN_RES_DPT = 075;

quit;

Il suffit d’utiliser proc sql pour pouvoir écrire une requête dans une syntaxe similaire au SQL ORACLE (pour ce qui est des requêtes simples).

Malheureusement, pour requêter des données, il est nécessaire que les tables soient du côté client SAS. L’exécution de ce code va donc commencer par rapatrier la table IR_BEN_R en la copiant depuis ORACLE. Or, cette table contient plusieurs dizaines de millions de lignes, autant que le nombre d’assurés ayant utilisé leur carte vitale depuis 2013.

Résultat de l’opération : en lançant cette requête (de manière asynchrone), on arrive à un temps d’exécution de 7h46.

On peut remarquer que si le nombre de lignes de cette table est de l’ordre de la population française, on peut être conduit à manipuler des tables bien plus grandes, avec des milliards de lignes, comme celle contenant une ligne par boite de médicaments remboursée en pharmacie (pour voir comment traiter efficacement ces tables, lire ce billet).

Plusieurs options pour travailler directement sur la base ORACLE

Il existe trois solutions pour que les requêtes s’exécutent directement sur le serveur ORACLE.

Solution 1 : DBIDIRECTEXEC

Lorsque cette option est activée :

• SAS va traduire la requête du bloc proc sql
• SAS va demander au serveur ORACLE d’exécuter cette requête sans rapatrier la table IR_BEN_R
• SAS rapatriera uniquement la table résultat, c’est-à-dire la table des bénéficiaires parisiens.

options DBIDIRECTEXEC;

proc sql;

create table  ORAUSER.PARISIENS as
select * from ORAVUE.IR_BEN_R
where BEN_RES_DPT = 075;

quit;

Cette fois le temps moyen d’exécution est de 12,4 secondes sur 10 tentatives, et la table résultat se trouve côté SAS, ce qui veut dire que vous pouvez lancer l’analyse statistique.

Solution 2 : EXECUTE BY ORACLE

Dans cette nouvelle approche, on travaille immédiatement sur le serveur ORACLE :

• On a accès au langage PL/SQL, plus cohérent et complet que le simili-SQL de proc sql fourni pas SAS
• La table résultant de la requête n’est pas rapatriée vers SAS

proc sql;
%connectora;
EXECUTE(

    create table PARISIENS as
	select * from IR_BEN_R
    where BEN_RES_DPT = 075)
    
BY ORACLE;
quit;

Dans ce code, on utilise la macro %connectora qui contient les détails de connexion à la base. Enfin on entoure la requête PL/SQL par EXECUTE( et )BY ORACLE;. On verra comment automatiser tout ceci dans le billet sur les macros.

Quel est le temps d’exécution de cette méthode ? Sur 10 tentatives, on obtient un temps moyen d’exécution de 3,82 secondes. C’est cette méthode qu’il faudra privilégier pour toutes les tables intermédiaires ne nécessitant pas de traitement statistique.

Solution 3 : SQL pass-through

Dans cette solution hybride on pourra :

• écrire une requête directement en PL/SQL
• obtenir la table résultante côté SAS pour un traitement statistique.

Pour cette méthode, on obtient, sur 10 tentatives, un temps d’exécution moyen de 16,14 secondes.

Tableau récapitulatif

Il ne faut donc jamais utiliser la méthode naïve proc sql comme il était obligatoire de le faire sur la base ATIH ou sur des accès SNDS sur projet.

Il faut préférer la méthode EXECUTE BY pour toutes les tables intermédiaires.

Et finalement, faire un SQL pass-through pour les tables nécessitant un traitement statistique.