Use of performance option DBKey with SAS EG when using point and click

[HrZiller]

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

[ChrisHemedinger · Accepted solution]

In the Query Builder, right-click on the table name in the list and select Properties.

In the Table properties window, add the options you need to the Options field at the bottom.

That will insert these data set options into the syntax for the query.  You can also use explicit pass-through, if you want, by selecting Options (from the Query builder toolbar), Options for this Query, then the Pass-through tab -- in case that helps.

[HrZiller]

Hi Chris,

can't believe I didn't see this in the window.... must get new glasses! Thanks a lot - huge performance difference with this on.

Hope things are well in NC after the hurricane!

Cheers

Jan

PS: my ninja-tech skills must be failing - even after +10 years in SAS support 🙂