Reading data from Work takes long time

Reply
New Contributor
Posts: 3

Reading data from Work takes long time

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;

 

 

 

Super User
Posts: 3,489

Re: Reading data from Work takes long time

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

proc print data = ActiveIB (obs = 10);
run;
Super User
Posts: 21,572

Re: Reading data from Work takes long time

[ Edited ]

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;

 

 

 


 

New Contributor
Posts: 3

Re: Reading data from Work takes long time

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?

 

Super User
Posts: 3,489

Re: Reading data from Work takes long time

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

New Contributor
Posts: 3

Re: Reading data from Work takes long time

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.

 

PROC Star
Posts: 1,935

Re: Reading data from Work takes long time

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.

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 124 views
  • 0 likes
  • 4 in conversation