load dataset from client to CAS

[dipand]

Hello,
i have a dataset with 16.000.000 record and 136 column. I load this dataset every day on cas. My procedure is this:

data hseq.HSEQ_INSP_FIND_TEMP1;
set staging.HSEQ_INSP_FIND_TEMP
;
run;

proc casutil incaslib="hseq" outcaslib="hseq";
droptable casdata="HSEQ_INSP_FIND_TEMP" quiet;
promote casdata="HSEQ_INSP_FIND_TEMP1" casout="HSEQ_INSP_FIND_TEMP";
save casdata="HSEQ_INSP_FIND_TEMP" casout="HSEQ_INSP_FIND_TEMP" replace;
quit;

but is very slowly about over two hours.
is there another way for load dataset in cas?

Thank you,

A.

[smm662002]

Hi,
I don't know if you still need help, but a diffrent way to load data to CAS is to use SAS DI Studio jobs scheduled out of working hours (if possible).

Alternativeley you can use a proc fedsql to load the data but source and destination have to be from caslibs. After the upload you can use proc casutil or proc cas to promote the table.
Regards,
Smm662002

[dipand]

unfortunately my dataset is on sas 9.4 and i must load they on the cas, because i read this data from sap/bw. the sas job start all day in different hours after finish the job sap/bw and the time for load this dataset to very large and i can't major performance

[LinusH]

This post have some suggestions how to trigger a parallel load from SAS datasets:

https://blogs.sas.com/content/sgf/2019/10/17/how-to-parallel-load-and-compress-a-sas-cloud-analytic-...

If your process is critical, and you have an MPP CAS, you can always explore these options:

https://communities.sas.com/t5/SAS-Communities-Library/4-Approaches-for-Parallel-Data-Loading-to-CAS...

[Patrick]

Just as a comment to the first link @LinusH shared to an article from 2019: I made the experience of negative query performance impact when compressing a CAS table (using compress=true).

If you're on a very recent Viya version then use memoryFormat="DVR" instead. https://communities.sas.com/t5/SAS-Communities-Library/Viya-2020-1-CAS-Duplicate-Value-Reduction/ta-... 

[SASKiwi]

I strongly suspect that the poor performance is caused by large default character column lengths being defined on the SAS dataset being read from SAP/BW. This results in the dataset size being much larger than it should be. In SAS 9.4 data libraries on disk this is not a problem as you can apply the SAS option COMPRESS to get rid of the extra space efficiently. It is advisable to not compress in-memory tables as it adversely affects performance. I know it is tedious but you can resize all long character columns using the LENGTH statement to reduce space in the on-disk dataset which should improve load times into memory a lot.   

[DerylHollick · Accepted solution]

I have seen this method load faster.  You could try it instead of the DATA step.

%let path=%sysfunc(pathname(staging));
proc cas;
   table.upload / path="&path/HSEQ_INSP_FIND_TEMP.sas7bdat" casout={caslib="hseq" name="HSEQ_INSP_FIND_TEMP1"};
quit;

Out of the 2 hours, how much of that time is the actual load step?

And are all of the 136 columns needed?  Can you drop any?

[dipand]

my customer don't want that delete any columns, but i try your istruction. Thank you so much