0

I can write some SQL, but I'm not good at knowing how to make things perform better. My query below works with one JobID, but I need to pull openers from 5 jobs. When I do this it times out. I'm thinking there is a better way to write this, but I'm still new to SQL and don't know much about what performs better than what. Any suggestions?

select distinct s.SubscriberKey
from _sent s
WHERE s.JobID IN ('2787871','2787957','2787958','2787970','2787971')
AND s.SubscriberKey NOT IN (

    SELECT o.SubscriberKey 
    FROM _Open o  
    WHERE s.JobID IN ('2787871','2787957','2787958','2787970','2787971')


)
2

I'd write it like this:

select distinct s.SubscriberKey
from _sent s
left join _open o on (
       o.jobid = s.jobid 
       and o.subscriber_id = s.subscriberid 
       and o.isunique = 1
)
WHERE s.JobID IN (2787871,2787957,2787958,2787970,2787971)
and o.subscriber_id is null

The jobid is an number, so treating it like one removes a datatype conversion. Also you can minimize the criteria to just the essentials in relating the views together -- jobid and subscriberid.

Update

Sometimes starting with Job will mitigate a timeout:

select distinct s.SubscriberKey
from _job j
inner join _sent s on (s.jobid = j.jobid)
WHERE j.JobID IN (2787871,2787957,2787958,2787970,2787971)
and not exists (
  select * 
  from _open 
  where o.jobid = s.jobid 
  and o.subscriber_id = s.subscriberid 
  and o.isunique = 1
)
|improve this answer|||||
  • ok but why did you change SubscriberKey to subscriber_id? for clarity sake? – garek007 Dec 1 '17 at 21:48
  • also I'm getting a validation error, looks like something is out of order here? – garek007 Dec 1 '17 at 21:54
  • oops. should be fixed now. – Adam Spriggs Dec 1 '17 at 22:01
  • subscriberid is an internal number and not a string, so it's inherently more performant. – Adam Spriggs Dec 1 '17 at 22:03
  • oh ok, but doesn't the _open table have subscriberid too? why did you put subscriber_id? – garek007 Dec 1 '17 at 22:06

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.