SAS/Access to Oracle & READBUFF Performance

[Michael_SAS]

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

[MC1985]

Hi Michael,

do you know if there's a way to calculate in a prior way the optime buffersize?

can you help me?

thank you very much

MC

[AhmedAl_Attar]

Hi Michael,

If I'm not mistaken, READBUFF & BUFFSIZE (SQL PASS Through Option) are related to the -MEMSIZE option allocated to your SAS session, right?

If you increase these options READBUFF & BUFFSIZE, without increasing the -MEMSIZE then you are likely to see decremented performance, after all, these options affect he amount of records you are placing in memory in every fetch operation from the database.

It's no difference in concept when dealing with sorting large data set or even summarizing a data set, having the optimum -memsize & -sortsize option values affect your SAS performance.

Just my 2 cents,

Ahmed