topic Re: Reading data from Work takes long time in SAS Programming

Reading data from Work takes long time

leok31 — Wed, 24 Jan 2018 01:18:28 GMT

I'm new SAS and still learning my way around.

I've connected to SQL Server via SAS using ODBC connection.

I used PROC SQL statement to create a table, this took around 10 second.

But then, when I try to query that table it takes a long time. So far I waited 8 min before stopping the process.

Similar thing happens when I try to JOIN the newly created table to another table from SQL Server.

Is there anything I can do to speed up the process?

Thank you

Here is the code that I'm using

proc sql;
 CREATE TABLE ActiveIB as 
SELECT distinct col1
      ,col2
      ,col3
      
  FROM CAM.FactTable
  where Col4= 'Active'
  and (DateCol is  NULL or  DateCol <= '01DEC2017'd)
  ;
  
  quit;

proc sql outobs=10;
select *
from ActiveIB
;
quit;

Re: Reading data from Work takes long time

SASKiwi — Wed, 24 Jan 2018 01:32:20 GMT

I suspect your second SQL query is reading all of your data. Try this:

proc print data = ActiveIB (obs = 10);
run;

Re: Reading data from Work takes long time

Reeza — Wed, 24 Jan 2018 02:01:46 GMT

Work shouldn’t be the issue for your second query.

Hoeever when you’re pulling data from different sources, ie WORK and a server somewhere else, SAS first brings it all in, which is what slows things down. So the fact that your join is slow Is not surprising. The fact that a plain select takes long doesn’t make sense however.

@leok31 wrote:

I'm new SAS and still learning my way around.

I've connected to SQL Server via SAS using ODBC connection.

I used PROC SQL statement to create a table, this took around 10 second.

But then, when I try to query that table it takes a long time. So far I waited 8 min before stopping the process.

Similar thing happens when I try to JOIN the newly created table to another table from SQL Server.

Is there anything I can do to speed up the process?

Thank you

Here is the code that I'm using
proc sql;
 CREATE TABLE ActiveIB as 
SELECT distinct col1
      ,col2
      ,col3
      
  FROM CAM.FactTable
  where Col4= 'Active'
  and (DateCol is  NULL or  DateCol <= '01DEC2017'd)
  ;
  
  quit;

proc sql outobs=10;
select *
from ActiveIB
;
quit;

Re: Reading data from Work takes long time

leok31 — Wed, 24 Jan 2018 03:10:20 GMT

For some reason, selecting just the top 10 is now working fine.

Selecting all the rows still takes a very long time, it's only 300k rows. Why would it take so long if the data is stored in Work library and not on an external server?

Re: Reading data from Work takes long time

SASKiwi — Wed, 24 Jan 2018 03:44:41 GMT

Post the SAS log of your program including the notes on how long it took so we can provide further advice.

Re: Reading data from Work takes long time

leok31 — Wed, 24 Jan 2018 04:49:39 GMT

The query has been running for over 30 min.

I'm running SAS Studio, so I had to cancel it and it results in termination of the session. I can't get any log data for it.

Re: Reading data from Work takes long time

ChrisNZ — Wed, 24 Jan 2018 20:51:03 GMT

1- Just checking: your CAM library uses the ODBC engine to point to SQL Server?

2- Use

 options sastrace=',,,d' sastraceloc=saslog nostsuffix dbidirectexec ;

to see what query is sent to SQL server.

3- Use dataset option(obs=10) to limit the number of rows so we can get a log.

4- Run the query and post the log.