Proc sql update

LAP — Fri, 11 Apr 2008 19:57:14 GMT

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?

Re: Proc sql update

deleted_user — Tue, 15 Apr 2008 08:30:57 GMT

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

topic Re: Proc sql update in SAS Procedures

Proc sql update

Re: Proc sql update