02-15-2018 08:24 AM - edited 02-15-2018 08:26 AM
There's a large table that I need to update daily. It has many columns, but there's only one column that needs to be updated.
Table name: EDS_CUSTOMER
Primary Key: CUSTOMER_RK
Column to update: ACTIVE_FLAG
The source data is a work table with only two columns: CUSTOMER_RK and ACTIVE_FLAG.
I want to match on CUSTOMER_RK and update ACTIVE_FLAG in EDS_CUSTOMER if it has a different value in the source table.
I have a Table Loader that uses Update/Insert, with "SQL Set" for matching rows and "Skip New Rows" for new rows. Constraint Conditions are "On" After Load. It matches on the column CUSTOMER_RK.
I'd appreciate any advice on how to optimize this operation. It's a very heavy load that goes on for hours as-is.
P.S. Couldn't find a suitable forum for DI Studio. Welcome any SAS Base suggestions.
02-15-2018 09:27 AM
lot of good suggestions in the below link and all of them sound great to me
1. use datastep for update
2. check whether indexes on CUSTOMER_RK
3. recreate table instead of update
02-15-2018 09:54 AM
02-18-2018 03:11 PM
Another thing to look at would be your database commit settings. If your job is committing for every row for example that would slow your processing drastically. Try increasing this setting to see if it improves performance.