J’ai suivi le webinaire SAS sur le SQL Pass-Through : Voici comment optimiser vos traitements

Je sors tout juste d'un webinaire proposé par SAS, "Comment améliorer vos temps de traitement en utilisant la fonction SQL Pass Through ?", animé par Yulia Paramonova, consultante au sein du département Customer Advisory de SAS France et je voulais partager avec vous les points essentiels que j'en ai retenus. Si certains aspects peuvent paraître basiques pour les experts, ils constituent néanmoins une base solide et indispensable pour quiconque souhaite optimiser ses traitements de données. Le sujet central était l'optimisation des traitements et l'utilisation pertinente du SQL Pass-Through au sein de l'environnement SAS Access. L'idée maîtresse ? Comprendre comment les rouages techniques influencent nos temps d'exécution et comment s'appuyer sur la puissance des Systèmes de Gestion de Bases de Données (SGBD) grâce au SQL Pass-Through.

Les Fondations d'une Optimisation Efficace

Avant de rêver de techniques avancées, il est crucial de bien comprendre les éléments fondamentaux qui régissent la vitesse de nos traitements. Au-delà de ce qui a été présenté lors de ce webinaire, et fort de ma propre expérience et de mes nombreux écrits sur le sujet, je souhaite enrichir cette base. L'optimisation est un domaine que j'explore en profondeur à travers mes articles, et je ne manquerai pas de vous orienter vers certaines de mes publications pour approfondir des aspects spécifiques que nous allons aborder

Les Facteurs Impactant le Temps de Traitement

Le temps de traitement d’une requête ou d’un script est influencé par une multitude de facteurs, mais quatre éléments clés ressortent :

CPU (Processeur) : Il effectue les calculs et exécute le code.

Mémoire : Elle assure le stockage temporaire des données et des instructions durant l’exécution.

I/O (Input/Output) : Il s’agit des opérations de lecture et d’écriture sur le disque, incluant les transferts via le réseau.

Espace disque : Il permet le stockage final des données et peut impacter la vitesse d’accès.

Ces facteurs, bien qu’ils puissent paraître techniques pour certains utilisateurs, sont cruciaux lorsqu’on travaille avec des bases de données structurées et volumineuses.

Bonnes Pratiques d’Optimisation

Pour optimiser vos traitements, il convient de respecter quelques principes simples, voire de devenir « paresseux » au bon sens :

Ne faites pas plus de travail que nécessaire :
Par exemple, si vous devez analyser une table de 100 colonnes et seules 10 sont utiles, sélectionnez uniquement celles-ci pour éviter un traitement inutile.

Optimiser selon votre environnement :
Que vous travailliez dans le secteur banquaire, assurance, santé ou grande distribution, chaque configuration de serveur et chaque type de disque auront leurs spécificités. Connaître et discuter avec votre administrateur de bases de données est primordial.

Choisir ses batailles :
Concentrez-vous sur les traitements les plus chronophages. Un traitement qui prend une heure peut être optimisé pour passer à 15 minutes, alors qu’un traitement d’une minute, exécuté des milliers de fois par jour, pourrait aussi nécessiter une optimisation.

Faire un benchmark régulier :
On sort le comparateur de temps (réel vs. CPU) pour traquer les améliorations concrètes de nos traitements. Mais gare aux fantômes du cache qui peuvent nous faire croire à des gains miraculeux ! Franchement, j'ai été un peu surpris que FULLSTIMER n'ait pas été de la partie. C'est l'outil de télémétrie indispensable pour analyser les performances de nos Data step au niveau du bit près. Un peu comme tester un nouveau processeur sans regarder les benchmarks détaillés, il manquait une pièce maîtresse !

Positionnement judicieux des filtres et instructions (WHERE, KEEP, etc.) :
Le placement de ces instructions dans votre code SAS peut considérablement réduire le volume de données transféré et donc améliorer les performances.

SQL Pass-Through : Accéder aux Données en Direct

L’une des fonctionnalités les plus puissantes de SAS Access est le SQL Pass-Through. Cette fonctionnalité permet à l’utilisateur de transmettre directement des requêtes SQL au SGBD, offrant ainsi un contrôle précis sur la lecture, l’écriture et la mise à jour des données.

Définition et Avantages

Grâce à SQL Pass-Through, vous pouvez :

Exécuter des requêtes en utilisant la syntaxe native du SGBD (Oracle, Teradata, Single Store, etc.).

Lire, écrire et mettre à jour les données de manière transparente sur différentes plateformes.

Bénéficier d’une optimisation des requêtes, puisqu’une grande partie du travail est réalisée côté SGBD, ce qui réduit le transfert de données et accélère les traitements.

Modes d’Utilisation : Implicite vs. Explicite

Il existe deux méthodes pour se connecter à un SGBD via SQL Pass-Through :

Pass-Through Implicite

Utilisation d’un LIBNAME :
Vous déclarez une connexion via l’instruction LIBNAME. Ensuite, le code SAS standard (PROC SQL classique) est automatiquement traduit par SAS Access dans la syntaxe native du SGBD.

Avantages :

Simplicité d’écriture.

Un code unique et portable sur différentes bases de données.

Moins de nécessité de connaître la syntaxe SQL propre à chaque SGBD.

Pass-Through Explicite

Utilisation de PROC SQL Connect To :
Ici, vous écrivez directement la requête en langage natif du SGBD (par exemple, en utilisant les fonctions spécifiques d’Oracle comme TO_CHAR ou TO_DATE).

Avantages :

Meilleur contrôle sur la requête.

Exécution directe sans traduction intermédiaire par SAS Access.

Possibilité de combiner la puissance du SGBD avec certains traitements ultérieurs dans SAS (post-processing).

Exemples Pratiques et Démonstrations

La présentation a illustré plusieurs exemples concrets à l’aide de SAS Studio et de connexions libname.

Connexion et Visualisation des Données

Déclaration des LIBNAME :
En déclarant un LIBNAME pour Oracle, la table et ses propriétés (types, formats, labels) sont automatiquement accessibles dans SAS.

Exemple :
Après la déclaration, vous pouvez visualiser les colonnes et les métadonnées telles que les longueurs et les formats, facilitant ainsi l’écriture de requêtes optimisées.

Comparaison de Requêtes et Benchmarking

Utilisation de l’option SASTRACE :
Cette option permet d'afficher dans le log le détail des requêtes transmises au SGBD. Vous pouvez ainsi voir si le filtre WHERE est appliqué côté SGBD ou si SAS doit récupérer l’intégralité de la table avant de filtrer.

J'ai un article complet sur le fonctionnement de l'option sastrace, que je vous invite à découvrir.

Exemple de filtration :
Un KEEP placé correctement (au niveau de l’instruction libname plutôt que dans la DATA step) réduit le volume des colonnes transférées et accélère l’exécution.

Jointures et Emplacement des Calculs

Jointures entre tables SAS et tables de la base :
Il est souvent plus efficace, en termes de temps de traitement, de réaliser les jointures entièrement dans le SGBD plutôt que de combiner des tables SAS avec des tables distantes.

Impact sur le temps réel d’exécution :
Lors d’un exemple, exécuter une jointure par le SGBD a ramené le temps d’exécution de 2 minutes 15 à seulement 30 secondes, améliorant significativement l’expérience utilisateur.

Exécution Complète dans le SGBD

Utilisation de l’option EXECUTE :
Permet d’effectuer tout le traitement dans la base sans transférer les résultats vers SAS.

Cas d’usage :
Création de tables directement dans la base, ce qui est particulièrement utile quand vous disposez des droits d’écriture et que vous souhaitez éviter des transferts de grandes quantités de données.

Combiner la Puissance de SAS et des Bases de Données

L’un des grands avantages de SAS Access est la possibilité de combiner la simplicité d’écriture de SAS avec la puissance des SGBD :

En écrivant un code SAS standard tout en profitant de la traduction automatique (en mode implicite) ou en écrivant directement en SQL natif (en mode explicite), vous pouvez obtenir le meilleur des deux mondes.

Exemple combiné :
Certaines démonstrations ont montré comment appliquer des formats et des labels SAS sur des résultats obtenus via SQL Pass-Through, permettant ainsi de réaliser des traitements complexes rapidement et efficacement.

Bonnes Pratiques et Recommandations Finales

Pour résumer, voici quelques recommandations clés pour optimiser vos traitements et exploiter au mieux SQL Pass-Through :

Respecter les 4 facteurs (CPU, mémoire, I/O, espace disque) lors de la conception de vos traitements.

Minimiser le transfert de données : Appliquer des filtres directement dans la requête exécutée par le SGBD.

Utiliser le benchmarking : Comparez toujours le temps réel d'exécution et le temps CPU pour détecter d'éventuels goulets d'étranglement.

Choisir judicieusement le mode (implicite ou explicite) selon le contexte :

Pour des traitements rapides et génériques, optez pour le Pass-Through Implicite.

Pour des traitements nécessitant une syntaxe spécifique ou une optimisation fine, privilégiez le Pass-Through Explicite.

Attention aux fonctions spécifiques : Certaines fonctions SAS ne sont pas convertibles et peuvent forcer SAS à récupérer l’intégralité des données dans le cas où le SGBD ne comprend pas la syntaxe.

Éviter les jointures entre tables SAS et SGBD pour de grandes quantités de données : privilégiez les jointures directement dans le SGBD.

Pour aller plus loin, des formations dédiées à SAS existent et permettent d’approfondir ces concepts. Des ressources complémentaires, telles que des schémas d’optimisation et des cas d’étude, sont également disponibles pour aider les utilisateurs à améliorer continuellement leurs traitements.

Conclusion

Cette présentation a permis de mettre en lumière :

L’importance de comprendre et d’optimiser les facteurs techniques impactant le temps de traitement.

La puissance de SQL Pass-Through pour interagir efficacement avec divers SGBD, que ce soit en mode implicite ou explicite.

L’intérêt de combiner les avantages de SAS et des bases de données pour obtenir des traitements rapides et robustes.

Avec ces bonnes pratiques et la baguette magique de SAS Access, vous allez transformer vos traitements poussifs en bolides de course ! Vos systèmes vous remercieront par une performance étincelante, et vos utilisateurs vous couvriront de lauriers pour votre efficacité.

Bonne optimisation et à bientôt pour de nouvelles découvertes dans l’univers SAS et du SQL Pass-Through !