Upload
pascal-mignot
View
105
Download
1
Embed Size (px)
Citation preview
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