hi there,
we are using SAS EG for adhoc users here at the customer I am working for. DW is stored in SQL Server. Simple joins create SAS tables, but when joining back to SQL server we sometimes get slow performance, because we are joining a SAS table with a DBMS table. This performance can be much better when using the DBkey option.
I am aware that we can modify the work location to SQL Server as well - but this not the solution here in this situation.
EG Release: 7.15 HF2 (7.100.5.6112) (64-bit)
Code generated by EG:
PROC SQL;
CREATE TABLE WORK.bygning AS
SELECT t2.*
FROM WORK.SFE t1
INNER JOIN _03_PLUS.Bygning t2
ON (t1.SFE_ID_ICE = t2.SFE_ID_ICE)
ORDER BY t2.Bygningsnummer,
t2.Bygning_ID_ICE,
t2.Virkning_fra
;
QUIT;
SAS log result:
NOTE: PROCEDURE SQL used (Total process time):
real time 2:56.05
cpu time 2:55.75
Modified SAS EG code - added: (dbkey=sfe_id_ice)
PROC SQL;
CREATE TABLE WORK.bygning AS
SELECT t2.*
FROM WORK.SFE t1
INNER JOIN _03_PLUS.Bygning(dbkey=SFE_ID_ICE) t2
ON (t1.SFE_ID_ICE = t2.SFE_ID_ICE)
ORDER BY t2.Bygningsnummer,
t2.Bygning_ID_ICE,
t2.Virkning_fra
;
QUIT;
SAS log result:
NOTE: PROCEDURE SQL used (Total process time):
real time 0.27 seconds
cpu time 0.01 seconds
Difference with/without DBkey: 3min vs. less than 1 sec.
So my specific question is:
How can I get this DBkey option added when using EG standard join functionality?
Thanks
Jan
Full SAS log below:
1 The SAS System 08:28 Tuesday, September 11, 2018
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='\\ccta.dk\dfssystem\HomeFolders\w5\W92285\SAS\Find data til kommune-ejendomsnr - v1.egp';
6 %LET _CLIENTPROJECTPATHHOST='C780320';
7 %LET _CLIENTPROJECTNAME='Find data til kommune-ejendomsnr - v1.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options sastrace=',,,d' sastraceloc=saslog;
27 PROC SQL;
28 CREATE TABLE WORK.bygning AS
29 SELECT t2.*
30 FROM WORK.SFE t1
31 INNER JOIN _03_PLUS.Bygning(dbkey=SFE_ID_ICE) t2
32 ON (t1.SFE_ID_ICE = t2.SFE_ID_ICE)
33 ORDER BY t2.Bygningsnummer,
34 t2.Bygning_ID_ICE,
35 t2.Virkning_fra
36 ;
OLEDB: AUTOCOMMIT turned ON for connection id 3
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 3
OLEDB: AUTOCOMMIT turned OFF for connection id 3
OLEDB: AUTOCOMMIT turned ON for connection id 3
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 3
OLEDB_10: Prepared: on connection 3
SELECT * FROM "_03_PLUS"."Bygning"
OLEDB: AUTOCOMMIT turned ON for connection id 4
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 4
OLEDB: AUTOCOMMIT turned OFF for connection id 4
OLEDB_11: Prepared: on connection 3
SELECT "SFE_ID_ICE", "Bygning_ID_ICE", "ICE_Registreret", "Grund_ID_ICE", "Adresse_ID_ICE", "Jordstykke_ID_ICE", "Kommune_ID_ICE",
"Bygningspunkt_ID_ICE", "Ejerlejlighed_ID_ICE", "BFG_ID_ICE", "Bygning_ID", "Kommunenummer", "Ejendomsnummer", "Bygningsnummer",
"BygningensAnvendelse", "AntalLejlighederMedKoekken", "AntalLejlighederUdenKoekken", "Opfoerelsesaar",
"EjendomsnummerForEjerlejlighed", "OmTilbygningsaar", "EjerlejlighedsNummer", "MidlertidigOprettelseFuldforelse",
"KodeForMereEndEnLejlighed", "DatoForMidlertidigOpfoertBygning", "Vandforsyning", "Afloebsforhold", "YdervaeggensMateriale",
"Tagdaekningsmateriale", "SuplYdervaeggensMateriale", "SuplTagdaekningsMateriale", "AsbestholdigtMateriale",
"KildeTilBygningensMaterialer", "SamletBygningsareal", "BygningensSamledeBoligAreal", "BygningensSamledeErhvervsAreal",
2 The SAS System 08:28 Tuesday, September 11, 2018
"BebyggetAreal", "ArealIndbyggetGarage", "ArealIndbyggetCarport", "ArealIndbyggetUdhus", "ArealIndbyggetUdestueEllerLign",
"SamlArealAfLukOverdaekPaaBygning", "ArealAfAffaldsrumITerraenniveau", "AndetAreal", "ArealAfOverdaekketAreal",
"ArealAabneOverdaekPaaBygningSaml", "AdgangsAreal", "BeregningsprincipCarportAreal", "BygningsarealerKilde", "AntalEtager",
"AfvigendeEtager", "Varmeinstallation", "Opvarmningsmiddel", "SupplerendeVarme", "Sikringsrumpladser", "Fredning",
"BevaringsvaerdighedReference", "StormraadetsOversvoemmSelvrisiko", "DatoForRegistFraStormraadet", "Byggeskadeforsikringsselskab",
"DatoForByggeskadeforsikring", "Udledningstilladelse", "OmfattetAfByggeskadeforsikring", "MedlemskabAfSpildevandsforsyning",
"EjendommensEjerforholdskode", "PaabudVedrSpildevandsafledning", "FristVedrSpildevandsafledning", "TilladelseTilUdtraeden",
"DatoForTilladelseTilUdtraeden", "TilladAlternBortskafEllerAfled", "DtTilladAlternBortskafEllerAfled", "Sikklas", "TinglystAreal",
"Registreret_fra", "Registreret_til", "Virkning_fra", "Virkning_til", "DataKilde", "Md5", "NyRaekke" FROM "_03_PLUS"."Bygning"
WHERE ((("SFE_ID_ICE"= ? ) OR (("SFE_ID_ICE" IS NULL ) AND ( ? IS NULL ))))
NOTE: Compressing data set WORK.BYGNING increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.BYGNING created, with 10 rows and 79 columns.
37 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.34 seconds
cpu time 0.06 seconds
38 PROC SQL;
39 CREATE TABLE WORK.bygning AS
40 SELECT t2.*
41 FROM WORK.SFE t1
42 INNER JOIN _03_PLUS.Bygning t2
43 ON (t1.SFE_ID_ICE = t2.SFE_ID_ICE)
44 ORDER BY t2.Bygningsnummer,
45 t2.Bygning_ID_ICE,
46 t2.Virkning_fra
47 ;
OLEDB: AUTOCOMMIT turned ON for connection id 3
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 3
OLEDB: AUTOCOMMIT turned OFF for connection id 3
OLEDB: AUTOCOMMIT turned ON for connection id 3
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 3
OLEDB_12: Prepared: on connection 3
SELECT * FROM "_03_PLUS"."Bygning"
OLEDB: AUTOCOMMIT turned ON for connection id 4
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 4
OLEDB: AUTOCOMMIT turned OFF for connection id 4
OLEDB_13: Prepared: on connection 3
SELECT "SFE_ID_ICE", "Bygning_ID_ICE", "ICE_Registreret", "Grund_ID_ICE", "Adresse_ID_ICE", "Jordstykke_ID_ICE", "Kommune_ID_ICE",
"Bygningspunkt_ID_ICE", "Ejerlejlighed_ID_ICE", "BFG_ID_ICE", "Bygning_ID", "Kommunenummer", "Ejendomsnummer", "Bygningsnummer",
"BygningensAnvendelse", "AntalLejlighederMedKoekken", "AntalLejlighederUdenKoekken", "Opfoerelsesaar",
"EjendomsnummerForEjerlejlighed", "OmTilbygningsaar", "EjerlejlighedsNummer", "MidlertidigOprettelseFuldforelse",
"KodeForMereEndEnLejlighed", "DatoForMidlertidigOpfoertBygning", "Vandforsyning", "Afloebsforhold", "YdervaeggensMateriale",
"Tagdaekningsmateriale", "SuplYdervaeggensMateriale", "SuplTagdaekningsMateriale", "AsbestholdigtMateriale",
"KildeTilBygningensMaterialer", "SamletBygningsareal", "BygningensSamledeBoligAreal", "BygningensSamledeErhvervsAreal",
"BebyggetAreal", "ArealIndbyggetGarage", "ArealIndbyggetCarport", "ArealIndbyggetUdhus", "ArealIndbyggetUdestueEllerLign",
"SamlArealAfLukOverdaekPaaBygning", "ArealAfAffaldsrumITerraenniveau", "AndetAreal", "ArealAfOverdaekketAreal",
"ArealAabneOverdaekPaaBygningSaml", "AdgangsAreal", "BeregningsprincipCarportAreal", "BygningsarealerKilde", "AntalEtager",
"AfvigendeEtager", "Varmeinstallation", "Opvarmningsmiddel", "SupplerendeVarme", "Sikringsrumpladser", "Fredning",
"BevaringsvaerdighedReference", "StormraadetsOversvoemmSelvrisiko", "DatoForRegistFraStormraadet", "Byggeskadeforsikringsselskab",
"DatoForByggeskadeforsikring", "Udledningstilladelse", "OmfattetAfByggeskadeforsikring", "MedlemskabAfSpildevandsforsyning",
3 The SAS System 08:28 Tuesday, September 11, 2018
"EjendommensEjerforholdskode", "PaabudVedrSpildevandsafledning", "FristVedrSpildevandsafledning", "TilladelseTilUdtraeden",
"DatoForTilladelseTilUdtraeden", "TilladAlternBortskafEllerAfled", "DtTilladAlternBortskafEllerAfled", "Sikklas", "TinglystAreal",
"Registreret_fra", "Registreret_til", "Virkning_fra", "Virkning_til", "DataKilde", "Md5", "NyRaekke" FROM "_03_PLUS"."Bygning"
OLEDB_14: Executed: on connection 3
SELECT "SFE_ID_ICE", "Bygning_ID_ICE", "ICE_Registreret", "Grund_ID_ICE", "Adresse_ID_ICE", "Jordstykke_ID_ICE", "Kommune_ID_ICE",
"Bygningspunkt_ID_ICE", "Ejerlejlighed_ID_ICE", "BFG_ID_ICE", "Bygning_ID", "Kommunenummer", "Ejendomsnummer", "Bygningsnummer",
"BygningensAnvendelse", "AntalLejlighederMedKoekken", "AntalLejlighederUdenKoekken", "Opfoerelsesaar",
"EjendomsnummerForEjerlejlighed", "OmTilbygningsaar", "EjerlejlighedsNummer", "MidlertidigOprettelseFuldforelse",
"KodeForMereEndEnLejlighed", "DatoForMidlertidigOpfoertBygning", "Vandforsyning", "Afloebsforhold", "YdervaeggensMateriale",
"Tagdaekningsmateriale", "SuplYdervaeggensMateriale", "SuplTagdaekningsMateriale", "AsbestholdigtMateriale",
"KildeTilBygningensMaterialer", "SamletBygningsareal", "BygningensSamledeBoligAreal", "BygningensSamledeErhvervsAreal",
"BebyggetAreal", "ArealIndbyggetGarage", "ArealIndbyggetCarport", "ArealIndbyggetUdhus", "ArealIndbyggetUdestueEllerLign",
"SamlArealAfLukOverdaekPaaBygning", "ArealAfAffaldsrumITerraenniveau", "AndetAreal", "ArealAfOverdaekketAreal",
"ArealAabneOverdaekPaaBygningSaml", "AdgangsAreal", "BeregningsprincipCarportAreal", "BygningsarealerKilde", "AntalEtager",
"AfvigendeEtager", "Varmeinstallation", "Opvarmningsmiddel", "SupplerendeVarme", "Sikringsrumpladser", "Fredning",
"BevaringsvaerdighedReference", "StormraadetsOversvoemmSelvrisiko", "DatoForRegistFraStormraadet", "Byggeskadeforsikringsselskab",
"DatoForByggeskadeforsikring", "Udledningstilladelse", "OmfattetAfByggeskadeforsikring", "MedlemskabAfSpildevandsforsyning",
"EjendommensEjerforholdskode", "PaabudVedrSpildevandsafledning", "FristVedrSpildevandsafledning", "TilladelseTilUdtraeden",
"DatoForTilladelseTilUdtraeden", "TilladAlternBortskafEllerAfled", "DtTilladAlternBortskafEllerAfled", "Sikklas", "TinglystAreal",
"Registreret_fra", "Registreret_til", "Virkning_fra", "Virkning_til", "DataKilde", "Md5", "NyRaekke" FROM "_03_PLUS"."Bygning"
NOTE: Compressing data set WORK.BYGNING increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.BYGNING created, with 10 rows and 79 columns.
48 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 2:55.65
cpu time 2:55.35
49
50 GOPTIONS NOACCESSIBLE;
51 %LET _CLIENTTASKLABEL=;
52 %LET _CLIENTPROCESSFLOWNAME=;
53 %LET _CLIENTPROJECTPATH=;
54 %LET _CLIENTPROJECTPATHHOST=;
55 %LET _CLIENTPROJECTNAME=;
56 %LET _SASPROGRAMFILE=;
57 %LET _SASPROGRAMFILEHOST=;
58
59 ;*';*";*/;quit;run;
60 ODS _ALL_ CLOSE;
61
62
63 QUIT; RUN;
64
