Do you struggle to accomplish certain scenarios in Blackbaud CRM's™ ad-hoc query tool?
Hours of “Loading” screens a regular part of your day?
Pulling hairs out over duplicate rows or misaligned records in your query results?
If you answered yes to any of the above, it’s possible that you (much like this gentleman) are bringing the wrong tool to the job, a knife to a gun fight so to speak!
Ad-hoc query is an extremely powerful tool offering business users a way to pull data from essentially any table in the database, without the need for a SQL background or skill set. Without a doubt, queries are one of the most widely used artifacts, the library of which typically grows, and grows, and grows. This is not a bad thing, and indeed what the tool is for!
However, it can be good to take a step back periodically, and remind ourselves (and our teams) that the answer to every problem is not always ad-hoc query. The SQL query that gets built on the fly, behind the scenes, is made up of multiple joins on SQL “views” that are often inefficient. You can review the generated SQL by clicking on the "fx" icon in the preview results pane of the ad-hoc query window. These inefficiencies in the generated SQL can result in long running queries that impact application performance. Hence, it is recommended that when building out datalists and/or reports from ad-hoc queries, special care should be taken to avoid embedding in high traffic pages such as tabs and sections of the constituent page or similar. At the risk of stating the obvious, the tool is best leveraged for “ad-hoc”, on-demand, and not overly complex needs.
That means you are asking for highly specific information that does not span across too many views of a given record type. Every time you add a field from different source views to be shown in your results, you increase complexity. And many standalone fields (especially revenue) are already executing numerous SQL joins behind the scenes. Throw into that mix a request for data from interactions, prospect plans, attributes, and membership for example - and you could be left with a very long running query that has the potential to impact Blackbaud CRM™ performance at large.
What to do instead?
Start by asking what is essential in your output, and limit your query to include just that. Users should be in the habit of incorporating filters that eliminate most of the unnecessary data from the results. Pay attention to the “and” or “or” clauses in the filter criteria as well. “And” clauses help in narrowing down the results whereas “Or clauses can add additional rows to the results, thus indirectly impacting the runtime performance of the query.
From there, if the particular requirement requires a high number of joins (five and up is a good benchmark), it may be appropriate to instead write a back-end SQL query. Often, something that may require ten joins in ad-hoc query can be accomplished through a single, efficient SQL statement. If the need to consume the data is regular and recurring, it may also need to be packaged as a report or datalist instead.
Where this isn’t possible and you are left with complex queries, be mindful of their execution time and usage patterns. It is recommended you avoid running revenue queries in particular when processing revenue through import or batch. This can often cause blocking where either the selection or the batch/import has to wait until the query completes before running. This in turn can cause queries, selections and/or batches to take longer to process depending on which acquires the lock first.
Remember, always test your complex queries in a non-production environment, optimize them first and then promote to your production environment.
We’re here to help
Finally, if this assessment of ad-hoc query efficiency sounds like an assessment you need, but perhaps don’t have the staff or bandwidth for, please reach out! Our experienced team can help assess and optimize your queries according to your specific needs.