BookmarkSubscribeRSS Feed
LAP
Quartz | Level 8 LAP
Quartz | Level 8
I'm trying to update records in an oracle table using the values from a SAS table.
I'm testing against a SAS table (instead of the oracle table) using the following

Update Oracle_Table as a
Set FCST_UNTS = (Select FCST_UNTS from SAS_Table as b
where a.Base_Segm_id = b.Base_segm_id and
a.year = b.year and
a.mo = b.mo),
FCST_TONS = (Select FCST_TONS from SAS_Table as b
where a.Base_Segm_id = b.Base_segm_id and
a.year = b.year and
a.mo = b.mo),
FCST_REV = (Select FCST_REV from SAS_Table as b
where a.Base_Segm_id = b.Base_segm_id and
a.year = b.year and
a.mo = b.mo)
where a.Base_Segm_id in (Select Base_segm_id from SAS_Table ) and
a.year in (Select Year fromSAS_Table ) and
a.mo in (Select mo from SAS_Table ) ;

There are 160,428 records in the SAS_TABLE and 409,008 records in the oracle table. I used a smaller version of the tables initially and all seemed to work well.
However it seems to be taking forever to run the full update.

Is there a better way to do this? or do I just need to be patient?
1 REPLY 1
deleted_user
Not applicable
All manner of issues may arrive with performance including the tuning of the Oracle server. For that you need to speak with your Oracle DBAs.

As a rule of thumb though, when I load or update foreign tables from SAS, I make sure that the indexes on the foreign table are dropped first to ensure that the database is not thrashing as new data is inserted that changes an existing index.

Kind regards

David

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 646 views
  • 0 likes
  • 2 in conversation