topic Re: slow view performance in a sas data set created from Oracle view in SAS Enterprise Guide

slow view performance in a sas data set created from Oracle view

dsdsdssss — Thu, 21 Jun 2018 08:13:27 GMT

Hi all,

I'm experiencing pretty slow performance when opening even a small dataset that has been created by either a data step or a proc sql from an Oracle view. I can't imagine what makes it so slow when I open it and try to scroll down.

For example:

data example1;

set result.user_permission_report; /*this is a view in Oracle*/

run;　

proc sql;

create table example2 as select * from result.user_permission_report;

quit;

My aim is to give a faster experience for users by feeding it's data into SAS. The table is not big (~4000 rows and ~40columns). I tried to create the table in the local work or on the server, but the situation is the same.

Do you have any suggestions? It looks as if the data set is still functioning as a view, but how can I create a plain, raw dataset from these data then? The situation is the same in EG. 5.1 or 7.1 (32bit). SAS version is 9.4m1.

Thank you,

Barnabas

Re: slow view performance in a sas data set created from Oracle view

Kurt_Bremser — Thu, 21 Jun 2018 08:29:40 GMT

Have a look at the latencies between your desktop where EG is running and the SAS server. Open a cmd window and do a

ping servername

Re: slow view performance in a sas data set created from Oracle view

dsdsdssss — Thu, 21 Jun 2018 08:40:13 GMT

It seems OK.
Minimum: 0 millisec
Maximum: 1 millisec

I don't have this problem with other SAS tables on the server (these are not created from an Oracle view)

Re: slow view performance in a sas data set created from Oracle view

Kurt_Bremser — Thu, 21 Jun 2018 08:46:39 GMT

If you always run the code before you view the dataset, then the Oracle view is executed each time. To circumvent this, create a permanent SAS dataset in a library that is accessible by all users who need that data.

Re: slow view performance in a sas data set created from Oracle view

andreas_lds — Thu, 21 Jun 2018 09:48:46 GMT

Using

options fullfstimer;

before the code will add some more information to the log, that might reveal the component responsible for the long runtime. If "real time", e.g. is much higher than "cpu time" the sas process waits for some other process to finish.

Re: slow view performance in a sas data set created from Oracle view

Patrick — Thu, 21 Jun 2018 10:32:21 GMT

@dsdsdssss

If accessing the Oracle view directly then there could be latency in establishing the connection to Oracle as well as time required to move the data from Oracle to SAS over the network.

....but as you write you're experiencing the same issue also when loading the data first into SAS Work and then browse the Work table that can't really be the explanation.

And then you write also that you don't experience such issues with other tables in Work.

Hmmm.... Could it be that these 40 columns are all character with a really big length? What happens if you create and access the table in Work with the compress option?

proc sql;
create table example2(compress=yes) as 
select * from result.user_permission_report;
quit;

Re: slow view performance in a sas data set created from Oracle view

dsdsdssss — Thu, 21 Jun 2018 12:40:52 GMT

Thanks for the suggestion, the problem stems from the lengths in the Oracle table! It's a pretty weird behaviour though...

Here is what I did:

1. Create a SAS data set in EG, and set the lengths of the data set before the SET clause, because the original length of the variabes are longer. For example display_nm is originally 1024 long:

data user_permission_report;

length display_nm $200;

set source;

run;

2 Create an Oracle data set by a single data step (sasoprsk is the oracle library):

data sasoprsk.user_permission_report;

set user_permission_report;

run;

3. There is a view in a SAS EG library, which points to the oracle table. For example:

proc sql;

create view result.user_permission_report as

select *

from sasoprsk.user_permission_report;

quit;

Well, if I check the length in the three different tables, comes the magic:

- in the 1st data set the length is 200

- in the 2nd, if I log in to Oracle and check the length of display_nm, it's VARCHAR2(800)

- if I check the length in the SAS VIEW in EG by right click-> Properties-> Columns, it shows 1024!

I wish I could to this with my bank account. 🙂

I guess it might be some kind of metadata collision, or what?

Thank you for the help!