topic Re: Optimizing Update/Insert in SAS Data Management

Optimizing Update/Insert

EinarRoed — Thu, 15 Feb 2018 13:26:45 GMT

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.

Example

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.

Re: Optimizing Update/Insert

kiranv_ — Thu, 15 Feb 2018 14:27:57 GMT

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

https://communities.sas.com/t5/SAS-Enterprise-Guide/what-can-be-used-instead-of-proc-update-for-a-big-data-set/td-p/245180

Re: Optimizing Update/Insert

LinusH — Thu, 15 Feb 2018 14:54:47 GMT

For me it doesn't sound that you have an appropriate data model, or your ETL is a bit inconsistent. You could for instance have a separate Customer Active table.
Then I'm not sure if recreating the table is a good idea if you have a low hit rate.
Be sure to use MODIFY with index.

Re: Optimizing Update/Insert

SASKiwi — Sun, 18 Feb 2018 20:11:01 GMT

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.