SAS EG 8.3 Performace Problem

[ern_23]

Dear SAS Community,

As SAS EG 8.3 user (SAS Version 9.4) , I am having performance problem which has serious effect on my daily job. I can drop down my problems as follows;

1- When I submit code, SASApp keeps refreshing itself. It seems SASApp tries to connect all the time and in my opinion this causes longer running times. In addition to that when I am doing nothing on SAS , it also refreshing itself all the time.

2- When I submit the same code on SQL side (i.e Oracle, MsSQL) it returns in seconds while the same code takes 20 - 30 minutes to complete(depends complexity of the code , please consider I also create indexes prior to table joins ).

3- When I try to cancel the process since it takes too much time of mine, cancellation of any simple process also takes minimum 1 to 10 minutes. 

I'd be appreciated if anyone can provide me guidance / couple of ideas to fix above issues or has faced similar problems and fixed somehow.

Thank you.

Eren

[SASKiwi]

This isn't normal SAS EG 8.3 behaviour. I use this version myself and performance is very acceptable. I suggest you open a track with SAS Tech Support as this might take some diagnosing, including turning on EG application logging. Also do you have access to another PC with EG 8.3 and does it perform the same?

[ern_23]

Thank you for replying.

Yes , my colleagues also have the same performance problems. I enabled application logging on EG and shared logs with SAS Tech Support Team. They think the problem may caused by Oracle. But I do not think the problem is related to it as I m having same problems when I work on temporary tables on Work lib.

[SASKiwi]

In my experience we get fairly similar performance when querying external databases with SAS versus the native SQL tools. What I suggest you try is turning one of your slow external database queries into a simple row count like this:

proc sql;
  select count(*) from
  << put the rest of your slow database query here >>
 ;
quit;
 

If this runs a lot faster than selecting a large result set then you can pretty much guarantee that you have a slow network between the database and SAS.

[AlanC]

I suspect DB or NAS issue, however...

...if you need to go low level to determine the slowdown, consider downloading sysinternals from Microsoft. Use the tools procmon (very low level access) and procexe to see the loaded dlls. Watch a video or read a blog ,especially on procmon, to know how to use it. It will tell you, in great detail, where performance is being hit. Procexe is like task manager on steroids. It will tell you what is consuming the system when EG is used. It may be an ancillary program.

Procmon will capture EVERYTHING on your machine. Filter. filter, filter until you find clues.  Ignore registry read/writes, for example (use buttons at top, right-click items to Exclude). If it is is a low level thing, you can rule it in/out.

[ern_23]

Thank you all for replying. Yesterday I shared detailed log files with SAS Support Team and now waiting their turn on the issue. If we could not make any progress, I'll take your recommendations into account. Also I'll let you know if we get any solution from the Support Team