--- schema_version: "1" id: e2fc4f9c78b7bb955003d0ca2c21dcad20c7051bfd35c1f3dabbe742ec2501f1 title: Use Intermediate Tables to Optimize a Query collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/mc_as_use_intermediate_tables_to_optimize_a_query.md canonical_url: https://help.salesforce.com/s/articleView?id=mktg.mc_as_use_intermediate_tables_to_optimize_a_query.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: 7c1b32726a31e5eb799a9bf009229e83d13a3c14d878c748ee522565bbe60809 description: Split large queries into smaller components to use with Automation Studio’s query activity. products: - Marketing - Marketing Cloud Engagement release: "264" --- # Use Intermediate Tables to Optimize a Query Split large queries into smaller components to use with Automation Studio’s query activity. 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.
Data Extension NameJobTable
External KeyJobTable
## Fields | Name | Data Type | Length | Primary Key | Nullable | Default Value | | --- | --- | --- | --- | --- | --- | | JobID | Number |   |   |   |   | | EmailName | Text | 100 |   |   |   | | DeliveredTime | Date |   |   |   |   | ## Create a Query Activity to Populate the Job Table
Query NameJobTable
External KeyJobTable
## Query Syntax ``` SELECT j.JobID, j.EmailName, j.DeliveredTime, FROM [_Job] j WHERE j.EmailID = 123456 ```
Target Data Extension NameJobTable
Data ActionOverwrite
## Create an Opens Data Extension Create a data extension, then select standard data extension.
Data Extension NameOpenTable
External KeyOpenTable
## Fields | Name | Data Type | Length | Primary Key | Nullable | Default Value | | --- | --- | --- | --- | --- | --- | | JobID | Number |   |   |   |   | | SubscriberID | Number |   |   |   |   | | OpenTime | Date |   |   |   |   | ## Create an Opens Query Activity
Query NameOpenTable
External KeyOpenTable
## Query Syntax ``` SELECT o.JobID, o.SubscriberID, o.EventDate AS OpenTime FROM [_Open] o WHERE o.IsUnique = 1 ```
TargetData ExtensionOpenTable
Data ActionOverwrite
## Create a Subscriber Data Extension Create a data extension, then select standard data extension.
Target Data Extension NameSubscriberTable
External KeySubscriberTable
## Fields | Name | Data Type | Length | Primary Key | Nullable | Default Value | | --- | --- | --- | --- | --- | --- | | SubscriberID | Number |   |   |   |   | | EmailAddress | EmailAddress | 254 |   |   |   | | SubscriberKey | Text | 254 |   |   |   | ## Set Up a Subscriber Query Activity
Query NameSubscriberTable
External KeySubscriberTable
## Query Syntax ``` SELECT s.SubscriberID, s.EmailAddress, s.SubscriberKey FROM [_Subscribers] s JOIN [OpenTable] o ON s.SubscriberID = o.SubscriberID ```
Target Data ExtensionSubscriberTable
Data ActionOverwrite
## Create a Data Extension to Store Opens Data Create a data extension, then select standard data extension.
Target Data ExtensionOpensIn30Days
External KeyOpensIn30Days
## Fields | Name | Data Type | Length | Primary Key | Nullable | Default Value | | --- | --- | --- | --- | --- | --- | | JobID | Number |   |   |   |   | | EmailName | Text | 100 |   |   |   | | SendTime | Date |   |   |   |   | | OpenTime | Date |   |   |   |   | | EmailAddress | EmailAddress | 254 |   |   |   | | SubscriberKey | Text | 254 |   |   |   | ## Create a Query to Send Opens Data to Target Data Extension
Query NameOpensIn30Days
External KeyOpensIn30Days
## 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 ```
Target Data ExtensionOpensIn30Days
Update TypeOverwrite