This is all done from a SAS 9.1.3 SP4 installation, with the SAS Access to Teradata and SAS access to ODBC. My SAS server is a local (US-Chicago) server, in a trusted domain. My Teradata instance is a local (US-Columbus, OH) installation in a trusted domain. My Sybase installation is a UK based B2B connection in a non-trusted domain, so traffic is trans-Atlantinc, through multiple firewalls.
Accessing Teradata:
Both Teradata and ODBC are MUCH (Exponentially) faster when using pass-thru, provided that the code is written correctly, and you are not joining SAS tables to Teradata or joining tables across Teradata Schemas.
The Teradata Access engine is generally 20% to 40% faster than the ODBC engine (when accessing Teradata), with Teradata pass-thru being the best all around performance. In other words, Teradata pass-thru is usually 30% to 40% faster than ODBC Pass-thru, and Teradata libname is usually 20% to 30% faster than ODBC libname code.
Accessing a Sybase Installation:
Sybase IQ is strictly an ODBC compliant RDBMS, so SAS Access to Sybase was not an option.
Pass-thru still generally out performs the libname connections, but only if you are able to subset your data on the DBMS. However, if you need to pull a whole table, or you can't subset, I am finding the libname statement is outperforming the proc-sql connect by by a margin of 3 to 1. I know that sounds counter-intuitive, but I have been able to replicate this finding multiple times. I am attributing this to the complexity of this connection, and guessing that there may be substantial latency issues on the pass-thru connections."
Thanks to Rich Kerwin for contributing to the sas-BIogsource!
1 Comment
I have found the SAS publication SAS/ACCESS 9.1 Supplement For Teradata SAS/ACCESS For Relational Databases to be indispensible when working with terradata.