Fastest way to update a table joining to a huge table

Database
Enthusiast

Fastest way to update a table joining to a huge table

I was wondering what is the most optimum/fastest method to carry out an update under the following scenario. What we have now is working fine, but with production volumne can create a problem.

Need to update table 1 (approx 250-500k rows) based on whether or not a record is present in table 2 (staggering 5 billion rows). The join between the tables is on 3 columns which together form the PI in both the tables.

If a record exists in TABLE 2 then

UPDATE T1

FROM TABLE1 T1, TABLE2 T2

SET T1.FRUIT = T2.FRUIT

WHERE T1.DATE = T2.DATE

AND T1.ID = T2.ID

AND T1.ID2 = T2.ID2

If a matching record is not found in table 2

UPDATE TABLE1 T1

SET T1.FRUIT = T1.DEFAULT_FRUIT

WHERE NOT EXISTS

(SELECT 1

FROM TABLE1, TABLE2

WHERE T1.DATE = T2.DATE

AND T1.ID = T2.ID

AND T1.ID2 = T2.ID2)

We can combine the above two queries if that would help.

Appreciate your help on this. Thanks.

Tags (1)
5 REPLIES
Senior Supporter

Re: Fastest way to update a table joining to a huge table

If both tables have the same PI Update 1 should be OK.

I don't know your processes or if this a one time task. When you build the table 1 can't you set fruit to defaul_fruit?  In this case you would only need to run the update 1.

If this is not possible it might even be faster to do a unconditional Update on table 1 which sets FRUIT = T1.DEFAULT_FRUIT and run update 1 afterwards as this will only update 250-500k rows...

Enthusiast

Re: Fastest way to update a table joining to a huge table

First of all thanks so much for such a prompt response.

Then thanks again for the simple and effective suggestion of carrying out second update first. That actually might work out :))) I will definitely explore this possibility.

Sometimes the solution lies beyond teradata concepts :)

-PT

Enthusiast

Re: Fastest way to update a table joining to a huge table

I would suggest looking at the MERGE statement in the Teradata Manuals. It will allow you to manage both the INSERT and UPDATE actions (UPSERT) whether the matching record exists in the table in a single statement. It is also a block level operation that may yield you improved performance over the UPDATE operator which operates as a row level operation.

Enthusiast

Re: Fastest way to update a table joining to a huge table

Thanks Rob. Will definitely look at the suggestion you made.

Enthusiast

Re: Fastest way to update a table joining to a huge table

If both the table have same PI then use MERGE UPDATE. Nothing faster than that.