Use Intermediate Tables to Optimize a Query

Learn how to divide a single query into multiple, more efficient queries. In this example, a query designed to find Opens in the last 30 days based on an EmailID consistently times out. Don't use this code in a query activity.

SELECT
j.JobID,
j.EmailName, 
j.DeliveredTime AS SendTime, 
o.EventDate AS OpenTime, 
s.EmailAddress, 
s.SubscriberKey 
FROM [_Job] j
JOIN [_Open] o 
ON j.JobID = o.JobID
JOIN [_Subscribers] s
ON o.SubscriberID = s.SubscriberID
WHERE 
o.IsUnique = 1 AND 
j.EmailID = 123456

This query may time out because it tries to retrieve data from multiple sources in one query. To make sure that this query consistently returns data in a timely manner, divide it into four smaller queries on separate, consecutive steps of your automation. Use these instructions to set up four data extensions and corresponding queries called Jobs, Opens, Subscriber, and Target Data Extension. The Target Data Extension unites and stores the data retrieved by the preceding queries.

Create a Jobs Table Data Extension

Create a data extension, then select standard data extension.

Fields

Create a Query Activity to Populate the Job Table

Query Syntax

SELECT
j.JobID,
j.EmailName, 
j.DeliveredTime, 
FROM [_Job] j
WHERE
j.EmailID = 123456

Create an Opens Data Extension

Create a data extension, then select standard data extension.

Fields

Create an Opens Query Activity

Query Syntax

SELECT
o.JobID,
o.SubscriberID,
o.EventDate AS OpenTime 
FROM [_Open] o 
WHERE 
o.IsUnique = 1

Create a Subscriber Data Extension

Create a data extension, then select standard data extension.

Fields

Set Up a Subscriber Query Activity

Query Syntax

SELECT
s.SubscriberID,
s.EmailAddress, 
s.SubscriberKey 
FROM [_Subscribers] s
JOIN [OpenTable] o
ON s.SubscriberID = o.SubscriberID

Create a Data Extension to Store Opens Data

Create a data extension, then select standard data extension.

Fields

Create a Query to Send Opens Data to Target Data Extension

Query Syntax

SELECT
j.JobID,
j.EmailName, 
j.DeliveredTime AS SendTime, 
o OpenTime, 
s.EmailAddress, 
s.SubscriberKey 
FROM [JobTable] j
JOIN [OpenTable] o 
ON j.JobID = o.JobID
JOIN [SubscriberTable] s
ON o.SubscriberID = s.SubscriberID