Problem with SQL that i want to JOIN _Sent with DE

Question

I have a problem with my SQL where my goal is to create a Data Extension that the records will populate with the number of sent that a subscriber inside in one DE received by the month:

The problem is: ERROR: Automation failed due to system error. What I did wrong?

SELECT count(*) AS TotalSent,  
DATENAME(month, p.EventDate) as [Month],
p.SubscriberKey as SubscriberKey
FROM _Sent p 
INNER JOIN [Clients_DE] B ON p.SubscriberKey = B.AccountID
WHERE p.EventDate BETWEEN GETDATE()-30 AND GETDATE()
GROUP BY p.EventDate, p.SubscriberKey
HAVING COUNT(*) > 0

Answer 1

I am answering under the assumption that this activity is erroring after about 30 minutes due to a timeout. SubscriberKey is not an indexed field on the _Sent data view. Ideally, when joining the send / open / click views together, one would join these together on SubscriberID, JobID, ListID, BatchID. Since for this use case you are not joining these views together but rather a Custom Object to the view, it would be more performant to join on the SubscriberID field from _Sent back to your DE. It might take a little more data staging. By that I mean, you might need to query the _Subscriber data view to get the SubscriberID staged into your Clients_DE data extension. If you are able to do that, joining from _Sent to Clients_DE on SubscriberID will perform more efficiently.

Hope this helps.