PROC SQL implicit pass-through to Oracle

[ScottBass]

Hi,

I have this code from a SAS-supplied solution macro:

  proc sql;
    update seg_kc.fsc_entity_watch_list_dim
      set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00),
           change_current_ind = 'N'
      where upcase(watch_list_name) = 'WCHK' and
            upcase(change_current_ind) = 'Y' and
            entity_watch_list_number in
              (select entity_watch_list_number
                 from STG_WTCH.wl_wchk_delete_load);
  quit;

I've modified it to close out all records, not just those matching the sub-query:

  proc sql;
    update seg_kc.fsc_entity_watch_list_dim
      set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00),
           change_current_ind = 'N'
      where upcase(watch_list_name) = 'WCHK' and
            upcase(change_current_ind) = 'Y';
  quit;

However, it is taking forever to run.

I've always been taught not to call a function for every record in the dataset/table if that function is in fact just returning a constant.  I was also hoping the below would make PROC SQL use implicit pass-through to Oracle:

   %* get runasofdate ;
   %fcf_get_runasofdate;
   %put;
   %put >>> RUNASOFDATE: &runasofdate;
   %put;
   
   %* convert to datetime - this will execute once, at compile time ;
   %let datetime=%sysfunc(putn(%sysfunc(inputn(&runasofdate,yymmdd8.)),date9.)):00:00:00;
   %put >>> DATETIME:  &datetime;
   
   proc sql noprint;
      update seg_kc.fsc_entity_watch_list_dim
         set 
            change_end_date = "&datetime"dt,
            change_current_ind = 'N'
         where 
            upcase(watch_list_name) in ('WCHK','WORLDCHECK')
            and
            upcase(change_current_ind) = 'Y'
      ;
   quit;

But this is still taking forever.

I can convert this to explicit pass-through, which performs well, but there may be issues with that when crossing environments (DEV/TEST/PROD) - I'd have to investigate that further.

So, is there a way convert the above code to implicit pass-through, or otherwise improve the query performance?

Thanks...

[jakarman]

Scott, When implicit is wanted then:

1/ your first check should be on the used functions the must be available in the RDBMS

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p0f64yzzxbsg8un1uw...

2/ You could follow (obs=0) the SQL translation often the SQL is tried behind curtains and when failing all data is copied to the sas-machine. The sqltrace option is a must for analyzing what is happening. A nice paper. http://support.sas.com/resources/papers/proceedings13/072-2013.pdf  

3/ dates / datatimes quotings sqlconstdatetime and sqlreduceput are eliminating those sas specifics before trying to convert it to Oracle Sql  http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#p12ohgh32ffm6un13s...

The quotation in SQL is normally single quotes in ANSI SQL for constant values. SAS is using the double-quotes when allowing macro vars it can be nedessary to stick to those single ones. Thena use %nrstr(%')  for that single quote.

The Date-constant should be translated to ansi standards. http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ  

The usage of date/time conventions as of SAS habits can cause problems. I have seen several posts indicating the automatic translation (SAS/access) is failing.    

4/ forcing dbmust

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p005mmm6mnyvdtn10r...

[Patrick]

Hi Scott,

I would expect that your last SQL using change_end_date = "&datetime"dt  will convert nicely into Oracle SQL and will execute fully in-database. As @jakarman points out option "SASTRACE" will tell you what's going on behind the scene and how much of the SAS SQL gets converted and sent to Oracle for execution.

Defensive programming is generally a good thing BUT column "watch_list_name" is indexed; by applying an upcase() function on this column the index can't get used.

      where upcase(watch_list_name) = 'WCHK' and
            upcase(change_current_ind) = 'Y';

I believe for "watch_list_name" and "change_current_ind" the load process needs to ensure that the values get loaded all uppercase so that any subsequent query doesn't need to use upcase() functions.

Thanks,

Patrick