0

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.

3
  • Please consider reading this advice
    – mustaccio
    Commented Apr 6, 2021 at 2:00
  • Thank you for your link, @mustaccio, I will edit my question appropriately.
    – OGmaps
    Commented Apr 6, 2021 at 2:07
  • Hi, and welcome to the forum! Could you please provide your table structures - fiscal_year and owners (as text and with indexes).
    – Vérace
    Commented Apr 6, 2021 at 7:25

2 Answers 2

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.

1
  • I can't say if this approach or the join proposed by JD will perform best, try both. most efficient probaby depends on what your data looks likel
    – Jasen
    Commented Sep 22, 2022 at 0:32
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.