SAS Administration: Fetch Data Faster across ODBC
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.
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.
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