Optimize a query that is timing out

Question

I have two data extensions that I need to join and filter. One stores member data for a product, the other stores opt-in status for all products. The DE with opt-in status can have multiple instances of the same person, one for each product they're opted in to. I need to find people who are in the members table who either don't have a record that associated with the product or who are associated with the product but have opted out.

I've tried several different methods of finding this result. Pulling records that are opted out of the product gives me too low of a number because it's not accounting for people who aren't associated with the product at all. Pulling records who aren't associated with the product or are opted out of the product gives me too high of a number because it's pulling in the instances of people who are associated with multiple other products.

At this point, I have two queries. One will find the primary keys for all records that are opted in to the product and store them in a data extension called "Subscribed Emails." It runs just fine and returns about 6.6 million records. The query below should just find primary keys in the members table that don't exist in the subscribed emails table. However, it keeps timing out. The members table has around 13 million records in it. Is it possible that comparing 13 million and 6.6 million records is too much work? What can I do to simplify this query even further?

select m.AccountID from members m
where not exists (
    select AccountID from [Subscribed Emails]
    )

Answer 1

Your not exists clause doesn't contain any connection back to your source Data Extension, so it's not surprising that it's timing out.

I'd approach it like this:

select distinct
m.AccountID 
from members m
where not exists (
    select top 1 e1.*
    from [Subscribed Emails] e1
    where e1.accountID = m.accountid
) or exists (
  select top 1 e2.*
  from [Subscribed Emails] e2
  where e2.accountID = m.accountid
  and e2.status = 'unsubscribed'
)

Answer 2

One solution would be to add a roll-up-summary field on the Members record that contains the number of [Subscribed Emails]. Then your query is only going against one table.

Answer 3

In a moment of desperation, I tried using "Not In" instead of "where not exists", even though "where not exists" has worked for me in the past. The following query ran without timing out. I think that being able to specify the field I wanted to compare improved the efficiency.

select m.AccountID from members m
where m.AccountID not in (
    select AccountID from [Subscribed Emails]
    )