2

I've set up an automation that resends an altered version of a mail to all who recieved it the first time it was sent, but didn't open it.

This is done in Automation Studio using 3 SQL Query and a Send Email activities.

Here's the SQL for the three SQL Query activities:

SELECT s.subscriberKey, j.jobid, sub.EmailAddress
FROM [_job] j 
JOIN [_Sent] s ON s.jobid = j.jobid AND s.EventDate > dateadd(d,-7,getdate()) AND j.EmailName like '%TOBERESENTDK%'
LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey

SELECT s.subscriberKey 
FROM [AutomatedRetargetStep1of3DK] s
JOIN [_Open] o ON s.jobid = o.jobid AND s.subscriberkey = o.subscriberKey

SELECT s.subscriberKey, s.EmailAddress
FROM [AutomatedRetargetStep1of3DK] s
WHERE s.subscriberKey NOT IN (SELECT subscriberKey FROM [AutomatedRetargetStep2of3DK])

This automation is set up for 5 countries, and typically the largest sends are approx. sent to 80.000 subscribers, while the smallest sends approx. goes out to 8000 subscribers.

Usually, when I runs these automations, the 2 automations to Switzerland and Sweden (8.000 and 40.000 subscribers) are run successfully, while the 3 remaining automations to Denmark, Germany and The Netherlands (80.000, 75.000 and 56.000 subscribers) times out during the second SQL Query giving me this error: Error: Query failed: Timeout

So, I'm puzzled, I've seen people struggle with SQL queries handling millions or rows, and really hadn't seen this issue comin'.

So, help would really be appreciated. Anyone with more experience than me having an idea if it's really the larger amount of subscribers in the automations being the problem.. or if I'm doing anything else in a inappropriate way that makes these automations time out?

THANK YOU

The three Data Extensions for the SQL Queries.

AutomatedRetargetStep1of3DK enter image description here

AutomatedRetargetStep2of3DK enter image description here

AutomatedRetargetStep3of3DK enter image description here

  • You named your Dataextensions 1 2 3, are they AutomatedRetargetStep1of3DK, AutomatedRetargetStep2of3DK and AutomatedRetargetStep3of3DK? – Johannes Schapdick Sep 5 '18 at 10:53
  • Yes, let me just edit the question above to avoid misunderstandings :) – FelixSanchez Sep 5 '18 at 11:00
  • Can you please change all the data views table joins comparison to s.subscriberid = o.subscriberid instead? Try to use subscriberId on the table joins if it is available. – Brad Sapkota Sep 5 '18 at 11:30
  • Would it help if your second query would use INNER JOIN instead of JOIN. I believe JOIN (not certain) is cross join and takes a lot more time – Johannes Schapdick Sep 5 '18 at 11:30
  • The keyword join defaults to an inner join, so that part is fine – Tyler Jul 19 at 2:58
5

Here's how I'd approach these queries.

The date calculation can be simplified and updated to handle the time portion of the timestamp.

SELECT 
  s.subscriberKey
, s.jobid
, sub.EmailAddress
FROM [_Sent] s 
INNER JOIN [_job] j ON j.jobid = s.jobid 
INNER JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey
where s.EventDate > convert(date, getDate()-7) 
AND j.EmailName like '%TOBERESENTDK%'

I'd throw in the isUnique condition which will limit the results to the essentials:

SELECT 
  s.subscriberKey 
FROM [AutomatedRetargetStep1of3DK] s
INNER JOIN [_Open] o ON s.jobid = o.jobid AND s.subscriberkey = o.subscriberKey
where o.isunique = 1

I generally stay away from IN and NOT IN. EXISTS and NOT EXISTS are faster, in my opinion.

SELECT  
  s.subscriberKey
, s.EmailAddress
FROM [AutomatedRetargetStep1of3DK] s
WHERE NOT exists (
    select * 
    from [AutomatedRetargetStep2of3DK] s2 
    where s2.subscriberkey = s.subscriberkey
)
  • 1
    you can also turn the NOT EXISTS into a LEFT JOIN. I find doing a left join is the most efficient way to handle this. Something like: LEFT JOIN [AutomatedRetargetStep2of3DK] s2 ON s.subscriberkey = s2.subscriberkey WHERE s2.subscriberkey IS NULL – Gortonington Sep 5 '18 at 12:37
  • 1
    I've done it both ways. I've found the performance difference to be negligible. I'm try to opt for something less abstract and easier for someone to read and understand. I should really test it on a large data set and see how much different it actually is. – Adam Spriggs Sep 5 '18 at 13:15
  • 2
    In general SQL practice, I am 100% with you in that NOT EXISTS is faster. I get the feeling though that there may be Linked Servers or something else going on in the back end of SFMC (likely to help keep the info secured) that reduces the efficiency of EXISTS and IN, but keeps JOIN around the same speed. It is especially noticeable on very large quantities of rows and/or columns. – Gortonington Sep 5 '18 at 15:54
  • Hi Adam. Thank you. Have tried your changes to the markets for Switzerland, Denmark and The Netherlands (8.000, 80.000 and 56.000 subscribers) and the outcome was very much like the result I had with the old code. The automation for Switzerland ran succesfully (time: approx 2 minutes), while the automation for Denmark and The Netherlands still timed out after 30 minutes working on the second SQL query. I'm not saying the code is not optimized, but telling that I'm still having issues running these automations. Any idea if the problem could be something else than the SQL queries? – FelixSanchez Sep 6 '18 at 9:13
  • Why are your subscriberkey fields 100 and 50 characters instead of the standard 254? – Adam Spriggs Sep 6 '18 at 11:47
4

One of the most important callouts that was not made here would be contained in the following query syntax:

INNER JOIN [_job] j ON j.jobid = s.jobid 
INNER JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey
where s.EventDate > convert(date, getDate()-7) 
AND j.EmailName like '%TOBERESENTDK%'

It is imperative that instead of:

INNER JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey

This is done:

INNER JOIN _Subscribers sub ON s.SubscriberID = sub.SubscriberID

SubscriberKey is not an indexed field on the _sent data view. Changing this will make the query, by one modification, execute in a fraction of the time that it otherwise would and will also make it scale far better.

  • Easiest, most overlooked optimization. – Tyler Jul 19 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.