Proc SQL - Query Taking Longer time - Query Need to Optimize - Need Expert Help!!

[amitbjambhulkar]

Hello,

I am trying to write the below query which taking longer time around 11 hours & 30 mins to complete. Is their any other way where it can run the query faster way. Any help / suggestion will be really helpful for me. Thanks.

proc sql noprint;

select

max(datepart(tran_datetime)) format date9.

into :end_ugt_hist 

from DBA.sas_max_site_datetimes 

where site = "SKY" and  "15SEP2020"d <= datepart(tran_datetime) <= today();

quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           11:33:01.77

      cpu time            40.52 seconds

Thanks,

Amit

[Ksharp]

I think the wasting time is not from SAS side, it should be from DataBase side (DBA.sas_max_site_datetimes).

Try to reduce the process time at DBA side by LIBNAME's option :  read_buff=10000 ,insert_buff=10000 ,dbcommit=0,bulkload=yes.

And @SASKiwi knew more things about this topic .

Or try to use pass-through method to run this SQL.

[Kurt_Bremser]

In the WHERE clause, use datetime constants (replace the call of TODAY() with the result of DATETIME() stored in a macro variable, so you can have no function calls at all in your WHERE.

Use

options fullstimer;

to see the relationship between real time and CPU usage; a library name of DBA points to a connection to a remote database, so either the network connection and/or the configuration of the LIBNAME might be the culprit.

Also take a look at the SASTRACE= system option which can show you which parts if the query are offloaded to the remote DB.

[Patrick]

It's likely that SAS can't translate the full SQL query to the database SQL syntax and though a lot of data gets first pulled to the SAS side before the where clause executes.

What database are you interfacing with? The SAS documentation for the access engine for your DB documents which SAS functions can get pushed to the DB side.

I obviously couldn't fully test it but I believe below code should work and perform better.

%let low_dttm   =15SEP2020:00:00:00;
%let high_dttm  =%sysfunc(intnx(dtday,%sysfunc(datetime()),1,b),datetime18.);

proc sql noprint;
  select datepart(tran_datetime)) format=date9. into :end_ugt_hist
  from
    (
      select max(tran_datetime)
      from DBA.sas_max_site_datetimes 
      where site = "SKY" and  "&low_dttm"dt <= tran_datetime < "&high_dttm"dt
    )
  ;
quit;

[amitbjambhulkar]

MariaDB database.

[Patrick]

@amitbjambhulkar Try the code I just posted (latest update). It should work for ODBC.