BookmarkSubscribeRSS Feed
Sanjay_M
Obsidian | Level 7

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? 

3 REPLIES 3
LinusH
Tourmaline | Level 20
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
LinusH
Tourmaline | Level 20
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
Sanjay_M
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 965 views
  • 0 likes
  • 2 in conversation