0

I have a DE extension where I have saved 1 record by querying:

DE NAME: DataView_Job_DE

Select top 1
JobID
,EmailName
,CreatedDate
,JobType
,JobStatus
,Row_Number() OVER (Partition by jobid ORDER BY Jobid) as rowNumber
from _job

Now, I am trying to use the jobid from this output and use it in my main query where I am looking to get following:

Expected output: Records from sendable audience who did not make it to _SENT log without hardcoding the jobid

SELECT
a.CustomerID,
d.EmailName AS EmailName,
convert(varchar (10), d.CreatedDate, 23) AS SendDate,
'UNSUCCESSFUL SEND' AS Category,
d.JobID AS JobID,
FROM ENT.Contacts_Sendable_DE a
INNER JOIN ENT.DataView_Job_DE d on d.rowNumber = 1
LEFT OUTER JOIN _sent s
on a.CustomerID = s.SubscriberKey AND s.jobid = d.jobid
WHERE s.SubscriberKey IS NULL

Question: The above query is giving me expected output but Is there any other optimized way to do this? It looks like the query is taking a little longer than than usual so I am wondering if there is any better approach? Please advise...

1 Answer 1

0

The main problem will be working with the "raw" data view (_sent) which is typically large and known for a tendency to be slow. It is generally a good idea to regularly mirror the data view into a data extension, then work in your queries with the copy.

also, WHERE NOT EXISTS is marginally more performant than LEFT JOIN WHERE b.Key IS NULL pattern I think, but that won't make much of a difference.

SELECT
a.CustomerID,
d.EmailName AS EmailName,
convert(varchar (10), d.CreatedDate, 23) AS SendDate,
'UNSUCCESSFUL SEND' AS Category,
d.JobID AS JobID,
FROM ENT.Contacts_Sendable_DE a
INNER JOIN ENT.DataView_Job_DE d on d.rowNumber = 1
WHERE NOT EXISTS
 ( SELECT 1 FROM
    _sent s
    WHERE a.CustomerID = s.SubscriberKey
    AND s.jobid = d.jobid )

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .