topic Re: OLE DB - Long Execution time in SAS Programming

OLE DB - Long Execution time

yue001 — Tue, 23 Apr 2019 20:47:27 GMT

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

Re: OLE DB - Long Execution time

ChrisNZ — Sat, 20 Apr 2019 04:11:26 GMT

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?

Re: OLE DB - Long Execution time

SASKiwi — Sat, 20 Apr 2019 06:47:21 GMT

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;

Re: OLE DB - Long Execution time

yue001 — Tue, 23 Apr 2019 20:23:32 GMT

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

Re: OLE DB - Long Execution time

SASKiwi — Tue, 23 Apr 2019 21:19:37 GMT

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.