Learn about our newest features and enhancements!
Query Best Practices
Jamie Beliveau 12/10/2021 3:46:00 PM

The CharityEngine® query tool is an extremely powerful back-office application that provides administrators nearly direct access to their database.  In addition to querying the database in real-time through the query builder interface, saved queries can be used to create scheduled reports, automate data updates using transformations, create or update records automatically using trigger jobs, and even to create dynamic audiences for one-time or automated marketing initiatives, to name a few.

When creating queries that will be used with other apps, it is important to consider the application of the query when designing it.  For example, if your query will be used to execute a one-time email blast, there are more stringent performance considerations that could impact your outcomes than, for example, a one-time report.  While you can re-run both in CharityEngine®, email blasts might have time-sensitivity associated with their execution, whereas rerunning a report can usually be done as an internal task with no external impact.

Regardless of the application, it is always recommended users follow database selection best practices in order to avoid slow load times and overwhelming result amounts. Remember, your query is a real-time selection against your database, so the more complex it is, the more load you will be placing on your system.  NOTE: This is especially true if you are running jobs in parallel.  Whether it be an initiative or a data job, running multiple complex queries at the same time can exacerbate the load on your database and dramatically slow down performance.

If a query runs in the user interface in CharityEngine® and produces results, there is usually nothing to be concerned about.  When queries are complex enough that they take longer than a few minutes to run and As a general rule of thumb, if a query takes longer than 5 or 10 minutes by itself (while no other heavy jobs are running) it is a pretty substantial workload.  That does not mean it is wrong, incorrect or undesirable, it just means that you need to be aware that it is doing a lot and warrants special consideration.  Any queries that take longer than 30 minutes should be looked at by a senior administrator to ensure that there is not a better way to implement them.


Common Performance Mistakes

  1. Too Many Layers: Layers are what are created when you use subqueries. While subqueries are an important and useful component, “layering” occurs when a subquery is created from a subquery, and multi-layer queries like this will cause your database to perform a recursion, which will always cause your query to slow down.
  2. Unnecessary Selections: While it is nice to see a lot of data on the screen, unnecessary select fields, especially in subqueries where they are not seen, can cause your database to do unnecessary work. We recommend adding extra fields when troubleshooting, but keep the main query free of unnecessary select fields by choosing only the bare minimum fields to run the query with.
  3. Too Many Subqueries: Like layers, too many subqueries can also lead to performance issues. Each subquery is its own set of database instructions and adds to the complexity of your main query. Do not overuse subqueries.


Helpful Tips - General

Here are some tips to help you optimize your query if it is taking too long to run. Note that when a query is the target of a campaign email message, the speed at which a query is run will impact the delivery of the message. For email marketing messages, the query will time out after 2 hours and your message will fail to send. This timeout is a safety mechanism to ensure that you do not have runaway campaigns or queries that impact your database for too long.  When a campaign audience query times out, the email campaign will fail with a message that it could not execute the query.

1. When possible, use IDs

Queries will run faster if you use an ID rather than the name of something to filter. For example, if you want to include contacts who have submitted on a specific form in your query, use the Form ID filter rather than the Form Name.

2. If you want to suppress contacts for email sends, utilize the built-in email suppression features

Here is a good example of a global suppression - if your organization is a provider of social services in your community, and you do not want the clients you support to receive marketing emails, you can build a query that only includes contacts with the client status or group and make that query your global suppression in the Campaigns Configuration section of CharityEngine®.

Additionally, by default, contacts who have opted out of emails, are deceased, or on your blacklist because of prior issues delivering messages to their email address are excluded through the built-in system suppression, so you do not need to filter out contacts who meet this criteria in your email queries.

Global suppression and system suppression filters like “deceased” are applied separately from the rest of the query, which is why it is faster to run when emails are set to send.

3. If your query has multiple filters or subqueries, make sure you are only referencing the same filter one time

It is easy to add a filter to your query criteria, but every time a filter is added, it makes the query itself more complex, and therefore takes more time to run.

For example, let’s say you want to build a query of contacts to send an email blast out to (contacts subscribed to a particular email list). You want the recipients to have given a certain amount, have attended an event in the last three years, and live in a particular area. To minimize slow load times, include the email list in the main query, and include the other requirements (event registrants, donor amounts, and zip code range) in the subqueries. This will create four queries instead of seven (seven queries would come from using the email list filter next to every other query) and will minimize load time.

4. Review your report and email criteria at least once a year

It is always a good idea to review your criteria on a scheduled basis, especially with email marketing lists. You may want to segment your data in a different way based on how your messages are performing, or maybe you need a report to run more frequently.

5. Consider a list if your data does not change often. 
As mentioned earlier, queries are a real time look at your database, and if the criteria for your marketing emails does not require that, try using an Opt In list instead.

Related Articles

Powered by Powered By CharityEngine