SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS/Access to Oracle & READBUFF Performance

Reply
SAS Employee
Posts: 13

SAS/Access to Oracle & READBUFF Performance

The default setting for SAS/Access to Oracle is 250 buffers. A READBUFF specifies the number of rows to read into SAS’ buffer. READBUFF can have a dramatic impact on performance. Here is an example of how you can test what a good READBUFF setting might be.

First I create three libnames with different READBUFF settings

libname ora1 oracle user=sh pass=sh path=ora10g READBUFF=250;
libname ora2 oracle user=sh pass=sh path=ora10g READBUFF=1000;
libname ora3 oracle user=sh pass=sh path=ora10g READBUFF=10000;

Next I identify a relatively large table in terms of column width and row count. For this example I have a table called INFORMS2009 which contains 79 columns and 1M records.

Finally, I make three runs with a datastep program to extract data

data test1;
set ora1.INFMS2009;
run;

data test2;
set ora2.INFMS2009;
run;

data test3;
set ora3.INFMS2009;
run;

And review the results:

NOTE: There were 1006114 observations read from the data set ORA1.INFMS2009.
NOTE: The data set WORK.TEST1 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:06.90
cpu time 20.01 seconds

NOTE: There were 1006114 observations read from the data set ORA2.INFMS2009.
NOTE: The data set WORK.TEST2 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 40.70 seconds
cpu time 21.54 seconds


NOTE: There were 1006114 observations read from the data set ORA3.INFMS2009.
NOTE: The data set WORK.TEST3 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 33.18 seconds
cpu time 20.90 seconds

How about 1m 6 seconds vs. 33 seconds! that is a 63% improvement in performance. Note your millage may vary, also like candy too much of a good thing can be bad too.

For example jacking up the READBUFF to 20k can actually result in worse performance than the default setting.

libname ora4 oracle user=sh pass=XX path=ora10g READBUFF=20000;

data test4;
set ora4.INFMS2009;
run;

NOTE: There were 1006114 observations read from the data set ORA4.INFMS2009.
NOTE: The data set WORK.TEST4 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:11.90
cpu time 23.01 seconds

for more information on READBUFF and SAS/Access to Oracle check out http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342369.htm
Ask a Question
Discussion stats
  • 0 replies
  • 1336 views
  • 0 likes
  • 1 in conversation