BookmarkSubscribeRSS Feed
EinarRoed
Pyrite | Level 9

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.

3 REPLIES 3
kiranv_
Rhodochrosite | Level 12

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

 

 

LinusH
Tourmaline | Level 20
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
SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1723 views
  • 2 likes
  • 4 in conversation