Query timing out, but looks right

Question

Why isn't this working? I took this query

SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey IN ('40712')
AND o.TriggeredSendCustomerKey IN ('40712')
Group by s.TriggeredSendCustomerKey

Which works for one triggered send perfectly. Whenever I add more TriggeredSendCustomerKeys in the parenthesis, the numbers are off. I also don't like hard coding the number so I tried this, which validates and seems like it should work, but the system doesn't like it.

SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
Group by s.TriggeredSendCustomerKey

Answer 1

The s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey is way more computationally expensive than

AND s.TriggeredSendCustomerKey IN ('40712')
AND o.TriggeredSendCustomerKey IN ('40712')

Although the hard coding reducing flexibility, it limits the range of values that the system has to search.

Instead of just searching for 40712, it has to return every case where the two value are the same. That combined with running joins on a system table, and you have a query that is taking longer than 30 minutes and timing out.

To improve performance I would break it into 2 part.

1. The query without the Aggregate Functions(TableA)

   SELECT s.TriggeredSendCustomerKey as TriggeredSend
   ,o.TriggeredSendCustomerKey as Opens
   ,(case when o.IsUnique = 1 then 1 end) as IsUnique
   FROM _Sent s with (NOLOCK)
   JOIN _Open o with (NOLOCK)
   ON s.SubscriberKey = o.SubscriberKey
   WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
   AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
   

2. Query with Aggregates (TableB):

   SELECT TriggeredSend,
   COUNT(Opens) as TotalOpens,
   COUNT(IsUnique) as UniqueOpens
   FROM [TableA]
   GROUP BY TriggeredSend
   

Answer 2

I agree with @Data_Kid about breaking it up into two queries. Generally speaking, I try not to join multiple System Data Views in a single query -- simply because you're asking for a timeout if you have a high send volume. Here's how I'd approach it.

The first query selects only _Sent rows and does an overwrite on the target:

select distinct
s.TriggeredSendCustomerKey as TriggeredSend
FROM _Sent s
where 
s.EventDate >= '2017-08-01 00:00:00' 
and s.EventDate <= '2017-08-31 23:59:59'
and s.TriggeredSendCustomerKey IN ('40712','40713','40714')
/* name: TriggeredSendSummary_Sends */
/* target: TriggeredSendSummary */
/* action: overwrite */

The second query uses the results of the first query and inner joins with _Open and does an update:

select 
s.TriggeredSendCustomerKey as TriggeredSend
, COUNT(o.TriggeredSendCustomerKey) as TotalOpens
, COUNT(case when o.IsUnique = 1 then 1 else 0 end) as UniqueOpens
from TriggeredSendSummary s
inner JOIN _Open o ON o.SubscriberKey = s.SubscriberKey
group by s.TriggeredSendCustomerKey
/* name: TriggeredSendSummary_Opens */
/* target: TriggeredSendSummary */
/* action: update */