10-31-2015 04:45 AM
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?
10-31-2015 07:02 AM
Scott, When implicit is wanted then:
1/ your first check should be on the used functions the must be available in the RDBMS
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
10-31-2015 08:12 PM - edited 10-31-2015 08:14 PM
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.