Unsubscribe data view Timed Out

Question

I'm trying to get all the unsubs in MC with Unsub Data view, the query that I'm using :

select
j.JourneyName,
ja.ActivityName as 'EmailName',
su.EmailAddress,
u.SubscriberKey as 'ContactKey',
u.SubscriberID as 'ContactID' ,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
u.JobID,
u.BatchID
from [_Unsubscribe] u
join [_Sent] s
on u.JobID = s.JobID
join [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ENT._Subscribers su
on s.SubscriberID = su.SubscriberID
where s.EventDate > dateadd(dd,-19,getdate())

In fact, this query doesn't work because it's timed out due to its complexity. I decided to split it like this :

select
s.TriggererSendDefinitionObjectID,
s.SubscriberID,
u.SubscriberKey as 'ContactKey',
u.SubscriberID as 'ContactID' ,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
u.JobID,
u.BatchID
from [_Unsubscribe] u
join [_Sent] s
on u.JobID = s.JobID
where s.EventDate > dateadd(dd,-19,getdate())" 

=> To ABC Data extension

select
j.JourneyName,
ja.ActivityName as 'EmailName',
su.EmailAddress,
t.ContactKey,
t.ContactID ,
t.DateUnsubscribed,
t.JobID,
t.BatchID
from [ABC] t
join [_JourneyActivity] ja
on t.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ENT._Subscribers su
on t.SubscriberID = su.SubscriberID

When I try the queries above, the first one is timed out. I tried an other way of split :

select
j.JourneyName,
ja.ActivityName as EmailName,
s.EventDate as SendTime,
su.EmailAddress,
su.SubscriberKey as ContactKey,
s.SubscriberID as ContactID ,
s.JobID,
s.BatchID
from [_Sent] s
join [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ent._Subscribers su
on s.SubscriberID = su.SubscriberID
where
s.EventDate > dateadd(dd,-19,getdate())

=> to ABC data extension

select
t.JourneyName,
t.ActivityName as EmailName,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
t.EmailAddress,
t.SubscriberKey as ContactKey,
t.SubscriberID as ContactID ,
t.JobID,
t.BatchID
from [abc] t
join [_Unsubscribe] u
on u.JobID = t.JobID

When I run the queries above, the first one is running successfully (2 million rows) but the second is timed out! This is so strange.

Can you help me with other solutions please? Thank you in advance!

Ilyass

Answer 1

Your queries are complex and not ideal as pointed out but are not the curl-pit.

System Data Views are built-in backend Data Extensions that can be queried directly. Because they are holding real-time data, you will find that read requests from your SQL can become deadlocked for write requests from other processes such as sent.

I would not recommend to query these type of Data Extensions directly but instead create a copy and use them instead.

You could use WITH (NOLOCK) which is the equivalent of using READ UNCOMMITED as a transaction isolation level. You stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. While it can prevent your read queries being deadlocked by other operations, it comes with the risk of wrong data.

If you do not want to make a copy (which I would recommend) you can try this:

SELECT
    j.JourneyName
    , ja.ActivityName AS 'EmailName'
    , su.EmailAddress
    , u.SubscriberKey AS 'ContactKey'
    , u.SubscriberID AS 'ContactID'
    , FORMAT(u.EventDate
        AT TIME ZONE 'Central Standard Time'
        AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') AS 'DateUnsubscribed'
    , u.JobID
    , u.BatchID

FROM [_Sent] s WITH (NOLOCK)

JOIN [_Unsubscribe] u WITH (NOLOCK)
    ON u.JobID = s.JobID

JOIN [_JourneyActivity] ja WITH (NOLOCK)
    on ja.TriggererSendDefinitionObjectID = s.JourneyActivityObjectID

JOIN [_Journey] j WITH (NOLOCK)
    ON j.VersionID = ja.VersionID

JOIN ENT._Subscribers su WITH (NOLOCK)
    ON su.SubscriberID = s.SubscriberID

WHERE 
    s.EventDate > DATEADD(dd,-19,GETDATE())