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.
... View more
