Loading

Marketing Cloud Engagement: Query Activity | Add/Update | Recommendations for scale

Date de publication: Jul 3, 2025
Description
Sometimes Query Activities that used to run and complete without issue may begin slowing down or even being stopped at the 30-minute maximum.   The primary scenario that this article applies to is an Add/Update query activity.  Typically this is caused by significant growth in the amount of data being queried and consequently written to a target table.   Alternatively, over time, the target table may become larger than it initially was.  


When the overall data footprint that is being leveraged increases, the run time of Add/Update Query Activities tends to increase in execution time linearly.   Other factors such as the overall logic of the query and the structure of the data being queried will also affect this.  The majority of time that Add/Update queries take to execute is often spent attempting to check for the existence of each row they are writing prior to the add or update action taking place within the table. 
Résolution

A simple recommendation may alleviate this problem without any significant overhaul/modifications to the Query Activity.   Specifically, what you'll want to do is stage the data that will be Added/Updated to an intermediate Data Extension before writing it to the target Data Extension.   


This will allow you to capitalize on the most expensive portion of the Query Activity itself which is the period of time where the Query checks for existence and writes each row as mentioned above.  Doing this will allow us to leverage the Import method available within Contact Builder that allows you to use the "From Existing Data Extension" source.   Import Activities are capable of doing the same Add/Update operation that a Query activity does faster and at a larger scale more efficiently than a Query activity is able to do.  If you have a Query Activity that exists simply to copy its records (without a where clause) from one Data Extension to another, this methodology would also be superior and should be used as an alternative. Of note, utilizing a Data Copy Import activity will be much more reliable, however it can increase the time the activity runs, which is due to the batching process for imports. There is no timeout for imports, though, like there is for Query Activities.


Here are the steps that you would leverage to do this:
 

  1. Make a copy of your Add/Update Query Activity's Target Data Extension that we refer to as Target13.   This will serve as your intermediate Data Extension, we'll call it Interim1.
  2. Change your existing Add/Update Query Activity to overwrite the new Data Extension Copy that you made
  3. Within Automation Studio, you can utilize the Data Copy or Import Activity then create the Import Definition.  
  4. Select the source of the Import to be from "Data Extension" and set the source as Interim1 from Step 1(Full instructions for creating an Import can be found here ).
  5. Set the target of this Import to be your original Data Extension (Target13) that you began with. Set the Import Type to Add and Update and complete its mapping.
Numéro d’article de la base de connaissances

000396776

 
Chargement
Salesforce Help | Article