Problem: We are facing an issue to Use the Table Loader (Update/Insert) for Sybase IQ. It’s taking a huge amount of Execution time to load the Data. We also used the BULKLOAD option to load the data but the execution time still not satisfactory and it’s not useful for Updates.
Execution Time: 7 hours for 3.9 Million records
Idea: I am creating a Custom Defined Transformation (Different Update/Insert Code as compare to Default generated Code by SAS DI) which gives me 90% faster Execution as compared to SAS DI. The custom defined transformation has all options which are in TABLE LOADER(SAS provided), but also has additional options for Sybase IQ.
Can you please provide me the feedbacks on this approach?
One of the Core code which increases the performance: proc sql; connect to SYBASEIQ ( connection string); reset noprint; execute ( update &OUTPUT_IQ_TBL NN set NN.TIN_SKEY =M.TIN_SKEY , NN.COMM_TIN_NO =M.COMM_TIN_NO , ...
FROM &INPUT_IQ_TBL M where NN.TIN_NO = M.TIN_NO ) by SYBASEIQ;
Instead of (Default sas di code):
proc sql ; update MSTDDWH.DIM_TIN_TEMP as m set TIN_SKEY = (select TIN_SKEY from &etls_lastTable as t where m.TIN_NO = t.TIN_NO), COMM_TIN_NO = (select COMM_TIN_NO from &etls_lastTable as t where m.TIN_NO = t.TIN_NO), ... where exists (select * from &etls_lastTable as t where m.TIN_NO = t.TIN_NO); Quit;