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

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

Sanjay_M — Mon, 14 Sep 2015 09:57:08 GMT

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?

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

LinusH — Mon, 14 Sep 2015 12:14:42 GMT

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?

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

LinusH — Mon, 14 Sep 2015 12:18:19 GMT

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;

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

Sanjay_M — Mon, 14 Sep 2015 12:43:52 GMT

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