db connector sas viya <--> oracle slow download

[acordes]

I'm having problems with the download velocity of tables from oracle.

after establishing successfully the connection, the data step to save the table runs successfully but very slowly.

Meanwhile the upload of a comparable table is 50 times faster.

I have tried an alternative download process with the following code playing around with the options like numreadbuffers, but it doesn't improve significantly.

caslib oracaslib datasource=(                                         

              srctype="oracle",

              uid='SAS',

              pwd="xxxxxxxxx",

              path=" xxxxxx"

);

proc casutil;   load casdata="FACT_MONTHLY_RENEWALS" incaslib="oracaslib" outcaslib="AKAIKE"      

 casout="FACT_MONTHLY_RENEWALS" dataSourceOptions=(numreadbuffers=10, numreadnodes=1, row_array_size=1000000);             quit;

LIBNAME myora ORACLE PATH=xxxxxxx USER=SAS PASSWORD='xxxxxxxxxxx';
/* slow */
data AKAIKE.FACT_MONTHLY_RENEWALS(promote=yes);
set myora.FACT_MONTHLY_RENEWALS;
run;

/* fast */
options casdatalimit=all;
data myora.SCORE_COLORS(INSERTBUFF=10000 DBCOMMIT=50000);
SET PUBLIC.HUERTO1(where=(score_date gt '31mar2021'd) keep=id score_date  r_RENEWAL_PROB r_equity);
rename ID=UNIQUEID;
RUN;

[Patrick]

From the looks of it you're downloading a full facts table to SAS but you're most likely uploading a much lower data volume. If so then that would explain the performance difference you observe.

You could "play" with readbuff and depending on Oracle settings download start only when the Oracle side query completed or already while it's running (nothing you can change, needs an Oracle DBA) - but in the end of the day it's mainly about the number of rows and record length of a row.

[dlawto2]

Also Viya is trying to make the results pretty, I had a report that took 2-4 sec in base SAS but ran for several hours in Viya and turning ODS off when reading the data in and then turn it back on, like below fixed the issue:

ods noresults;



proc sql;

connect to SQLSVR as mydb

(datasrc="DSN" user=UID password="Password" readbuff=5000);

create table experimental as select * from connection to mydb

(select * from table;)

quit;



ods results;

[acordes]

thanks, but I don't know how to connect.

77 ODS NORESULTS;
78
79 proc sql;
80
81 connect to oracle as mydb
82
83 (datasrc="FACT_OPE_FORM" user=SAS password=XXXXXXXXXXXXXXXXXX readbuff=5000);
ERROR: Invalid option name datasrc.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
84
85 create table DNA.experimental as select * from connection to mydb
86
87 (select * from table;)
88
89 quit;
NOTE: Statement not executed due to NOEXEC option.
90
91
92
93 ods results;

[dlawto2]

Well you will need to find the DSN name out from someone or you can look on the tnsnames.ora file. The DSN name would be unique to your site/database.
On Linux usually under /u01/app/oracle/admin/network

[acordes]

thanks, taking the same table for comparison in download and upload mode here comes the performance result:

okay, this time is only factor 3...

78 data AKAIKE.test_downlaod(promote=yes);
79 set myora.fact_ope_form;
80 run;
NOTE: The data set TEST_DOWNLAOD was promoted.
NOTE: There were 1518039 observations read from the data set MYORA.FACT_OPE_FORM.
NOTE: The data set AKAIKE.TEST_DOWNLAOD has 1518039 observations and 179 variables.
NOTE: DATA statement used (Total process time):
real time 11:01.08
cpu time 2:38.83

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 1518039 observations read from the data set AKAIKE.TEST_DOWNLAOD.
NOTE: The data set MYORA.TEST_UPLOAD has 1518039 observations and 179 variables.
NOTE: DATA statement used (Total process time):
real time 4:23.31
cpu time 1:30.62

[Patrick · Accepted solution]

Looks like you're comparing apples with apples. I've missed that your'e in Viya/CAS where I still don't have sufficient experience connecting to Oracle so just some general thoughts here.

1. To get a bit more SAS log set options

options compress=yes fullstimer msglevel=i SASTRACE=',,t,sa' SASTRACELOC=SASLOG NOSTSUFFIX;

Not sure if I've got the SASTRACE arguments right. You might need to amend for useful information: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0732u1mr57ycrn1urf24gzo38sc.htm 

2. Make sure you set readbuff to something bigger than the default.

3. And just as a thought: You might want to check to "play" with options like DBCLIENT_MAX_BYTES documented here. CAS got varchar columns so less important there likely but because a SAS length for characters defines bytes and with UTF-8 a byte can take up-to 4 bytes if options like these are not set appropriately for the DB you connect to, you can end up with character lengths on the SAS side 4 times the number of chars defined on the DB side.