BookmarkSubscribeRSS Feed
jmic_nyk
Obsidian | Level 7

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?

11 REPLIES 11
RahulG
Barite | Level 11
You should try proc cimport
jmic_nyk
Obsidian | Level 7

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.

CKjeldsen
Fluorite | Level 6

Hi

 

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

 

jmic_nyk
Obsidian | Level 7
Also my impression - tnx. What about a DS2 step?
jmic_nyk
Obsidian | Level 7

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

GertNissen
Barite | Level 11

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

jmic_nyk
Obsidian | Level 7
All suggestions in a batch job - hopefully results on monday. TNX again
GertNissen
Barite | Level 11

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

jmic_nyk
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 11 replies
  • 1968 views
  • 9 likes
  • 5 in conversation