SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Optimizing Update/Insert

Reply
Frequent Contributor
Posts: 100

Optimizing Update/Insert

[ Edited ]

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.

PROC Star
Posts: 503

Re: Optimizing Update/Insert

Posted in reply to EinarRoed

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-bi...

 

 

Super User
Posts: 5,852

Re: Optimizing Update/Insert

Posted in reply to EinarRoed
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.
Data never sleeps
Super User
Posts: 3,860

Re: Optimizing Update/Insert

Posted in reply to EinarRoed

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.

Ask a Question
Discussion stats
  • 3 replies
  • 242 views
  • 2 likes
  • 4 in conversation