Most efficient way to update a column where Id exists in another table

Question

I have several tables which correspond to fiscal_years (2017, 2018, 2019, 2020, etc). Each of these tables contain a record_id and an owner_id - with the record_id being unique within a given year. Each of the fiscal_year tables contain 6-8 million rows, and the owners table contains 1 million rows. Each of these fiscal_year tables contain a process_date field meant to represent the date when the owner_id was added/found/moved.

I have created a dynamic sql function that can retrieve the owner_ids unique among all of these tables and put them into an owner table - this part works fine and takes appx. 5 min to complete.

However, I am struggling to update the source table's (fiscal_years) process_date field if its owner_id exists in the owner table.

I have tried this:

update fiscal_year y
set process_date = now()
where exists (select owner_id from owners o where o.owner_id = y.owner_id)
and process_date is null;

But predictably, this query ran for 2 hours before I stopped it.

I also tried to use an etl tool to update the fiscal_year table using owners.owner_id as a key. But this method never finished either - presumably because there are many owner_ids that don't exist in each fiscal_year table.

How can I update the fiscal_years table when the owner_id is present in the owners table?

Any help on this would be greatly appreciated.

Answer 1

use of exists like that will cause repeated querying of owners for each row in fiscal_year

this will be better:

 update fiscal_year y
 set process_date = now()
 where y.owner_id in (select owner_id from owners)
 and process_date is null;

Here owners will be scanned once and the results remembered and re-used to verify each row.

Answer 2

I believe by doing an EXISTS on a subquery you may be using a sub-optimal way to accomplish this. Why not emulate an INNER JOIN directly like so:

update fiscal_year y
set y.process_date = now()
from owners o
where y.owner_id = o.owner_id
and y.process_date is null;

Having an index on (owner_id, process_date) for the fiscal_year table should help ensure the most optimal operations occur in this case too.

Your tables aren't big by any means, this process should be able to execute in minutes if not seconds, as long as everything is architected properly. I'd be curious what the above yields you (with the recommended index).