L'écriture Efficace de Requêtes dans le SAS à l'Aide de Proc sql avec Teradata
EDIT: Voici un ensemble plus complet de code qui montre exactement ce qu'il se passe par la réponse ci-dessous.
libname output '/data/files/jeff'
%let DateStart = '01Jan2013'd;
%let DateEnd = '01Jun2013'd;
proc sql;
CREATE TABLE output.id AS (
SELECT DISTINCT id
FROM mydb.sale_volume AS sv
WHERE sv.category IN ('a', 'b', 'c') AND
sv.trans_date BETWEEN &DateStart AND &DateEnd
)
CREATE TABLE output.sums AS (
SELECT id, SUM(sales)
FROM mydb.sale_volue AS sv
INNER JOIN output.id AS ids
ON ids.id = sv.id
WHERE sv.trans_date BETWEEN &DateStart AND &DateEnd
GROUP BY id
)
run;
Le but est simplement d'interroger la table pour certaines id est basé sur la catégorie de membre. Alors j'en somme, ces activité des membres dans toutes les catégories.
L'approche ci-dessus est beaucoup plus lent que:
- L'exécution de la première requête pour obtenir le sous-ensemble
- De l'exécution d'un deuxième requête sommes tous les ID
- L'exécution d'une requête troisième intérieure entre les deux ensembles de résultats.
Si je comprends correctement, il peut être plus efficace pour s'assurer que l'ensemble de mon code est complètement passé à travers, plutôt que de la croix de chargement.
Après l'affichage d'une question d'hier, un membre a suggéré que je pourrais bénéficier de poser une question distincte sur le rendement qui est plus spécifique à ma situation.
Je suis à l'aide de SAS Enterprise Guide pour l'écriture de certains programmes/requêtes de données. Je n'ai pas les autorisations pour modifier les données sous-jacentes, qui est stocké dans 'Teradata'.
Mon problème de base qui est de l'écriture efficace des requêtes SQL dans cet environnement. Par exemple, j'ai une requête un tableau de grande taille (plusieurs dizaines de millions d'enregistrements) pour un petit sous-ensemble de l'ID. Ensuite, j'utilise ce sous-ensemble, à la requête de la plus grande table de nouveau:
proc sql;
CREATE TABLE subset AS (
SELECT
id
FROM
bigTable
WHERE
someValue = x AND
date BETWEEN a AND b
)
Cela fonctionne dans une affaire de secondes et retourne 90k ID. Ensuite, je veux interroger ce jeu d'ID à l'encontre de la grande table, et les problèmes en découlent. Je suis désireux de la somme des valeurs au cours du temps pour les ID:
proc sql;
CREATE TABLE subset_data AS (
SELECT
bigTable.id,
SUM(bigTable.value) AS total
FROM
bigTable
INNER JOIN subset
ON subset.id = bigTable.id
WHERE
bigTable.date BETWEEN a AND b
GROUP BY
bigTable.id
)
Pour quelque raison que ce soit, cela prend un temps très long. La différence est que la première requête drapeaux someValue'. La seconde s'intéresse à toutes les activités, quel que soit ce qui est dans 'someValue'. Par exemple, je pourrais drapeau de chaque client qui commande une pizza. Puis je regarde à chaque achat pour tous les clients qui ont commandé de la pizza.
Je ne suis pas trop familier avec SAS, donc je suis à la recherche pour obtenir des conseils sur la façon de le faire plus efficacement ou d'accélérer les choses. Je suis ouvert à toutes idées ou suggestions et s'il vous plaît laissez-moi savoir si je peux offrir plus de détails. Je suppose que je suis juste surpris de la deuxième requête prend tellement de temps à traiter.
Vous devez vous connecter pour publier un commentaire.
L'essentiel à comprendre lors de l'utilisation de SAS pour accéder aux données de Teradata (ou de toute autre base de données externe que ce soit), c'est que le logiciel SAS prépare SQL et le soumet à la base de données. L'idée est d'essayer et de vous soulager (l'utilisateur) à partir de toute la base de données de plus de détails. SAS à l'aide d'un concept appelé "implict pass-through", ce qui signifie simplement que la SAS fait la traduction de SAS code dans les SGBD code. Parmi les nombreuses choses qui se produisent est conversion de type de données: SAS n'a que deux (et seulement deux) types de données numériques et de caractère.
SAS traite de la traduction de choses pour vous, mais il peut être source de confusion. Par exemple, j'ai vu des "paresseux" tables de base de données définis avec VARCHAR(400) colonnes ayant des valeurs qui ne dépassent jamais quelques petites longueurs (comme la colonne pour le nom d'une personne). Dans la base de données ce n'est pas beaucoup d'un problème, mais depuis SAS ne dispose pas d'un type de données VARCHAR, il crée une variable de 400 caractères pour chaque ligne. Même avec l'ensemble de données de compression, ce qui peut vraiment faire la résultante SAS dataset inutilement grand.
L'autre façon est d'utiliser des "explicite" pass-through", où vous écrivez natif de requêtes à l'aide de la réelle syntaxe du SGBD en question. Ces requêtes à exécuter entièrement sur les SGBD et le retour des résultats de la SAS (ce qui a toujours fait la conversion de type de données pour vous. Pour exemple, voici un "pass-through" la requête qui effectue une jointure de deux tables et crée un SAS jeu de données comme un résultat:
Avis que tout à l'intérieur de la paire de parenthèses est natif de Teradata SQL et que l'opération de jointure lui-même est en cours d'exécution à l'intérieur de la base de données.
L'exemple de code que vous avez indiqué dans votre question est PAS complète, exemple de fonctionnement d'une SAS/Teradata programme. Afin de mieux les aider, vous avez besoin de montrer la véritable programme, y compris une bibliothèque de références. Par exemple, supposons que votre véritable programme ressemble à ceci:
Qui indiquerait préalablement affecté LIBNAME déclaration par laquelle SAS a été la connexion à Teradata. La syntaxe de cette clause where serait très pertinent si la SAS est même capable de passer de la requête complète de Teradata. (Vous n'est pas de montrer ce que "a" et "b" se réfèrent. Il est très possible que le seul moyen SAS peut effectuer la jointure est de faire glisser les deux tables de retour dans un local de la session de travail et effectuer la jointure sur votre serveur SAS.
Seule chose que je peux vous suggérons fortement que vous essayez de convaincre votre Teradata administrateurs pour vous permettre de créer "pilote" tableaux de certaines utilitaire de base de données. L'idée est que vous pouvez créer une relativement petite table à l'intérieur de Teradata contenant l'ID que vous souhaitez extraire, puis utiliser cette table pour effectuer des jointures explicites. Je suis sûr que vous auriez besoin d'un peu plus formel de la base de données de la formation pour le faire (comme la façon de définir un index approprié et comment faire pour "recueillir des statistiques"), mais avec les connaissances et la capacité, votre travail sera juste voler.
Je pourrais continuer encore et encore mais je vais m'arrêter là. J'utilise SAS avec Teradata largement chaque jour contre ce que je dit, c'est l'un des plus grands Teradata environnements de la planète. J'aime la programmation dans les deux.
mydb
.Vous suggérer une hypothèse que les 90k les enregistrements de votre première requête sont tous uniques
id
s. C'est que défini?Je demande parce que l'implication de votre deuxième question, c'est qu'ils ne sont pas uniques.
- Un
id
peut avoir plusieurs valeurs au fil du temps, et ont différentessomevalue
sSi le
id
s ne sont pas uniques dans le premier jeu de données, vous devezGROUP BY id
ou de l'utilisationDISTINCT
, dans la première requête.Imaginer que les 90k lignes se compose de 30k unique
id
s, et ont donc une moyenne de 3 lignes parid
.Et puis imaginer que ces 30k unique
id
s 9 enregistrements dans votre fenêtre de temps, y compris les lignes oùsomevalue <> x
.Vous obtiendrez alors 3x9 enregistrements en arrière par
id
.Et que ces deux numéros de croître, le nombre d'enregistrements dans une deuxième requête croît géométriquement.
Requête Alternative
Si ce n'est pas le problème, une autre requête (qui n'est pas idéal, mais possible) serait...
DISTINCT
liste. Je n'ai pas utilisé unHAVING
déclaration avant, je vais donner un essai et voir si ça aide.Si l'ID est unique et d'une valeur unique, vous pouvez essayer de construire un format.
Créer un ensemble de données qui ressemble à ceci:
fmtname, start, label
où fmtname est le même pour tous les enregistrements, d'un nom de format (commence et se termine par une lettre, contient des caractères alphanumériques ou _); départ est la valeur de l'ID; et l'étiquette est un 1. Puis ajouter une ligne avec la même valeur pour fmtname, un vide commencer, une étiquette de 0, et une autre variable,
hlo='o'
(pour les "autres"). Puis les importer dans proc format à l'aide de laCNTLIN
option, et vous avez maintenant une 1/0 valeur de conversion.Voici un bref exemple d'utilisation de SASHELP.CLASS. ID est le nom, mais il peut être numérique ou caractère - selon ce qui convient à votre utilisation.
Maintenant au lieu de faire une jointure, vous pouvez faire votre requête "normalement", mais avec une clause where supplémentaire de
and put(id,$IDF.)='1'
. Ce ne sera pas optimisé avec un indice ou quoi que ce soit, mais il peut être plus rapide que la jointure. (Il peut également ne pas être plus rapide dépend de la façon dont l'optimiseur SQL fonctionne.)Si l'id est unique, vous pouvez ajouter un INDEX PRIMAIRE UNIQUE(id) à la table, sinon, la valeur par défaut est Non unique PI.
Connaître uniquenes permet à l'optimiseur de produire un meilleur plan.
Sans plus d'info comme une Expliquer (mis juste EXPLIQUER devant le SÉLECTIONNER), il est difficile de dire comment cela peut être amélioré.
Une autre solution consiste à utiliser des procédures SAS. Je ne sais pas ce que votre SQL est en train de faire, mais si vous êtes juste faire des fréquences (ou autre chose qui peut être fait dans une PROC), vous pourriez faire:
Ou n'importe quel nombre d'autres options (PROC MOYENS, PROC COMPILER, etc.). Qui peut être plus rapide que de faire la somme dans SQL (selon certains détails, tels que la manière dont vos données sont organisées, ce que vous faites réellement, et la quantité de mémoire disponible). Il a l'avantage supplémentaire que SAS pouvez choisir de faire de cette base de données, si vous créez la vue dans la base de données, qui pourrait être plus rapide. (En fait, si vous venez d'exécuter la freq hors de la table de base, il est possible que ce serait encore plus vite, et puis rejoindre les résultats de la plus petite table).