Proc SQL

Yan IMAConsultant SAS

Conception d’Outils Informatiques Inc.4025 HochelagaMontréal, QuébecH1W 1K4http://www.coi.ca

Les options cachées

Quelques « vérités »…

Une Proc SQL effectue toujours un produit cartésien !

C’est une boîte noire…

Elle peut toujours remplacer un data step !

Plusieurs stratégies…

Pour une requête avec jointure, l’optimiseur de la Proc SQL peut choisir entre :

un tri puis un merge, une création d’index, un hachage avec chargement en mémoire.

Comment choisit-il ?

Index si possible,

Merge si une des tables (ou les deux) sont déjà triées,

Hachage si une des tables peut tenir en mémoire,

Sinon tri.

L’option _METHOD

Permet d’analyser l’exécution de la Proc SQL en visualisant les choix opérés par l’optimiseur:

proc sql _method;requête SQL;

quit;

L’option _METHOD

proc sql _method;

select a.name, a.sex, a.age, b.predict

from sashelp.class a, sashelp.classfit b

where a.name = b.name;

quit;

NOTE: SQL execution methods chosen are:

sqxslct

sqxjhsh

sqxsrc( SASHELP.CLASS(alias = A) )

sqxsrc( SASHELP.CLASSFIT(alias = B) )

L’option _METHOD

proc sql _method;

select a.name, a.sex, a.age, b.predict

from sashelp.class a left join sashelp.classfit b

on a.name=b.name;

quit;

NOTE: SQL execution methods chosen are:

sqxslct

sqxjm

sqxsort

sqxsrc( SASHELP.CLASSFIT(alias = B) )

sqxsort

sqxsrc( SASHELP.CLASS(alias = A) )

A quoi cela peut-il servir ?

La taille du buffer de la Proc SQL a une incidence directe sur le choix de la stratégie à adopter.

L’augmenter peut inciter l’optimiseur à charger plus de tables en mémoire ; donc réduire sensiblement les temps de traitement.

Un exemple…

proc sql _method ; create table Finance_FIN_01_12_201012 as select TYPE_fre_dsc as Type_de_client, SEGA_fre_dsc as Segment, REGA_fre_dsc as Region, AREA_fre_dsc as Secteur, SUCC_fre_dsc as Centre_d_affaires, stt_succ as UnitCD, stt_var_nm as QstVar, var_fre_dsc as QstDsc length=75, b.grp_fre_dsc as StatDsc, N as Stat_N format=8.0, PW as Stat_PctW From monsug.tb_600m Join mrssys._sys_3var On var_id = stt_var_id

Join mrssys._sys_4grp a On a.grp_beg_val = stt_agg_tp_cd And a.grp_etu_nm = 'T999V01' And((grp_beg_dt <= "01DEC2010:00:00:00"dt | grp_beg_dt is null) and(grp_end_dt >= "31DEC2010:00:00:00"dt | grp_end_dt is null) )Join Mrsref.B001V02_ref_type t1 On t1.type = stt_typeJoin Mrsref.B001V02_ref_sega t2 On t2.sega = stt_segaJoin Mrsref.B001V02_ref_rega t3 On t3.rega = stt_regaJoin Mrsref.B001V02_ref_area t4 On t4.area = stt_areaJoin Mrsref.B001V02_ref_succ t6 On t6.succ = stt_succLeft Join mrssys._sys_4grp b On b.grp_id = stt_grp_id

Where b.grp_suppress_ind ne 1 ;quit;

Sa log…

NOTE: SQL execution methods chosen are: sqxcrta sqxfil sqxjm sqxsort sqxsrc( MRSSYS._sys_4grp(alias = b) ) sqxsort sqxjm sqxsort sqxfil sqxsrc( MRSSYS._sys_3var ) sqxsort sqxjm sqxsort sqxsrc( MRSREF.B001V02_ref_succ(alias = t6) ) sqxsort sqxjm sqxsort sqxsrc( MRSREF.B001V02_ref_area(alias = t4) ) sqxsort sqxjm sqxsort sqxsrc( MRSREF.B001V02_ref_rega(alias = t3) ) sqxsort sqxjm sqxsort sqxsrc( MRSREF.B001V02_ref_sega(alias = t2) ) sqxsort sqxjm sqxsort sqxsrc( MRSREF.B001V02_ref_type(alias = t1) ) sqxsort

sqxjhsh sqxsrc( MONSUG.TB_600M ) sqxsrc( MRSSYS._sys_4grp(alias = a) )

cpu time 35.01 seconds

Augmentons le buffer…

proc sql _method ;

reset buffersize=256k; create table Finance_FIN_01_12_201012 as select TYPE_fre_dsc as Type_de_client, SEGA_fre_dsc as Segment, REGA_fre_dsc as Region, AREA_fre_dsc as Secteur, SUCC_fre_dsc as Centre_d_affaires, stt_succ as UnitCD, stt_var_nm as QstVar, var_fre_dsc as QstDsc length=75, b.grp_fre_dsc as StatDsc, N as Stat_N format=8.0, PW as Stat_PctW From monsug.tb_600m Join mrssys._sys_3var On var_id = stt_var_id

Join mrssys._sys_4grp a On a.grp_beg_val = stt_agg_tp_cd And a.grp_etu_nm = 'T999V01' And((grp_beg_dt <= "01DEC2010:00:00:00"dt | grp_beg_dt is null) and(grp_end_dt >= "31DEC2010:00:00:00"dt | grp_end_dt is null) )Join Mrsref.B001V02_ref_type t1 On t1.type = stt_typeJoin Mrsref.B001V02_ref_sega t2 On t2.sega = stt_segaJoin Mrsref.B001V02_ref_rega t3 On t3.rega = stt_regaJoin Mrsref.B001V02_ref_area t4 On t4.area = stt_areaJoin Mrsref.B001V02_ref_succ t6 On t6.succ = stt_succLeft Join mrssys._sys_4grp b On b.grp_id = stt_grp_id

Where b.grp_suppress_ind ne 1 ;quit;

La nouvelle log…

NOTE: SQL execution methods chosen are:

sqxcrta

sqxfil

sqxjm

sqxsort

sqxsrc( MRSSYS._sys_4grp(alias = b) )

sqxsort

sqxjm

. . .

sqxjhsh

sqxjhsh

sqxjhsh

sqxjhsh . . .

cpu time 29.96 seconds

Pas une science exact…

Faire varier la taille du buffer n’a pas systématiquement une incidence sur les temps de traitement.

Un seul mot d’ordre : expérimenter..

Un dernier exemple…

libname ora oracle user = xxx pwd = xxx…

proc sql _method;select tab1.x, tab2.yfrom ora.tab1, ora.tab2where tab1.cle = tab2.cle;

quit;

TEMPS CPU : T

Avec une légère modification…

proc sql _method;select table1.x, table2.y,

substr(tab2.z,2,3) as topfrom ora.tab1, ora.tab2where tab1.cle = tab2.cle;

quit;

TEMPS CPU : 17 T !!!

Un début d’explication…

Extrait de la log sans la fonction Substr :

sqxcrta

sqxextr( connection to SASIOORA (select tab1.x, tab2.yfrom ora.tab1…))

Codes… pour les tripeux…

SqxCRTA Create table as select

SqxSLCT Select

SqxJSL Step loop join (Cartesian)

SqxJM Merge Join

SqxINDX Index Join

SqxHASH Hash Join

Suite des codes…

SqxSORT Sort

SqxSRC Source rows from table

SqxFIL Filter rows

SqxSUMG Summary stats with group by

SqxSUMM Summary

L’option _tree

Présente sous forme d’arbre, l’exécution de la Proc SQL :

proc sql _tree requête SQL;quit;

L’option _tree : un exemple

proc sql _tree;

select a.name, a.sex, a.age, b.predict

from sashelp.class a, sashelp.classfit b

where a.name=b.name;

quit;

L’option _tree : la log

Arborescence telle qu'elle a été prévue.

/-SYM-V-(a.Name:1 flag=0001)

/-OBJ----|

| |--SYM-V-(a.Sex:2 flag=0001)

| |--SYM-V-(a.Age:3 flag=0001)

| |--SYM-V-(a.Height:4 flag=0001)

| |--SYM-V-(a.Weight:5 flag=0001)

| \-SYM-V-(b.predict:6 flag=0001)

/-JOIN---|

| | /-SYM-V-(a.Name:1 flag=0001)

| | /-OBJ----|

| | | |--SYM-V-(a.Sex:2 flag=0001)

| | | |--SYM-V-(a.Age:3 flag=0001)

| | | |--SYM-V-(a.Height:4 flag=0001)

| | | \-SYM-V-(a.Weight:5 flag=0001)

| | /-SRC----|

| | | \-TABL[SASHELP].class opt=''

| |--FROM---|

Pour en savoir plus…

Support SAS : http://support.sas.com/techsup/technote/ts553.html

SAS Global Forum :

Paper 097-2008- Par Kirk Lafler

Aide en ligne SAS : Proc SQL

Merci !

Yan IMA

yan.ima@coi.ca