Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 5

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

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

Super User
Posts: 10,623

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

Posted in reply to dsdsdssss

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

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

Posted in reply to KurtBremser
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)
Super User
Posts: 10,623

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

Posted in reply to dsdsdssss

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 631

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

Posted in reply to dsdsdssss

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.

Respected Advisor
Posts: 4,802

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

[ Edited ]
Posted in reply to dsdsdssss

@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;

 

Occasional Contributor
Posts: 5

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

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. Smiley Happy

 

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

 

Thank you for the help!

Ask a Question
Discussion stats
  • 6 replies
  • 121 views
  • 0 likes
  • 4 in conversation