SAS Administration: Process Data Faster in RDBMS by Buffering the Data in Memory
By default, accessing third party relational databases can be very slow if not configured properly. I recently started using PostgreSQL 9.1, an open source database, to store high volumes of data for my SAS Global Forum 2013 paper. In this example I am using an ODBC connection to connect to a database I defined in Postgres. Native SAS/ACCESS engines to databases such as Oracle or MS SQL Server will have different options and abilities. At first it was taking forever to load up data because SAS was inserting 1 row at a time into the database table. After adding a simple option my data processing was off to the races!
RDBMS Buffering Options
The SAS INSERTBUFF and READBUFF options will improve ODBC and OLE DB libraries dramatically. By default these are set to 1 and 250 rows respectively for ODBC connections. Setting these buffer sizes tells SAS how many rows to buffer in memory before processing.
Using the LIBNAME Option
These options can be added to the LIBNAME statement to set the buffering sizes for all processing done on tables within the library. Ideally if you have the SAS Metadata server running, your SAS Administrator should set these options through the Data Library manager in SAS Management Console.
If you are using Base SAS or writing code in SAS Enterprise Guide, you can also manually write the LIBNAME step like this:
LIBNAME pgsgf13 ODBC DBCOMMIT=10000 READBUFF=30000 INSERTBUFF=30000 DATASRC=sasgf13 SCHEMA=public ;
Using a SAS Data Set Option
You can also explicitly define these buffer options for an individual data step in your code if you want. This may come in handy depending on the type, size and width of data you plan on inserting.
LIBNAME pgsgf13 ODBC DATASRC=sasgf13 SCHEMA=public ; data pgsgf13.test(dbcommit=10000 insertbuff=10000 readbuff=10000); *** DATA STEP STUFF ****; run;
Careful consideration must be taken into account when setting these options. The optimal setting depends on your SAS compute server resources and network capacity. The number of rows to buffer should be much less for very wide tables with lots of character data because of the physical byte sizes of character columns and the overall width of the table. In my project I am using very skinny fact tables with numeric data, which requires only 8 bytes per column of numeric data. Assuming I have 10 numeric columns, that’s only about 80 bytes of data per row. For my data step which creates a huge volume of data, I could theoretically set the INSERTBUFF much larger, but SAS does have a hard limit of approximately 32,000 rows it can buffer in memory 🙂 .
Learn all about SAS BI
You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.
Latest posts by Steve Overton (see all)
- BulkLoading with PostgreSQL available in SAS 9.4 - 2013-10-22
- Everything Still Starts and Ends with Data in SAS Visual Analytics - 2013-07-30
- Describe Your Table in SAS to Write the SQL Code - 2013-05-22
- Update Table Metadata Using Base SAS Code - 2012-12-19
- SAS Administration: Fetch Data Faster across ODBC - 2012-11-01