Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Longimanus — Thu, 29 Aug 2024 19:05:59 GMT

Hi folks! I need help!

I have a problem not even ChatGTP can solve. 🙄 Sure the suggested solutions look pretty good but there is always something wrong when I try to run it. I tried to change and "tell" ChatGTP something is wrong, and then it starts: You are completely right. I will send you the correct code. Well ... that also doesn't work. At some point the BOT gets it ... but not yet.

My problem: 😳

I need to update one variable altered_dttm in table TABLE_1 which is SO HUGE it's residing in our SPD-library, SPD_LIB.
There is a view on this table that only points to 1/3 of it, so I already planned to avoid to use the entire table in the final solution. Let's for the sake of the example say the view is TABLE_1 in LIB_X. I first coded a test SQL program to see if the code works on 1/1000.000th of all the rows in the actual table.
Then there is a lookup table TABLE_2 in WORK with the same variable altered_dttm. Yes they have the same name and this should not be a problem. ChatGTP likes me to rename the one in the lookup table.
Both tables have a unique composite key (PRIMKEY) build by the variables account_key and valid_from_dt.

You guessed it ... I need to replace the variable altered_dttm in table TABLE_1 if it is missing but not missing in TABLE_2. That is all really. Should be simple. Apparently not when it's HUGE for SQL ... or in a data step (replace) with a composite key.

So to test my SQL program I took a a tiny part of the TABLE_1 and called it TABLE_1_TEST in WORK. I made sure that all the relevant observations (with the same account_key and valid_to_dttm. ) were present and some more, to test-run and see if it worked.

My test program:

%LET T1 = TABLE_1_TEST;

%LET T2 = TABLE_2;

%LET Variable = ALTERED_DTTM;

proc sql;

update &T1. as a

set &Variable. = (select b.&Variable.

from &T2 as b

where a.account_key = b.account_key

and a.valid_from_dt = b.valid_from_dt)

where missing(a.&Variable.)

and exists (select 1

from &T2. as b

where a.account_key = b.account_key

and a.valid_from_dt = b.valid_from_dt)

;quit;

That SQL works exactly as I want. 🙂 I got all the missing dates written in the observations where they were missing. A happy man! 😊

Then I tried to run it on the view.

%LET T1 = LIB_X.TABLE_1; /* So I chose a view and not the entire MEGA table that resides in SPD_LIB ! */

%LET T2 = TABLE_2;

%LET Variable = ALTERED_DTTM;

The code started to run but ... nothing seemed to happen. Or maybe it was. I checked in putty (not sure how you guys monitor your PIDS) and the process was using almost a 100% of the CPU but the status was S ... sleeping. Waiting for something. Nobody on the system. I am not sure how SQL works with this when the amounts of data become 10.000 fold. I canceled by job after two hours and checked. Nothing was changed. Actually good for the moment. You don't want half of it done ...

I mentioned this to ChatGTP. I got an explanation that SQL can have difficulties with huge indexed tables. It suggested to fix it with a a modify-replace datastep or with hash object approach. I preferred the former since it looks familiar. It never came with a solution that worked. The problem being the composite key. It came with various "key=" options but non that worked. After various tries I thought: let's try that hash option. Also didn't work.

I feel a little embarrassed to ask you guys since this should be school stuff. But I never solved this with a table this size. I am sure a Data Step with modify 'n replace can use the index properly and not run through the entire table (if that is what SQL is doing ... worked fine on my little test dataset but not on REAL deal.). S o a n y b o d y ???!!! 🙏

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

ballardw — Thu, 29 Aug 2024 19:46:11 GMT

If your source is actually a view then you can't update it. A View is basically a set of instructions on how to extract/manipulate values from existing data. As such attempting to "update values" doesn't mean anything as views do not actually contain values just those instructions.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Longimanus — Thu, 29 Aug 2024 20:58:26 GMT

Aha ... so I need the full monty if I want to do it that way. So you are saying that running with a Datastep would not help me a bit. I still cannot use the view. Right?

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

ballardw — Thu, 29 Aug 2024 21:08:02 GMT

@Longimanus wrote:

Aha ... so I need the full monty if I want to do it that way. So you are saying that running with a Datastep would not help me a bit. I still cannot use the view. Right?

Not to change values in a view.

Performance issues with large data sets I often suggest talking to the data base manager or other people involved in managing the data. It may be that you can provide them the data from SAS and have them update the data set. Or use "pass through" code to call external database management tools to work with that. I don't work with SPD so I'm not sure if this is an option.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Longimanus — Thu, 29 Aug 2024 21:50:46 GMT

It's almost midnight and nobody is on the system ... so I decided to run on the REAL DEAL. The big table in the SPD library. It ran through it all in less than 10 minutes. Yes ... and the updates are correct. What a relieve. Thanx for the feedback. Yes ... I can retrieve a data from a view ... but not update it. "Of course" I think now. 😉 Duh! 😅

topic Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all. in SAS Programming

Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.

Re: Update HUGE indexed table with SQL (in SPD library) seems not to run at all.