You set up a query with different criteria including field data (Field1 is equal to 'Yes'), sending history (Has been sent Email1 in last 60 days), and web tracking (Has visited Page1 in last 60 days). When you calculate it on Monday, it takes only a minute or two, but on Tuesday it takes 20 minutes or more. Why would your query processing times fluctuate like this, despite the criteria remaining identical?
To understand why the time would fluctuate, review what the Acoustic Campaign does when a query is processed:
What happens when Acoustic Campaign "processes" a query?
A query is nothing more than a set of criteria that can be leveraged at any time to filter down the contacts in a database based on their field data, behavioral history, etc... Every time a search is done within a query, or when it is calculated, or when it is sent to, Acoustic Campaign "executes" the set of logic constructed within the query to evaluate if certain contacts within the parent database belong to the query.
Technically speaking, Acoustic Campaign takes the English criteria that you've built in the query to construct a single SQL statement to run against the various tables that hold the needed data. Behavioral data relating to opens and clicks is held in a different table than visits to a web page, or Relational Table data, for example. This SQL statement collects or counts the Recipient Ids (Acoustic Campaign's Unique Identifier) that match your inputted logic. When the statement is executed against the Database, the platform evaluates the resources it will need to complete the process and optimizes a path for the statement to efficiently run through the necessary tables. For a query calculation in particular, we run the statement and after the list of Recipient IDs is built, we count how many records are returned and that's reflected on the updated size description. For an email using a query, we similarly build out a list of Recipient IDs, but then employ those Recipient IDs for sends paired with the associated email addresses, field data, etc.
Why would a query process slowly/differently than before?
While not exhaustive, the following are common reasons for slowness and fluctuations when processing a query:
- The SQL statement Acoustic Campaign builds out based on the English criteria can become large and complicated, especially when "Is In query" sub-queries are included.
- The data the statement has to read can fluctuate in size and complexity.
- Other processes have modified the data the Acoustic Campaign is reading while the query is still processing.
- The data needed by the query was recently accessed and remained in cache.
- There could simply be "System Load" on the platform and read times could be slower than a non-peak time.
- The underlying data is invalid.
What happens when there are multiple queries running at the same time?
This might occur most often when multiple emails (that have a query as their contact source) are processing at the same time on the same parent Database. If the data that one query is accessing is required by the other, the other will need to temporarily wait. And while it's waiting, it's not able to process other elements of the query. So, in a way, queries queue against each other for resources when they simultaneously need the same block of data. That's why we recommend staggering emails to Queries, so one has time to complete before the other, or using 'Pre-Processing' so Queries can execute with plenty of time prior to the send time.