Optimisez les performances de traitement de vos données avec SAS/ACCESS

Le BIG DATA nous immerge inexorablement dans un océan d'informations. Accéder à toujours plus données le plus rapidement possible n’a jamais été autant au cœur des attentes des utilisateurs. Aussi, dans ces environnements où la vitesse revêt une importance capitale, il est impensable de ne pas comprendre l’interaction entre SAS et les bases de données. Savoir quand il est plus avantageux de laisser SAS faire le traitement ou quand il est préférable que la base de données s’en charge est fondamental dans l’atteinte de meilleures performances. Les performances, toujours les performances... Le nerf de la guerre pour beaucoup d'entre nous. Mais comme pour un virus, il est toujours plus facile de le traiter quand on sait à qui on a affaire. Cet article explore les pistes pour traiter efficacement vos données externes et obtenir le meilleur gain de performance. Il s’adresse aux développeurs mais également aux architectes en charge de la mise en œuvre du système d’information. [nextpage title="Présentation de SAS/ACCESS"] Avant de s’attaquer au sujet central de cet article, un rapide rappel du fonctionnement du module SAS/Access peut s’avérer nécessaire. Les modules SAS/ Access sont des solutions « out-of-the-box » fournissant une connectivité entre SAS et des sources de données tiers, via le client du SGBD, y compris les data warehouse appliances, Hadoop Distributed File System et les bases de données relationnelles (Oracle, Sybase, Mysql …). Le schéma ci-dessous montre les interactions entre les clients SAS, le module SAS/ACCESS et une base de données Oracle. Ce schéma permet d’avoir une vision globale des échanges.

• La procédure DBLOAD n’est pas disponible pour tous les modules SAS/ACCESS,
• Client et serveur peuvent être localisés sur la même machine physique,
• Les librairies clientes, nécessaires au fonctionnement du module SAS/ACCESS, sont fournies par le fournisseur de la base de données.

[nextpage title="Se connecter à une base de données"] Jeu de données utilisé

Pour illustrer cet article nous allons travailler sur une base de données Oracle 10G hébergée sur un serveur AIX. Nous utiliserons une table volumineuse que nous avons créée dans le schéma « scott » Table « visiteurs ». Cette table compte 270 000 lignes

SQL PASS-THROUGH implicite et étape DATA

Vous pouvez utiliser SAS/ACCESS pour lire, mettre à jour, insérer et supprimer des données d'un objet SGBD comme s'il s'agissait d'un ensemble de données SAS. Voici comment faire : 1. Activation d'une interface SAS/ACCESS en spécifiant un nom de moteur SGBD et les options de connexion appropriées dans une déclaration de LIBNAME, 2. Vous interagissez avec les données comme vous le faites avec n’importe quelle bibliothèque SAS classique, 3. SAS/ACCESS génère, si possible, des instructions SQL qui sont l'équivalent des procédures SAS que vous avez saisi, 4. SAS/ACCESS soumet le SQL généré pour le SGBD. Si vous utilisez SAS en version 64 bits, il est nécessaire que votre client d’accès à Base de Données (librairies) soit en version 64 bits. Il existe 2 méthodes pour accéder aux tables contenues dans une source de données tiers. L’une d’elle consiste à créer une bibliothèque en utilisant l’instruction LIBNAME. Cette syntaxe est familière aux utilisateurs de SAS. Dans l’exemple ci-dessous, nous créons une bibliothèque pour nous connecter à oracle : Avec une déclaration LIBNAME, vous pouvez utiliser votre boîte à outils SAS et manipuler vos données via l'étape DATA et les procédures habituelles comme si vous travailliez avec des données SAS ordinaires. En effet, le module SAS/ACCESS traduit les instructions SAS en commandes SQL directement interprétables par la base de données. Si le code SAS ne peut pas être traduit en SQL, le contenu de la table est rapatrié dans SAS et le code exécuté directement par le moteur SAS. La connexion se fait à l’exécution du LIBNAME. Elle reste valide tant que la session est en cours et que le LIBNAME n’est pas effacé.

SQL PASS-THROUGH explicite

Le SQL Pass-Through explicite est une autre façon d'établir une connexion à la base de données externe. Dans ce cas, le code SQL est envoyé tel quel à la base de données, ce qui nécessite l’écriture d’une requête SQL compréhensible par le SGBD.

Choisir entre implicite et explicite.

Il n’y a pas de formule magique pour affirmer qu’une méthode est meilleure que l’autre. Cela dépend des circonstances, des connaissances et de l’expérience du développeur, notamment dans le langage SQL. Cependant quelques points sont à prendre en compte afin de choisir la méthode en fonction : Utilisez le SQL Pass-through explicite lorsque :

• Vous avez besoin d’utiliser une requête SQL spécifique à la base de données utilisée,
• Vous voulez garder un contrôle sur les requêtes SQL envoyées à la base de données,
• Vous voulez vous assurer que le code soit bien exécuté par le DBMS, et non rapatrié côté SAS par souci de performance

Utilisez le SQL Pass-through implicite et l’étape DATA lorsque :

• Vos besoins sont complexes pour gérer la base de données (utilisation du macro-langage, des array, de boucles, de fonctions sans équivalence par le SGBD) ou non gérables par SQL (boucles, conditions, proc tabulate, graphiques, ODS …)
• Vous êtes plus à l’aise avec le langage SAS Base
• Vos programmes doivent être capables de se connecter à différentes base de données (Oracle,Mysql,DB2…) avec seulement des modifications mineures.
• Enfin, voici un tableau récapitulatif de la syntaxe à utiliser (ces 3 exemples sont équivalents) :

Vous serez obligé de mixer le langage SAS Base et SQL. Le langage SAS Base est utilisé pour vos besoins de reporting ou encore pour créer du code complexe (macro, array, if, boucle…). Autant de choses que les bases de données ne savent pas faire. N’oubliez pas que l’étape DATA offre bien plus de possibilités que le SQL. [nextpage title="Obtenir les meilleurs performance possible"] Dans le processus d'optimisation, de nombreux aspects sont à considérer. Nous allons nous attarder sur les principaux accélérateurs de performance suivants :

• Réduire le volume de données transférées entre SAS et la base de données,
• Transmettre rapidement des données nécessaires,
• Optimiser le traitement dans la base de données.

Limiter le nombre de lignes et de colonnes retournées

Limiter le nombre de lignes retournées par le SGBD est un facteur de performance extrêmement important, car il faut éviter que trop de données transitent sur le réseau. Autant que possible, précisez des critères de sélection limitant le nombre de lignes que la base de données retourne à SAS. Utilisez la clause WHERE pour récupérer un sous-ensemble des données. Si vous êtes uniquement intéressé par les premières lignes d'une table, pensez à ajouter l'option OBS=. Cette option permet de limiter le nombre de lignes transitant à travers le réseau, ce qui améliore considérablement les performances lors de la lecture de grandes tables. Elle est également utile dans la phase d’écriture du programme, pour tester la validité de son code sans chercher à récupérer toutes les observations. Toujours pour limiter la taille des données, vous pouvez également agir sur le nombre de colonnes. Pour cela, vous pouvez utiliser l’instruction SELECT de la procédure SQL, ou par les instructions DROP/KEEP de l’étape DATA.

Faire réaliser le traitement par la base de données

Pour une efficacité optimale, cela parait naturel de dire que toutes les opérations doivent être réalisées à l’intérieur de la base de données. Mais dans la réalité, la mise en œuvre n’est pas toujours aisée et ne s’avère pas toujours simple. En effet, la solution de facilité consiste à se dire qu’en utilisant le SQL pass-through explicite, nous avons un contrôle total sur les requêtes SQL envoyées à la base de données. Néanmoins cela implique que nous ne changions pas de base de données dans le cycle de vie de votre projet et surtout cela implique également d’avoir des besoins simples. Comme indiqué dans le précédent chapitre, il est nécessaire de jongler avec les deux méthodes en fonction du besoin et du traitement que l’on souhaite mettre en œuvre. La syntaxe SQL, bien que standard, peut varier d’une base de données à une autre. Certaines spécificités dans la syntaxe d’une requête SQL peuvent être interprétées correctement par un moteur de base de données mais pas par un autre. Par exemple, la soustraction de deux SELECT s’écrit « SELECT … MINUS SELECT » sous Oracle et « SELECT … WHERE NOT EXISTS … » sous SQL Server. Dans le cadre d’une migration d’un SGBD vers un autre, cela nécessiterait une passe complète sur le code SAS et, dans le pire des scenarios, une réécriture de certaines requêtes SQL.

Tri et indexation des données

Trier des données est gourmand en ressources, que ce soit en utilisant la procédure SORT ou une clause ORDER BY. Triez les données uniquement lorsque cela est nécessaire pour votre programme. Si vous utilisez une instruction BY, il est recommandé d’associer votre BY avec une colonne indexée. En effet, lors de l’utilisation d’un BY, le moteur SAS/ACCESS va transformer ce BY en une clause ORDER BY compréhensible par la base de données (sous condition que les éventuelles fonctions Septembre 2013 - 8 - ajoutées soit interprétables). La clause ORDER BY va trier les données avant de les retourner à SAS. Si votre table est volumineuse, ce tri peut nuire aux performances. Utilisez une variable basée sur une colonne indexée afin de réduire cet impact négatif. Cependant, le choix d’indexer une table ne se fait pas à la légère. La création d'index utilise de l'espace mémoire dans la base de données, et, étant donné qu'il est mis à jour à chaque modification de la table à laquelle il est rattaché, cela peut alourdir le temps de traitement du SGBDR lors de la saisie de données. L'entretien d'un index sur une colonne a également un impact sur les écritures de cette colonne. Aussi, il faut que la création d'index soit justifiée et que les colonnes sur lesquelles il porte soient judicieusement choisies, notamment pour minimiser les doublons. Pour illustrer l’impact d’un index sur les performances, nous allons utiliser une PROC SORT pour classer une table. Cette table, visiteurs_ville, contient 10 millions de lignes. Sa structure est la suivante :

• Id
• Id_visiteur
• Code postal

L’id _visiteur est à mettre en relation avec la table visiteur utilisée dans l’ensemble de cet article. Soumettons le code suivant : Le temps d’exécution est de 59,42 secondes. Maintenant, positionnons un index sur cette table. L’index est ajouté sur le champ id_visiteur : Si nous resoumettons le code, le temps d’exécution passe de 59,42 secondes à 36,07 secondes. Il faut savoir que même en présence d'un index, l'usage de ce dernier n'est pas systématique. Pour un nombre de lignes à retourner important, l'usage d'un index est plus coûteux qu'un parcours complet de la table. C'est donc très courant que les index ne soient pas utilisés dès que le moteur SQL estime que le nombre de lignes à récupérer dépasse une certaine valeur. Lorsque vous utilisez la procédure SORT, sachez que les règles de tri de SAS et du SGBD peuvent être différentes. Utilisez l’option SAS SORTPGM pour spécifier les règles à appliquer (SORTPGM System Option)

Dévoiler les requêtes de SAS/ACCESS

SAS propose l’option SASTRACE permettant d’indiquer dans le journal SAS la requête envoyée à la base de données. Cumulée avec l’option FULLSTIMER, l’option SASTRACE se révèle d’une grande utilité. Surtout si le temps de traitement d’un programme vous semble anormalement lent. Selon les paramètres utilisés, l’option SASTRACE donne un résultat différent dans le journal SAS. Généralement, nous utilisons la syntaxe suivante : Vous connaissez à présent cette option, reprenons donc les instructions SAS détaillées dans la partie 2 et observons le journal SAS maintenant que SASTRACE est positionnée : Dans les 3 exemples ci-dessus la requête SQL envoyée à la base de données est identique (SELECT * FROM VISITEURS). Pour désactiver les logs SAS/ACCESS utilisez la syntaxe suivante : [nextpage title="Conclusion"] L'optimisation est un art mais aussi un combat de tous les jours. Vous ne serez jamais totalement formé. Mais même pour un débutant il y a juste quelques « boutons à pousser » pour rendre les traitements flux de données plus rapides. Mais n’oubliez jamais que :

• SAS offre de nombreuses possibilités d'optimisation,
• Ces solutions sont simples à mettre en œuvre,
• Ces solutions sont bénéfiques avec peu d'effort,
• Assurez-vous d’une utilisation efficace des ressources,
• La mémoire tampon consomme de la mémoire,
• Il vous faudra un certain effort pour trouver les réglages optimaux en fonctionnement de votre environnement et de son utilisation,
• Utilisez les SASTRACE durant la phase de développement mais n’oubliez pas de la désactiver avant le passage en production,
• Et enfin, mais non des moindres, toujours tester les options sur de petits sous-ensembles de données.