topic Re: Proc SQL - Query Taking Longer time - Query Need to Optimize - Need Expert Help!! in SAS Programming

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

amitbjambhulkar — Wed, 18 Sep 2024 07:15:08 GMT

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

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

Ksharp — Wed, 18 Sep 2024 08:47:10 GMT

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.

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

Kurt_Bremser — Wed, 18 Sep 2024 08:56:14 GMT

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.

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

Patrick — Wed, 18 Sep 2024 12:21:56 GMT

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;

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

amitbjambhulkar — Wed, 18 Sep 2024 12:21:53 GMT

MariaDB database.

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

Patrick — Wed, 18 Sep 2024 12:44:51 GMT

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