We have a SQL Query looking for _Open and _Click data that is failing because it keeps timing out

Question

The query is intended to populate LastClick and LastOpen data in our Master Subscribers DE.
Here is the query:

SELECT
a.Email,
a.LeadUID,
MAX(o.EventDate) as LastOpenDate,
MAX(c.EventDate) as LastClickDate
FROM [Master Subscribers] a
LEFT JOIN _Open o
ON a.Email = o.SubscriberKey
LEFT JOIN _Click c
ON a.Email = c.SubscriberKey
GROUP BY a.Email, a.LeadUID

After talking with support they have recommended that we "split the joins into multiple queries so the data can be filtered down sequentially."

I understand the logic behind this but am unsure how to go about implementing it. Could anyone help me to take a step in the right direction with splitting this query into more digestible pieces? Thank you.

Answer 1

Optimization is a bit of an art since the SQL engine will try and do some optimization for you and some changes you do may reduce the effectiveness of that but my suggestions are as follows

1) first data extension to group opens by last open date (keep date and subscriber ID)

2) second data extension to group clicks by last click date (idem)

3) add a new field to your master data extension called subscriber ID, then do an update query for that field to populate from the Subscribers data view.

4) join the first two tables using the subscriber ID as suggested by Tyler.

Side note: it is considered poor practise to use an email address as a subscriber key, I would recommend you look into replacing that with some other identifier - see more at https://help.salesforce.com/articleView?id=mc_es_subscriber_key.htm&type=5&sfdcIFrameOrigin=null

Answer 2

Short of splitting it up, joining to the click data view on o.subscriberid = c.subscriberid should help quite a bit.