SFMC Query Timing Out

Question

I have a query that's assigns engagement definition to subscribers based on their interaction with the email. The dataViewSubscribers DE contains 16million users. However, the query times out now every time it's ran. Is there a way it can be optimised?

    SELECT DISTINCT ds.emailaddress, 
                    'New' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) >= 
           CONVERT(VARCHAR(10), Getdate() - 14, 102)

    UNION

    SELECT DISTINCT ds.emailaddress, 
                    'Active' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           INNER JOIN _open o 
                   ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 14, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) >= 
               CONVERT(VARCHAR(10), Getdate() - 90, 102)

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'Defining' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 14, 102) 
           AND CONVERT(VARCHAR(10), ds.datejoined, 102) >= 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND o.subscriberkey IS NULL 

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'At Risk' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON o.subscriberkey = ds.emailaddress 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND o.subscriberkey IS NULL 

    UNION 

    SELECT DISTINCT ds.emailaddress, 
                    'Inactive' AS EngagementDefinition 
    FROM   dataviewsubscribers ds 
           LEFT JOIN _open o 
                  ON ds.emailaddress = o.subscriberkey 
    WHERE  CONVERT(VARCHAR(10), ds.datejoined, 102) < 
                  CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) < 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 
           AND CONVERT(VARCHAR(10), o.eventdate, 102) !< 
               CONVERT(VARCHAR(10), Getdate() - 90, 102) 

Answer 1

I'd recommend combining this into a single query. Also converting to the date data type will is less costly than converting all of the dates to strings. Something like this:

SELECT  
ds.emailaddress
, case
    when ds.dateJoined >= convert(date, getDate()-14) then 'New' 
    when ds.dateJoined >= convert(date, getDate()-90)
         and ds.dateJoined < convert(date, getDate()-14) 
         and o.eventDate IS NULL then 'Active'
    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) then 'Inactive'
    else
      'Unclassified'
  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 

Loop through all of the DataViewSubscribers records and check the criteria in a CASE statement. The outer apply reduces all of the opens for a subscriber to a single value that you can use in your CASE statement.

Update: If it still times out, you can go through your subscribers in 4 queries by adding a where-clauses using the mod operator like this:

• Query 1: where ds._customobjectKey % 4 = 0
• Query 2: where ds._customobjectKey % 4 = 1
• Query 3: where ds._customobjectKey % 4 = 2
• Query 4: where ds._customobjectKey % 4 = 3

The first query would be an overwrite, then subsequent ones would be updates.