BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

Hello SAS Experts,

I am without a clue why the performance in SAS is so bad. I run the SQL via EG 7.1 on our SAS Server using SAS/ACCESS for Oracle.

The SQL Script itself selects abou 10-12m rows in Oracle in less than 30 minutes (Oracle SQL Developer) if I submit the same

SQL script via EG it just does not finish. I come back the next day and its still running. Something is really strange.

Any ideas would be amazing!



The below SQL embedded in the proc sql takes 3 minutes to run on the Oracle DB and does not finish after 3 hours within a SAS program running on a SASApp Server. why is that? 10m rows.


proc sql;
   connect to oracle as oracleadb (user=xxxx password='xxxx' path=DMP_ADB buffsize=50000);
     create table SASDWH.sasdwh_fact as
     (
       select *
           from connection to oracleadb          
             (

                /* takes 3 minutes to run in Oracle, 10m rows */
                SELECT
                       ....
            )  /*connection close*/      
     );
   disconnect from oracleadb;
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Well, you obviously ran out of disk space in library SASDWH.

Try using the compress=yes dataset option on your output dataset; this is especially helpful when long character fields are present.

If that does not help, either free up disk space by removing unneeded data or somehow get more space.

View solution in original post

16 REPLIES 16
LinusH
Tourmaline | Level 20

What happends if you issue this SQL in the via the SQL*NET client on the SAS server, but not using SAS?

The gut feeling is that the bottleneck is on the network.

Another way to test the bandwith is to ftp files between the hosts.

Data never sleeps
metallon
Pyrite | Level 9

Hi,

 

I have not spoken to IT regarding the network but the SQL client selects the 10m rows in 30 minutes. SAS proc sql does not finish but quits with an error:

 

ERROR: Insufficient space in file xxxxx is damaged. I/O processing did not complete.

SAS System                              14:08 Wednesday, June 8, 2016

ORACLE: Pipelined I/O thread did not timely terminate

ORACLE: Pipelined I/O thread terminated on 2nd attempt

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: SQL Statement used (Total process time):
      real time           41:27.17
      user cpu time       3:19.85
      system cpu time     1:05.44
      memory              1351358.96k
      OS Memory           1369752.00k
      Timestamp           06/08/2016 02:50:17 PM
      Step Count                        2  Switch Count  1425
      Page Faults                       0
      Page Reclaims                     4367
      Page Swaps                        0
      Voluntary Context Switches        1499183
      Involuntary Context Switches      19569
      Block Input Operations            656
      Block Output Operations           62433400
     

 

Kurt_Bremser
Super User

Well, you obviously ran out of disk space in library SASDWH.

Try using the compress=yes dataset option on your output dataset; this is especially helpful when long character fields are present.

If that does not help, either free up disk space by removing unneeded data or somehow get more space.

metallon
Pyrite | Level 9
Hi Kurt,
I changed to the library engine from V9 to SPD and set the partsize to 1250M and the new error is:

ERROR: The table or an index was not created because of insufficient disk space.

I wonder how I can see how much disk space I acutally need or if its a trial and error.
Kurt_Bremser
Super User

Since you could use the DB client, you have access to the SAS server outside of SAS (commandline, GUI). This means you can look at the disk space of your SASDWH location with tools depending on the type of the operating system (ie df on UNIX)

 

What if you just import 1 million records instead of 10 million?

metallon
Pyrite | Level 9
Hi. I just talked to the SAS Server Admin. I have filled the whole 30GB partition in minutes trying to write a SAS data set with about 10m rows (also using compress) ok. I will try it with 1m now. yes, we use Unix. I am suprised that 10m rows take up more than 30gb...that doesnt seem right.
Kurt_Bremser
Super User

Some things to ponder:

Look at the structure of your dataset (create it with just a few records and use proc contents). This will show you the observation size.

Next, look at the state of the partition before and while you run your data step.

Third, look if there's a quota regulation active on the partition. You might run into a personal quota limit long before the filesystem actually flows over.

metallon
Pyrite | Level 9
Hi,

I run the proc contents on the 1m rows that I managed to load.

Blocking Factor (obs/block) 264
Data Partsize 1310664960

Obersvations: 1000000
Variables: 100
Obs length: 3960
Compressed: No
Sorted: No

Engine: META
membertype: data
Kurt_Bremser
Super User

So you see that you get roughly 3960 * 1000000 bytes. Thats about 3.8 GB. Multiply by 10, and you get your filesystem overflow past 30 GB.

Also note that

Compressed: No

says you did not yet use the (compress=yes) dataset option, which might reduce the physical dataset size significantly if mostly empty character variables are present in the dataset.

Kurt_Bremser
Super User

You should be able to log on to a UNIX server using your SAS server credentials, using a SSH client like PuTTY.

Determine the physical location of your SASDWH by right-clicking on the library in the EG server list and selecting Properties

Then do

df -k location

from the UNIX commandline. This shows you filesystem size and usage in kbytes.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Have you taken this up with your IT people so they can debug the situation.  Could be any number of things, space on drive, network connection, ODBC driver etc.

SASKiwi
PROC Star

Try a select count(*) only with your program. Does it complete and how long does it take? This will test the database response, removing the time to download the data.

Ksharp
Super User
Add libname option readbuff=100000 into it to make it faster. Or can you just use data step to copy Or Try PROC COPY .
Kurt_Bremser
Super User

Since SAS uses a locally installed client to the DB in question, I'd use that same client to run the same select and store the resulting data in a local flat file. You might have a misconfiguration in the Oracle client to begin with.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 3153 views
  • 1 like
  • 6 in conversation