Data view updates timing out in marketing cloud

Question

I have written 3 queries to extract and store data from the Click, Open and Bounce views and I have set them to update so only the delta should be added.

When I set the update type to overwrite they work, but when I set them to update only they keep failing with a timeout message.

Can anyone tell me why the update would be timing out when the overwrite works perfectly and is dealing with much bigger amounts of data?

Query text below for the click query, but the others are all similar.

Primary keys on the DE are Event Date, URL, Subscriber ID and Job id as I only want one click on each link on each day for each job.

SELECT
c.AccountID 
,c.JobID
,c.SubscriberID
,c.SubscriberKey
,c.EventDate
,c.URL
,j.EmailID
,j.EmailName

From _Click c
INNER JOIN _Job as J on j.jobID = c.jobID
Where IsUnique ='True'

Answer 1

So with data views, they contain about 180 days worth of rolling data, this amount of data based on activities within a MC account can end up quite large over time.

Overwriting queries is always quicker because it does not require you to iterate through each matching record to perform an upsert.

In order to prevent timeouts you should add a WHERE clause that only pulls in the last day of engagement data because historical engagement data does not change, it is simply a log of data that just keeps getting appended, so in order for you to bring the delta data through for example,

If you were running your queries daily, you would do something like this:

SELECT
c.AccountID 
,c.JobID
,c.SubscriberID
,c.SubscriberKey
,c.EventDate
,c.URL
,j.EmailID
,j.EmailName

From _Click c
INNER JOIN _Job as J on j.jobID = c.jobID
Where IsUnique ='True' AND
CAST(EventDate AS Date) > CAST(DATEADD(dd, -1, CURRENT_TIMESTAMP) AS Date)

This will allow you to retrieve delta data by only getting the last 1 day worth of data and updating your target data extension.

This approach is fast and easy.