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

[tparvaiz]

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

[Reeza]

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...

[tparvaiz]

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

[SASKiwi]

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;

[kiranv_]

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;

[Reeza]

---

@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.