DATA Step, Macro, Functions and more

SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

Reply
Frequent Contributor
Posts: 134

SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

Hello, I am pulling data from SQL server.

 

it takes about 1 hour when I use sas and about 3 mints using SQL server Management Studio. data comes back but takes ages to pull it using SAS

 

here is a sample code that I used to query... please advise

 

Proc SQL;

create table temp_A

as

select distinct
a.number, b.type, c.country, d.city
from
            R1.dbo.Tbl_Numbers a
left join R1.dbo.Tbl_Type b on (a.ID = b.AcID)
left join R1.dbo.Tbl_Country c on (a.CID = c.CID)
left join R1.dbo.Tbl_City d on (a.ID = d.AcID)
where
dateDTTM >= '01Jun2016' and dateDTTM < '01Jun2017' and
(
upper(b.Comments) like '%HOURS%' or
upper(b.History) like '%HOURS%' or
upper(b.Group) like '%HOURS%'
) and
c.TCode = 3 and
d.ACode in ('F', 'C')
;

 

Thanks

Super User
Posts: 19,869

Re: SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

[ Edited ]

How big is the data? 

Did you run this with FULLSTIMER option? If so, please show the log. 

 

My first guess, it's a big table and the time is spent transferring the data from the server to SAS not actually doing the join. 

You should check which functions are passed to the DB as well versus which need to execute locally. See the DB specific version in the SAS docs to understand how functions are processed when using a server.

 

EDIT (links):

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n1hwhalvtejwi2n1t...

Frequent Contributor
Posts: 134

Re: SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

seems like joins are not supported by sas. if not, how can I achieve my desired results

Super User
Posts: 3,260

Re: SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

It looks like your query uses SQL Server-specific syntax. To run unchanged in SAS put it into an SQL passthru query which will look like the following - you will need to supply your own connection string:

 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (---- Put your SQL Server query here
   )
  ;
  disconnect from odbc;
quit;
PROC Star
Posts: 326

Re: SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data

[ Edited ]

Can you show where the join fails. If your query fails it might be on dates. Can you add this and try, whether it will help with your query times.

I have seen moving data from sql server to SAS for nearly 3 million records takes around 30 minutes with 100 columns, so you can estimate an approximate time.

 

options dbidirectexec sastraceloc=saslog;

 

Super User
Posts: 19,869

Re: SAS vs SQL Server Mgmt Studio - Sas takes longer to pull data


tparvaiz wrote:

seems like joins are not supported by sas. if not, how can I achieve my desired results


That doesn't sound correct. 

What makes you think that?

 

Joins work the same, EXCEPT if you're using datasets both in SAS and on the server. In that case SAS has to bring things down to SAS first to merge.

Ask a Question
Discussion stats
  • 5 replies
  • 227 views
  • 2 likes
  • 4 in conversation