BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HrZiller
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

 

queryprop.png

 

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

 

tableprop.png

 

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.

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!

View solution in original post

2 REPLIES 2
ChrisHemedinger
Community Manager

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

 

queryprop.png

 

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

 

tableprop.png

 

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.

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!
HrZiller
Obsidian | Level 7

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 🙂

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1300 views
  • 1 like
  • 2 in conversation