PRESTANDAHÖJANDE TIPS OCH TRICKS
FÖR SAS/ACCESS
JONAS WETTERBERG
XPERIENCE 2015-09-09
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
AGENDA
•
•
•
•
•
•
Utmaningen med externa datakällor
Enkla åtgärder
Att använda multi_datasrc_opt = Option
Sessionstabeller (MS SQL Server)
SQL Pass-Through / Connect to Statement
Uppsummering och reflektioner kommentarer
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
ETT VANLIGT DET TAR ALLTID TID ATT FLYTTA DATA ÖVER ETT
SCENARIO NÄTVERK
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
DET ENKLA ENK LA FÖRBÄTTRINGAR– KEEP SELECT *
Låt bli:
data min_tabell;
set mindb.tabell_manga_kols;
run;
proc sql;
create table min_tabell
as select *
from mindb.tabell_manga_kols;
quit;
Gör:
data min_tabell;
set mindb.tabell_manga_kols
(keep=kol1 kol2);
run;
proc sql;
create table min_tabell
as select kol1, kol2
from mindb.tabell_manga_kols;
quit;
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
MER ENKELT WHERE VS SUBSETTING IF, FUNKTIONER OCH FORMAT
Låt bli:
data min_tabell;
set mindb.tabell_manga_kols;
if kol1 = ’hepp’;
run;
Hela tabellen hämtas alltid till
SAS
data min_tabell;
set mindb.tabell_manga_kols;
where put(kol1, mitt_fmt.) = ’hepp’;
run;
Egendefinerade format skickas
sällan till den externa databasen
– hela tabellen måste hämtas.
Gör:
data min_tabell;
set mindb.tabell_manga_kols
(keep=kol1 kol2);
where kol1 = ’hepp’;
run;
Databasen skickar de rader som
uppfyller villkoret
data min_tabell;
set mindb.tabell_manga_kols
(keep=kol1 kol2);
where year(kol1) = 2015;
run;
Funktionen year skickas ofta till
databasen (kolla dokumentationen
för den access engine du arbetar
med)
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
VART KÖRS KODEN HUR VALIDERAR VI VART KODEN EXEKVERAR?
options sastrace=',,,d' nostsuffix
sastraceloc=saslog;
proc print data=my_db.customerorders;
run;
options sastrace=off;
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
EXEMPEL
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
USING THE MULTI_DATASRC_OPT = OPTION
The MULTI_DATASRC_OPT = SAS/ACCESS LIBNAME statement option can
improve performance when you join a large table and a small SAS data set.
libname hivedb hadoop server=namenode
subprotocol=hive2 port=10000
schema=diacchad user="&std" pw="&stdpw"
multi_datasrc_opt=IN_CLAUSE;
libname sasdat "/workshop/DIACCHAD";
proc sql;
select q1.customer_id, q1.order_id,
q1.delivery_date, c.customer_name
from sasdat.qtr1 q1,hivedb.customer_dim c
where q1.customer_id=c.customer_id;
quit;
The small data set must be listed first
in the FROM clause.
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
hp05d05
USING THE MULTI_DATASRC_OPT = OPTION
Partial SAS Log:
HADOOP_8: Executed: on connection 1
. . . AS SELECT `CUSTOMER_DIM`.`customer_name`,
`CUSTOMER_DIM`.`customer_id`
FROM `CUSTOMER_DIM` WHERE ( ( `customer_id` IN ( 10 , 12 , 18
, 24 , 27 , 31 , 41 , 53 , 89 , 92 , 111 , 171 , 2806 , 3959 ,
17023 , 70100 ) ) )
These ID values are the distinct ID values from the
SAS data set QTR1. Only these rows passed to
SAS.
Partial SAS Log if MULTI_DATASRC_OPT not used:
HADOOP_8: Executed: on connection 1
. . . AS SELECT `CUSTOMER_DIM`.`customer_name`,
`CUSTOMER_DIM`.`customer_id`
FROM `CUSTOMER_DIM`
All rows passed to SAS.
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
hp05d05
USING THE MULTI_DATASRC_OPT = OPTION
1.
select distinct ID
from sasdat.qtr1
21, 23, …
•
3.
select …
from sasdata.qtr1 s, temptable t
where s.ID=t.ID
2.
select *
from hivedb.customer_dim
where ID IN (21,23, …)
select q1.customer_id, q1.order_id,
q1.delivery_date,
c.customer_name
from sasdat.qtr1 q1,hivedb.customer_dim c
where q1.customer_id=c.customer_id;
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
MULTI_DATASRC_OPT – TÄNK PÅ
•
Den lilla tabellen får bara 4500 unika värden
• Olika databashanterare klarar av olika antal värden i en in-statement
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
SESSIONSTABELLER – ETT ALTERNATIV TILL MULTI_DATASRC_OPT
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
SQL PASS-THROUGH
proc sql;
connect to hadoop
(server=namenode subprotocol=hive2
schema=diacchad user="&std");
select *
from connection to hadoop
(select employee_name,salary
from salesstaff
where emp_hire_date between
'2011-01-01' and '2011-12-31'
);
disconnect from hadoop;
quit;
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
JOINA ÖVER OLIKA LIBNAMES
Vart sker joinen?
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
ALL DATA HÄMTAS TILL SAS OCH PROCESSAS DÄR
Notera: Ett felmeddelande genereras
(fast det är inget fel).
Är det små tabeller som i exemplet
spelar det ingen större roll
Ta med andra ord bort denna option
innan kod produktionssätts.
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
VI GÖR EN PASS-THROUGH ISTÄLLET
Skickas till MS SQL Server
Notera att databasscheman är relativa MS SQL
(dbo är adderat)
Joinen sker i databasen och vi får enbart tillbaka resultatet
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.
SAMMANFATTNING
•
•
•
•
•
•
I början av projektet är mindre volymer vanligt – därför kan man missa framtida
prestandaproblem.
Överarbeta inte. Några sekunder extra i en nattligt batchjobb har sällan någon
betydelse.
I ett användargränssnitt spelar extra sekunder stor roll
Kolla din kod sastrace funktionen – i ett tidigt skede av utvecklingen.
Tänk på: sql passthrough kommer nästan alltid göra koden databasberoende. Validera
att detta är ett accepterat designbeslut.
Glöm inte att se igenom libname statement, READBUFF=, INSERTBUFF= bulkload
etc. Kan ge stora förbättringar om det är rätt uppsatt
Company Confidential - For Internal Use Only
Copyright © 2015, SAS Institute Inc. All rights reserved.