Query timeout from multiple LEFT JOINS

Question

I'm in need of some query help. I'm currently trying to Join multiple tables on Salesforce Marketing Cloud but it keeps timingout. Each table has a field called "SubscriberKey" and "EventDate" for Sent, Opens, Clicks, Unsub which I want to pull all together. This way i can see the subscribers and their actions from an email deployment. Hope that makes sense. Below is my query:

SELECT 
s.SubscriberKey, 

min(s.EventDate) as [EventSent],
min(o.EventDate) as [EventOpens],
min(c.EventDate) as [EventClicks],
min(u.EventDate) as [EventUnsubs]

FROM _Sent s

LEFT JOIN _Open o 
ON s.SubscriberKey = o.SubscriberKey

LEFT JOIN _Click c 
ON s.SubscriberKey = c.SubscriberKey

LEFT JOIN _Unsubscribe u 
ON s.SubscriberKey = u.SubscriberKey

WHERE s.jobid = 695597
GROUP BY s.[SubscriberKey]

Answer 1

While valid, it's unlikely this will never run successfully -- even for a single job. The data views are just not that performant, unfortunately.

I'd suggest one query for every activity type.

The first one based on _Sent that does an overwrite and the subsequent three are updates and based on the rows resulting from the first query.

You should also join on SubscriberID as @Chris suggested.

If you don't want to do that, I'd suggest mirroring the data views in your own set of Data Extensions and refreshing them daily. That way you can control the primary keys.

These work-arounds are a ton of unnecessary work and, frankly, a big pain in the butt. Believe me, I know.