topic Re: Q: How to speedUp load to a remote work-lib? in SAS Community Nordic

Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 11:18:37 GMT

Hi folks!

In traditional SAS/Connect

I need to copy a dataset from a client to a remote server using a remote libname

So far:

data remote.mydata;

set local.mydata;

run;

is slow, so is Proc SQL

Ihe datastep only runs on one core (out of 40 available) - right? But PROC SQL should be able to run on eg 4 cores. But the elapse time in the saslog is almost the same. THREADS is enabled and CPUCOUNT=4 in config.

Why is SQL not faster than Data?

Bandwith is about 10Gb/s

Any ideas?

Re: Q: How to speedUp load to a remote work-lib?

RahulG — Fri, 22 Sep 2017 11:32:18 GMT

You should try proc cimport

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 11:40:46 GMT

Hrmm...

according to the manual it

"Restores a transport file created by the CPORT procedure."

My data is not a transportfile, but a plain SAS dataset.

Re: Q: How to speedUp load to a remote work-lib?

CKjeldsen — Fri, 22 Sep 2017 11:58:15 GMT

According to this, it looks like the PROC SQL only uses multi-threading for group by and order by clauses:

http://support.sas.com/rnd/scalability/procs/index.html

Another issue could be the bandwidth not being 10Gb/s all the way between the two servers, but as the cpu time in the log is significant compared to real time in both cases, I guess it has to do with single-threading.

Not sure how to overcome the issue apart from hardcode the input into chunks, and transfer them seperately in parallel.

Christian

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 12:01:09 GMT

Also my impression - tnx. What about a DS2 step?

Re: Q: How to speedUp load to a remote work-lib?

KirstenHaubjerg — Fri, 22 Sep 2017 12:05:00 GMT

Have you tried proc upload?

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 12:06:23 GMT

No - I suppose it also runs single threaded. But I will try - just in case 🙂

Re: Q: How to speedUp load to a remote work-lib?

GertNissen — Fri, 22 Sep 2017 12:20:00 GMT

Hi @jmic_nyk

Have you tried to use proc copy ?

The data step and sql will perform 'twice' as much work, as they also put the data into the Program Data Vector.

Perhaps CLONE, BUFSIZE= or COMPRESS the data before Proc Copy.

/Gert

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 12:30:28 GMT

Will try - tnx.

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Fri, 22 Sep 2017 12:31:53 GMT

All suggestions in a batch job - hopefully results on monday. TNX again

Re: Q: How to speedUp load to a remote work-lib?

GertNissen — Mon, 25 Sep 2017 15:22:05 GMT

Have a nice weekend - looking forward to read about your experience on performance. /Gert

Re: Q: How to speedUp load to a remote work-lib?

jmic_nyk — Sun, 25 Feb 2018 11:49:02 GMT

We'll wrap this question up at out SASNineNetwork meeting at Thursday in Copenhagen. I have some conclusions - but not all the way around the topic.