SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL sever?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL sever?

Hi All,

 

    When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL sever native client connection? 

That is when you do "libname test  ODBC"  or "libname test SQLSVR" to access the dataset, which one is more efficient?

Also what's the advantage and disadvantage when using ODBC connection or SQL sever native client connection in SAS?

 

Thank you!

Jade


Accepted Solutions
Solution
‎01-20-2017 08:38 AM
Super User
Posts: 3,252

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Performance should be similar. The main constraint is network bandwidth to move data between the SQL Server and the SAS application server. We use ODBC and find performance is very good. The advantage of ODBC is it can be used with any ODBC-compliant database whereas the SQL Server ACCESS product can only be used with that database.

 

In theory SAS/ACCESS to SQL Server will be more customisable but I have yet to identify any major limitations with ODBC.

View solution in original post


All Replies
Super User
Posts: 5,427

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

What do you mean by native SQL Server connection? Regardless of what your client is called it's still ODBC or OLEDB. Regardless, it shouldn't really care unless you have any specific requirements.
Data never sleeps
Regular Contributor
Posts: 168

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

To LinusH: Thank you for your reply! By speaking native SQL Server connection, I mean using SAS/access to MS SQL server directly.

Solution
‎01-20-2017 08:38 AM
Super User
Posts: 3,252

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Performance should be similar. The main constraint is network bandwidth to move data between the SQL Server and the SAS application server. We use ODBC and find performance is very good. The advantage of ODBC is it can be used with any ODBC-compliant database whereas the SQL Server ACCESS product can only be used with that database.

 

In theory SAS/ACCESS to SQL Server will be more customisable but I have yet to identify any major limitations with ODBC.

Regular Contributor
Posts: 168

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Thank you SASKiwi! Can I ask what makes you think SAS/ACCESS to SQL server will be more customisable?

Super User
Posts: 3,252

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

@JBailey's post lists some of these. However if you are just doing SQL queries then either product is fine. 

Super User
Posts: 5,427

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Maybe this old knowledge but SAS/ACCESS to SQL Server was only available in UNIX/Linux environments. So of you are in Windows I don't think that you have ACCESS to SQL Server as an alternative...
Data never sleeps
SAS Employee
Posts: 215

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Hey,

 

Windows is a new platform for SAS/ACCESS Interface to Microsoft SQL Server.

 

This includes a DataDirect driver for MS SQL Server. Benefits are; 

  1. Enhanced function push-down
  2. Selected PROC push-down
  3. Enhance Internationalization support
  4. Simplified LIBNAME statement - no DSN required.

I am sure I am missing something. If you are interested, I will create a community article. Let me know if you are interested. 

Regular Contributor
Posts: 168

Re: When SAS Accessing MS SQL server data, which one is more efficient , ODBC connection or SQL seve

Yes, I am interested. Please let us know if the article is ready!

 

Jade

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 588 views
  • 2 likes
  • 4 in conversation