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

[Sanjay_M]

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? 

[LinusH]

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?

[LinusH]

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;

[Sanjay_M]

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