SQL Query timing out - Optimization Help?

Question

I'm trying to run this as a query activity (writing to a data extension) but am finding that it's timing out. It's not all that 'heavy' of a query but it's not completing. I'm a database developer by trade...but don't know the intricacies of SalesForce query. For example - I've tried to introduce variables and CTEs into this query but I'm getting an error stating that 'select' must be the first word in the query.

    select
    j.JobID,
    j.emailname,
    j.emailsubject,
    a.Segment_Name,
    Count(distinct s.subscriberkey) Sends,
    Count(distinct o.subscriberkey) Opens,
    Count(distinct c.subscriberkey) Clicks,
    count(distinct u.subscriberkey) Unsubs,
    Count(distinct b.subscriberkey) Bounces,
    Count(distinct s.subscriberkey) - Count(distinct b.subscriberkey) Delivered
from
    _Job j 
    join _Sent s on j.JobID = s.JobID
    join [Snapshot - RPI_Standard_Audience 2_14_20] a on s.subscriberkey = a.email_address
    left join _Open o on j.JobID = o.JobID and
        a.email_address = o.subscriberkey
    left join _Click c on j.JobID = c.JobID and
        a.email_address = c.subscriberkey
    left join _Unsubscribe u on j.JobID = u.JobID and
        a.email_address = u.subscriberkey
    left join _Bounce b on j.JobID = b.JobID and
        a.email_address = b.subscriberkey


where
    j.JobID in ('4142867','4142864','4142863')
group by
    j.JobID,
    j.emailname,
    j.emailsubject,
    a.Segment_Name

Answer 1

A typical "error" leading to timeouts is that there simply are too many JOINs over the tables in the backend (data views). Solution would be to split up your query into several sequential parts. Often, this behavior will "creep" into your project as the data views are very small at the beginning, and with increased sendout volume, problems begin.

So, my advice is to be conservative and split into several rather boring queries to make use of some pre-processing.

So I'll outline a very broad schema, as you likely will have no issue with the queries themselves:

a) SELECT job / JOIN sent JOIN snapshot into stagingDE

b) At the same time you could create your counts in a "countsDE"

c) When a and b are done, run a LEFT JOIN of stagingDE and countsDE

Hope this helps!