06-21-2018 04:13 AM
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.
set result.user_permission_report; /*this is a view in Oracle*/
create table example2 as select * from result.user_permission_report;
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.
06-21-2018 04:29 AM
Have a look at the latencies between your desktop where EG is running and the SAS server. Open a cmd window and do a
06-21-2018 04:40 AM
06-21-2018 04:46 AM
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.
06-21-2018 05:48 AM
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.
06-21-2018 06:29 AM - edited 06-21-2018 06:32 AM
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;
06-21-2018 08:40 AM
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:
length display_nm $200;
2 Create an Oracle data set by a single data step (sasoprsk is the oracle library):
3. There is a view in a SAS EG library, which points to the oracle table. For example:
create view result.user_permission_report as
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!