2

I'm a SQL neophyte.

I've written a query to find records across tables and it works, but I'd like to figure out make it the most efficient for speed. What are some options to make this query more efficient (faster)? Should I combine the EmailIDs with OR clauses since the DateDiffs are the same? Other ideas?

SELECT a.EmailName, b.SubscriberKey, c.EmailAddress
FROM _Job A
JOIN _Sent B ON a.JobID = b.JobID
JOIN _Subscribers C ON b.SubscriberID = C.SubscriberID 
WHERE 
((A.EmailID = '111111'AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR 
(A.EmailID = '111112' AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR 
(A.EmailID = '111113' AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR 
(A.EmailID = '111114' AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR
(A.EmailID = '111115' AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR
(A.EmailID = '111116' AND DateDiff(hh,b.EventDate,GetDate()) = 19) OR
(((A.EmailID = '111111' OR A.EmailID = '111112')) AND DateDiff(d,b.EventDate,GetDate()) = 1 AND (c.EmailAddress NOT IN (SELECT email FROM AbandonCartPurchases))))
  • Rule of thumb #1, never join more than one system data view in a query activity. – Adam Spriggs Sep 20 '17 at 16:51
  • what's the alternative? – George Rosedale Sep 20 '17 at 16:56
  • One query for each view, first one is an overwrite and then each subsequent one does updates. – Adam Spriggs Sep 20 '17 at 16:57
4

You can definitely simplify what you have:

SELECT 
  a.EmailName
, b.SubscriberKey
, c.EmailAddress
FROM _Job A
JOIN _Sent B ON a.JobID = b.JobID
JOIN _Subscribers C ON b.SubscriberID = C.SubscriberID 
WHERE 
(
 a.emailid in (111111,11112,111113,111114,111115,111116) 
 and DateDiff(hh,b.EventDate,GetDate()) = 19)
or (

    a.emailid in (11111,111112) 
    and DateDiff(d,b.EventDate,GetDate()) = 1 
    and c.EmailAddress NOT IN (
        SELECT email as emailaddress 
        FROM AbandonCartPurchases
    )
)

I'd recommend breaking it up into multiple queries:

  1. Select all _Job rows with certain emailIds, write them to a separate Data Extension
  2. Select all _Sent rows for jobs in Data Extension from Step #1 (inner join)
  3. Select all _Subscribers for sends in Data Extension from Step #2
| improve this answer | |
  • Thank you! In reading the above, should the last "and" section should be an or, since the DateDiff is a day instead of hours? – George Rosedale Sep 20 '17 at 17:08
  • Oh, right. Updated. – Adam Spriggs Sep 20 '17 at 17:11
0

The majority of your query's execution time will be spent on a seek through _Sent. You should be able to improve performance considerably by limiting the scope of the portion of _Sent seen by the query.

From what you have so far, it looks like you will never need anything in _Sent beyond the past 24 hours.

Try adding this to the end of your query:

AND B.EventDate > DATEADD(dd, -2, getdate())
| improve this answer | |
  • I would have thought he also needed 19 days ago re: DateDiff(hh,b.EventDate,GetDate()) = 19 ? – Samuel Dare Sep 20 '17 at 22:51
  • 1
    It's 19 hours - we're trying to find users that received an email that goes out every 15 minutes, so from midnight to a 7pm deployment time is 19 hours – George Rosedale Sep 21 '17 at 15:12
  • @GeorgeRosedale my bad should have caught that – Samuel Dare Sep 21 '17 at 17:26

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