Load data into CAS with FedSql query...or proc sql query?

AsSASsin — Wed, 02 Dec 2020 13:51:13 GMT

Hi all,

from all documentation read online I found that the FedSql is better than the proc sql in terms of performance.

I have an Oracle DB and I have to create a table with a join between two tables on my DB.

I used the FedSql to retrieve the data with varchar types instead of proc sql.

The problem is that my fedsql is solwer that proc sql.

The structure of my query is:

proc fedsql sessref=casauto;
create table oralib.tmp_fm{options replace=true replication=0} as
select * from connection to oralib( mysqlquery);

quit;
NOTE: Executing action 'fedSql.execDirect'.
WARNING: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
WARNING: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
NOTE: Table TMP_FM was created in caslib ORALIB with 2391025 rows returned.
NOTE: Action 'fedSql.execDirect' used (Total process time):
NOTE: real time 175.846113 seconds
NOTE: cpu time 26.078261 seconds (14.83%)
NOTE: total nodes 8 (64 cores)
NOTE: total memory 754.00G
NOTE: memory 1.35G (0.18%)
MPRINT(MACROUPDATE): quit;
NOTE: PROCEDURE FEDSQL used (Total process time):
real time 2:55.88
cpu time 3.55 seconds

With the proc sql:

proc sql;
connect to oracle (user="&dbuser" password="&dbpwd" path="&dbPath" DBCLIENT_MAX_BYTES=1 READBUFF=1000);
create table oralib.tmp_fm as
select * from connection to oracle(mysqlquery);

disconnect from oracle;

quit;

NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE: real time 0.018614 seconds
NOTE: cpu time 0.017000 seconds (91.33%)
NOTE: total nodes 8 (64 cores)
NOTE: total memory 754.00G
NOTE: memory 2.72M (0.00%)
NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE: real time 0.019678 seconds
NOTE: cpu time 0.014038 seconds (71.34%)
NOTE: total nodes 8 (64 cores)
NOTE: total memory 754.00G
NOTE: memory 2.72M (0.00%)
NOTE: Executing action 'table.columnInfo'.
NOTE: Action 'table.columnInfo' used (Total process time):
NOTE: real time 0.023280 seconds
NOTE: cpu time 0.016972 seconds (72.90%)
NOTE: total nodes 8 (64 cores)
NOTE: total memory 754.00G
NOTE: memory 2.98M (0.00%)

ORACLE_13: Executed: on connection 2
SELECT statement ORACLE_12

NOTE: Executing action 'table.addTable'.
NOTE: Table ORALIB.TMP_FM created, with 2391025 rows and 19 columns.

Summary Statistics for ORACLE are:
Total row fetch seconds were: 73.320074
Total SQL execution seconds were: 0.009120
Total SQL prepare seconds were: 0.011830
Total seconds used by the ORACLE ACCESS engine were 77.744559

MPRINT(MACROUPDATE): disconnect from oracle;
MPRINT(MACROUPDATE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1:17.81
cpu time 8.99 seconds

This is my oralib declaration:

caslib oralib notactive
sessref=casauto
path="/data/"
libref=oralib
datasource=(
srctype="oracle",
username="&dbUser.",
password="&dbPwd.",
schema="&dbSchema.",
path="&dbPath.",
/*NumReadNodes=&nodes., NumWriteNodes=&nodes.*/
);

I tried with NumReadNodes and NumWriteNodes but I have problems with the oracle syntax(in example the sysdate value).

Is there a mistake or some configuration to enhance the performance of the fedsql create table in a cas session?

Thank you all.

topic Load data into CAS with FedSql query...or proc sql query? in SAS Viya

Load data into CAS with FedSql query...or proc sql query?