The Query Tool is a mighty one because of its ability to wrangle complex sets of data to answer your trickiest questions. But even this tool has its Achilles heel. You may have even seen an error message that looks like this:
If you've gotten this error message, there's a good chance you've found it.
So, What is it?
Queries can handle a lot. But sometimes, they get a little overwhelmed by how many choices they have to make to give you the data you need. Here is what will overwhelm them most:
- Too many "OR" statements
- Too many Nested Queries, meaning that there are too many Queries inside one Query
Why Can't They Keep Up?
"OR" statements and Nested Queries create layers of decision-making to rule out data, and the more layers there are in a Query, the more likely it is to crash. (Onions and ogres aren't Queries' friends, either.)
So, what we mean by simplifying or flattening a Query is taking out all of the unnecessary layers that sloooooooooow it down.
Great! Now, How Do I "Flatten" it?
In theory, taking out unnecessary "OR statements" and Nested Queries is easy. But we know you pour your heart and soul into your Query, and you still want it to find what you set it up to find, so it can be challenging to determine what exactly to eliminate.
To guide you through this process, here are a few approaches you could consider to flatten your Query:
Reduce your "OR" statements. Look over the number of parameter sets you have in between these statements and think about how you can decrease this number.
For example: Let's say you want a list of Contacts that aren't Households, and you have only the three Contact Types to choose from (Household, Foundation, and Organization). You could choose to create a Contact Query where Contact Type - Is - Foundation OR Contact Type - Is - Organization.
But why not take advantage of the "Is not" Operator? By using that, you can find the same set of data by simply eliminating the Household Contact Type.
Either Query will provide the same list of Contacts, but the second one is simpler and takes less time than the other. Who doesn't like that?
Look for any redundant parameters, then consolidate. Do any of the Queries you're nesting have the same necessary parameters in the main Query?
First example: Are you nesting a Contact Query within another Contact Query? In this case, you could take the parameter used in the Nested Query and put it in the main Query. Voilà! You've already eliminated one Nested Query!
Maybe you have a different Query Type nested in your main Query. In this case, consider the parameter(s) in that nested Query. Can you find them in the main Query type? If so, use them in the main Query and remove the parameter nesting the second Query.
Second example: Let's say you want to know how many Gifts are going toward the General Fund.
You could create a Project Query with the parameter, "Project Name - Is - Default Project," and then nest this Project Query in a Gift Query.
But you could also simply create a Gift Query with the exact same parameter without having to nest the Project Query.
In the second approach, the Query yields the same number of Gifts as the first, but it takes less time because there are fewer steps to get there (both for you and the Query tool).
Consider the question(s) you want your Query to answer. Sometimes, it's not a great idea to put all your eggs in one basket. For the most part, one Query is meant to answer one question, like How many donors have given to a specific Project? If you are trying to answer several questions with one Query, it's worth separating Queries to accommodate each question.
Remember: You can have as many Queries as you want in Virtuous! The number of Queries you have won't slow down the run of one Query. If you need to break one Query out into many, it's no big deal!
Familiarize yourself with all the parameters available in a Query. We know; there can be a lot in one Query type alone. But recognizing all of the possibilities available in a Query can help you create a more direct path to the information you need rather than taking a whole lot of turns to get there (if you can get there without crashing).