DATA Step, Macro, Functions and more

Fetching data - [SQL Server ODBC implicit V/s SAS warehouse datasets]

Reply
Occasional Contributor
Posts: 16

Fetching data - [SQL Server ODBC implicit V/s SAS warehouse datasets]

Table 1 is a SQL Server table registered metadata in SAS - 100 million rows

SQL Server database {using ODBC connection implicit query not expicit pass thru}

SAS PROC SQL query fetches these 100 million rows in 4 minutes

Processing time - 4 minutes

 

Table 2 is a SAS dataset having the same data as above SQL Server table = 100 million rows

Same query when used to fetch data from this SAS dataset it takes 1 hour.

Processing time - 1 hour

 

Both queries are same.

I cannot see the indexes on the SQL Server table.

 

Why fetching the data from SAS internal datasets takes longer than fetching from SQL Server? 

Super User
Posts: 5,424

Re: Fetching data - [SQL Server ODBC implicit V/s SAS warehouse datasets]

Funny, usually it's the other way around.
Indexes are good in subset situations, not when doing table scans.
We need to know more to help you interpret your results.
What is your query?
Where is your SAS data stored physically?
What are the attributes of the tables?
How is the SQL Server configured?
Data never sleeps
Super User
Posts: 5,424

Re: Fetching data - [SQL Server ODBC implicit V/s SAS warehouse datasets]

Addition:
to speed SAS processing up consider SPDE. Also compressing might help in some situations.
To evaluate the processing in SAS, add
OPTIONS FULLSTIMER MSGLEVEL=I;
To evaluate SQL Server processing add
OPTIONS SASTRACE=',,,d' SASTRACELOC=SAS LOG NOSTSUFFIX;
Data never sleeps
Occasional Contributor
Posts: 16

Re: Fetching data - [SQL Server ODBC implicit V/s SAS warehouse datasets]

I'm not too sure about the configuration of the SQL Server box but we access it using ODBC connection using a libname.

 

 

Example:

 

SQL Server Query

==============

proc sql;

create table A as

select

   var1

   ,var2

   ,var3

from SQLLIB.SQL_table1

where var_X = 'XXX'

and var_Y = 'YYY'

and var_Z = 'ZZZ'

;

quit;

 

SAS Dataset Query

==============

proc sql;

create table A as

select

   var1

   ,var2

   ,var3

from SASLIB.SAS_table1

where var_X = 'XXX'

and var_Y = 'YYY'

and var_Z = 'ZZZ'

;

quit;

 

I will try using the options you suggested

Ask a Question
Discussion stats
  • 3 replies
  • 259 views
  • 0 likes
  • 2 in conversation