Modify taking lot of time

[yashpande]

Hi All,

Basically we have table loader code from SAS DI which looks as below. We are using Update Insert technique in DI and hence below is the code which is used. However, it is taking whole lot of time. sasdata is sas library. And we have indexes on both the tables. 

sasdata.TBL_DTLS_ERROR_APD has 212354 records and etls_lastTable has 60000 records.

I am out of all my ideas on how to reduce this time of execution. Any help is really appreciated.

data sasdata.TBL_DTLS_ERROR_APD;
length etls_msg $200;
drop etls_msg;
modify sasdata.TBL_DTLS_ERROR_APD
&etls_lastTable
updatemode = nomissingcheck
;

by POL_NUM_TXT INSURED_ID;

/* if the record does not exist in the master, then add it */
if %sysrc(_DSENMR) eq _iorc_ then
output;
/* if the record exists in the master, then replace it */
else if %sysrc(_SOK) eq _iorc_ then
replace;
%if &etls_StopOnIORC ne %then
%do;
if _iorc_ in (&etls_StopOnIORC) then
do;
etls_msg = iorcmsg();
put etls_msg;
%rcSetDS(5)
stop;
end;
%end;
_iorc_ = 0;
_error_ = 0;

run;

[Krueger]

What's the actual run time for this? While you may think it's long it may be "normal". Not saying it can't be improved but it's a matter of how much more could you potentially improve it from where it's at. Some code can be cut in half on execution time, while others extensive changes and/or re-writing it might only improve by milliseconds. 

[s_lassen]

A couple of ideas on how to improve performance:

1. Make sure that your index is the right one: when looking up rows using two variables, it should be a composite index 
   (like in "create index idx on sasdata.TBL_DTLS_ERROR_APD(POL_NUM_TXT,INSURED_ID)",
   not two simple indexes on the two variables
2. If you can, keep your master data sorted on the index variables (when it is not in use, run a PROC SORT withe the FORCE option, and then recreate the index)
3. Having an index on the transaction data set will not help, best you can do is to sort it by the key variables.