SQL Query Timeout Salesforce Marketing Cloud

Question

I have a query running in Automation studio that times out. The dataViewSubscribers data extension contains 20 million users. Is there a way this can be optimised?

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when o.eventDate >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) then 'Active'
    when ds.dateJoined < convert(date, getDate()-14)
         and ds.dateJoined >= convert(date, getDate()-90)
         and o.eventdate IS NULL then 'Defining'
    when ds.dateJoined < convert(date, getDate()-90) 
         and o.eventdate IS NULL then 'At Risk'
    when ds.dateJoined < convert(date, getDate()-90)
         and o.eventdate < convert(date, getDate()-90)
         and o.eventdate !< convert(date, getDate()-90) then 'Inactive'
  end AS EngagementDefinition 
FROM DataviewSubscribers ds 
outer apply (
  select 
    max(o0.eventDate) eventDate
  from _Open o0
  where o0.subscriberid = ds.subscriberid
  and o0.isunique = 1
) o where ds._customobjectKey % 6 = 0

Answer 1

Looks like you're utilizing all of the tricks already.

I'd build a new DE for the Opens and revise your query left join to it.

Opens query:

select
o0.subscriberid
, max(o0.eventDate) eventDate
from _Open o0
where o0.isunique = 1
group by o0.subscriberid
/* name: MostRecentOpens */
/* target: MostRecentOpens */
/* action: overwrite */

Then I'd repeat this pattern in your 6 subsequent queries:

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when o.eventDate >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) then 'Active'
    when ds.dateJoined < convert(date, getDate()-14)
         and ds.dateJoined >= convert(date, getDate()-90)
         and o.eventdate IS NULL then 'Defining'
    when ds.dateJoined < convert(date, getDate()-90) 
         and o.eventdate IS NULL then 'At Risk'
    when ds.dateJoined < convert(date, getDate()-90)
         and o.eventdate < convert(date, getDate()-90)
         and o.eventdate !< convert(date, getDate()-90) then 'Inactive'
  end AS EngagementDefinition 
FROM DataviewSubscribers ds 
left join MostRecentOpens o on o.subscriberid = ds.subscriberid
where ds._customobjectKey % 6 = 0

If it still times out, I'd consider adding a query for each EngagementDefinition instead of breaking the DataViewSubscribers into 6 groups.