OLE DB - Long Execution time

[yue001]

I used below code to pull the data in SAS (which is installed on Citrix) , it took me 2 minutes to get the result back. When I tested this query in MS SQL manager, it only used one sec. If I write a more complex query, it will take forever to run. Can anyone let me know how to fix this?

Proc sql;
connect to oledb as test ( datasource=XXXX provider=MSDASQL.1);
CREATE TABLE TEMP AS

select * from connection to test (select ID_Number Start_Date from Daily_Table
where Start_Date between '2019-02-01' and '2019-02-28')
;
quit;

Log: 

NOTE: Table WORK.TEMP created, with 3547 rows and 2 columns.

21 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 2:19.78
cpu time 0.40 seconds

[ChrisNZ]

Is it exactly the same query that runs in less than a second?

Can you validate with the MS SQL DBAs that the query and environment are the same?

Could the citrix network layer be responsible? Can you download other data at a normal speed?

[SASKiwi]

Run your query like this as a test. If it performs a lot faster it is the database - SAS network connection that is slow. If it runs in about the same time then it is the database that is taking all the time. Please report your result.

Proc sql;
connect to oledb as test ( datasource=XXXX provider=MSDASQL.1);
CREATE TABLE TEMP AS

select * from connection to test (select count(*) as count from Daily_Table
where Start_Date between '2019-02-01' and '2019-02-28')
;
quit;

[yue001]

Thank you SASKiwi!
I run the query, here is the result:
NOTE: Table WORK.TEMP created, with 1 rows and 1 columns.

22 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.09 seconds
cpu time 0.09 seconds

[SASKiwi]

OK, that means it could be your network connection between SAS server and database that is very slow. You should talk to your SAS or IT administrator to diagnose what is the problem here.