SAS EG slow after changing data flow

[lucazanotti]

Hi Community, 

hope you can help on this. 
We have a dataset which recently changed in terms of data type. 

It used to have 5 fields being 7 digit maximum (0XXXXXX) but now has 18 char (a0000XXXo0909909) and this is true for all the 5 fields. 
Since the change we have experienced lots of slow cpu time on proc sort and proc sql in the same table. 

In terms of new rows this dataset (having the field type changed ) is about 2-3k rows more. Total rows is 2.3M. 
Do you kindly have any tip to increase efficiency? 

We are hosting SAS from a specific server.

I have read something on here to use the SAS > Utilities > SAS Integeration Technologies Configuration (on the client machine where you use EG from) Click on "Configure Default SAS Metadata Server > Clear Settings  > Finish, but I am not sure if this can work in the server and ha any side effect?

Thanks

Luca

[TomKari]

I think there must be something different in your scenario, or something is wrong with your environment. SAS should be able to process this volume without breaking a sweat.

To move the conversation forward, I've created the following code to create a datasets, and run a SORT and SQL on it, both fairly intensive. The dataset has 2.4 million records, 5 18 byte fields.

data DS1;
	call streaminit(200529);

	do i = 1 to 2400000;
		Field1 = "abcde" || put(int(rand('uniform') * 100000000), z8.) || "fghij";
		Field2 = "abcde" || put(int(rand('uniform') * 100000000), z8.) || "fghij";
		Field3 = "abcde" || put(int(rand('uniform') * 100000000), z8.) || "fghij";
		Field4 = "abcde" || put(int(rand('uniform') * 100000000), z8.) || "fghij";
		Field5 = "abcde" || put(int(rand('uniform') * 100000000), z8.) || "fghij";
		output;
	end;
run;

proc sort data=DS1 out=DS2;
	by Field1 Field2 Field3 Field4 Field5;
run;

proc sql noprint;
	create table DS3 as select
		* from DS1 where substr(Field3, 6, 1) > "5"
	order by Field5;
quit;

The whole thing runs in 4 seconds on my plain-jane laptop.

See what happens in your environment, and let us know.

   Tom

[lucazanotti]

Hi Tom,
many thanks for this. It runs very fast (sql proc 0.99seconds and cpu time 1.5) and proc sort 1.79s and 3.31s respectively.
I will exaplain bettr (and apologies if I did not mention before) the connection is a dns odbc to oracle.
Funny things is that in DbVisualizer and other apps the same query is fast, only SAS for some reasons does not perform well since the change.
Hope this will shed some lights.
Thanks
Luca

[TomKari]

Hi, Luca

This makes a lot of sense. Connections between different products, like from SAS to Oracle, can definitely introduce problems that result in poor performance.

You mention running the same query under DbVisualizer. Could you post the query, so we can see if there's anything that raises any flags with SAS?

Thanks,
Tom

[lucazanotti · Accepted solution]

Hi Tom,
thanks a lot for the helpful notes.
I think I figured out the solution: with a combination of increasing buffer size and using accordingly in my datastep the option (compress=YES) I have been able to achieve my goal. For future references and other people that may encounter a similar issue this article has helped me a lot.
https://support.sas.com/resources/papers/proceedings17/1401-2017.pdf

[TomKari]

I'm glad you're back in business!

That's an excellent paper. I've bookmarked it for future reference.

Best,

   Tom