Query timing out, how can I optimize this?

Question

This query is timing out

SELECT s.TriggeredSendCustomerKey,
COUNT(s.TriggeredSendCustomerKey ) as TotalOpens
FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
GROUP BY s.TriggeredSendCustomerKey

I tried writing it like this, but this produced the ever so helpful generic 'System Error'

SELECT o.TriggeredSendCustomerKey, 
COUNT(o.TriggeredSendCustomerKey ) as TotalOpens
FROM _Open o with (NOLOCK)
WHERE o.SubscriberKey IN (
SELECT SubscriberKey FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
)
GROUP BY o.TriggeredSendCustomerKey

The 01_Individual_Emails_Sent_Last_Month_SDV is a Data Extension that contains about 3.3 million records from October. I pull this first to narrow down results. I don't want to add any date logic here, because it will screw up the TotalOpens number I get if I run later in the month (opens continue to get added to the _Open table and associated with a send even if the open occurs in the next month)

Any thoughts?

Answer 1

You are best limiting the query on _Open within a date range as it's otherwise a very heavy table to go querying. You will be limited to 180 days of the query anyway.

Also consider counting only unique opens, rather than every one - this will reduce your load.

Your first query (Join) will likely be more performant that your 2nd query -

The SQL query process time is limited to 30 mins - however you can raise a support request to have this increased (by exception). It wouldn't be advisable tough to increase beyond 120 mins.

You could also try something like below:

Select DISTINCT
SubscriberKey,
TotalOpens
FROM (

SortOrder = Row number() over (PARTITION by s.SubscriberKey ORDER BY TotalOpens DESC)
,s.SubscriberKey 
,COUNT(s.TriggeredSendCustomerKey) as TotalOpens

FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
LEFT JOIN _Open o with (NOLOCK) ON s.SubscriberKey = o.SubscriberKey

Where DateDiff(d,o.EventDate,GetUTCDate()) between 0 and 30 
And o.SubscriberKey is not null

) z
where SortOrder = 1