BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Admin, General

SAS Administration: Fetch Data Faster across ODBC

Submitted by on 2012-11-01 – 7:43 AM 2 Comments

On my current “big data” project, I’m connecting SAS to PostgreSQL using the ODBC access engine.  At first I was seeing pretty bad performance – specifically I wasn’t able to view the ~2.2 billion sample records I created.  After adding 2 simple lines to the odbc.ini configuration file I was able to fly through the data.

By default, ODBC data sources (at least in Linux) may not have the fetch parameter defined in the odbc.ini configuration file depending on how the ODBC connection was originally setup.  This can severely impact how efficient you can read high volumes of data because the ODBC connection will try to access the entire table at once.  Setting the fetch parameter tells the ODBC driver to retrieve a certain number of records at a time in memory, rather than all at once.  This is similar to setting the READBUFF parameter as I previously mentioned.

Enable Fetching with ODBC Sources

Find your odbc.ini configuration file.  Mine is located at /etc/odbc.ini.  Add the following 2 lines to your specific DSN you wish to enable fetching.

Fetch=50000
UseDeclareFetch=1

In the end, this is what my odbc.ini configuration looks like today:

Also note, here is an error that can potentially be fixed using this configuration tweak: http://support.sas.com/kb/41/369.html

Let me know your ideas!

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.

The following two tabs change content below.

Tags: ,

2 Comments »