ODBC might be the reason for your speed and single-thread woes. This driver hasn't evolved in years (again afaik).
You may want to test the native SAS/ACCESS Interface to Microsoft SQL Server and see if speed is better.
This is before I got here, and told to me by my colleagues, so this is third hand...
When setting up our SAS environment, SAS Professional Services recommended ODBC over Access to SQL Server. I don't know why, and that sounded "fishy" to me.
I agree with @ChrisNZ that ODBC is a generic standard, a "vanilla" approach to multiple RDBMS's. However, the driver itself is from Microsoft, and AFAIK the SAS ODBC engine is just a wrapper around the capabilities/API's available from the Microsoft driver.
When I choose "Export Data" from SSMS, I get these drivers in my environment:
I have never tested them all - I've always used SQL Server Native Client 11.0 - but I bet I'd get different performance characteristics across the different drivers.
Call or email SAS TS. Explain your problem. Ask them if there are performance differences between the generic SAS/Access to ODBC vs. SAS/Access to SQL Server? But if your underlying hardware infrastructure (network bandwidth, local disk I/O) won't keep up with how fast SS is passing data to the client (SAS), I wouldn't see how the different engines would compensate for that.
... View more