IQA Queries: Best Practices

The following best practices should be followed when working in the Summary tab:

Do not push slashes (/) in query names

Adding slashes to IQA query names cause errors when importing. To avoid these errors, update all existing IQA queries with slashes in their name and update any content items or reports associated with the queries.

Make use of the Use cached results option

The Use cached results option caches query results so that future requests for the query data can be served faster. This option is meant to be used for queries that do not require real-time results, for example, queries that display on the Staff site Dashboards. When this option is enabled, the query results may be cached for up to 6 hours.

Review the SQL query output

Review the SQL query output to make sure there are no cross joins in the query.

The following best practices should be followed when working in the Sources tab:

Minimize the number of sources in a single query

Every additional source in a query affects performance. In general, the more complex the query, the greater the cost to performance.

Be mindful and careful when deciding which sources to use

Do not use too many sources. It is recommended to not use greater than four sources for a single query.

Be aware that using a query as a source does not join the same as two business objects

Using an IQA query as a source in another query does not join the same way that two business objects join together. Adding a query as a source does not create a sub query, but rather simply copies all sources and joins from the added query.

The Finding contacts who have not registered for an event tutorial details one example of how to use a query as a source in another query.

Start building queries with limited sources

Start building your query with two sources only. Remember to test as you build your query.

If you are happy with results and performance then add more sources, one source at a time. Continue to test and ensure the results and performance are still acceptable. If you find that results or performance are affected then you will be able to more accurately locate the source or relation that is causing the issues.

Are all the sources necessary? Sources are added as queries are created initially, but users might find some anticipated relations and properties are not required. Review your sources to determine if you still need all sources in your query.

Use CsContactBasic and NetContactData

For performance reasons, it is recommended to use the NetContactDatabusiness object as a source whenever possible instead of CsContact and NetContact, because NetContactData is optimized for efficient querying.

NetContactBasic is NOT recommended.

Relate sources on identifier columns whenever possible

Sources should always be related on an identifier column where possible. For example, when joining to a contact source join on the contact’s ID rather than attempting to join on the contact's name.

Select properties whose values are likely to be unique or match a set of consistent values across all records. Often, these will be properties determined from either system values or a known list of values and will have property names that are or end in one of the following:

• Id (PartyId, ItemId, InvoiceId, ActivityId, and so forth)
• Key (ContactKey, MajorKey, UserKey, BillingLogKey, GroupKey, and so forth)
• Code (CountryCode, DiscountCode, EventCode, OrderTypeCode, and so forth)

Generally, do not select properties whose values are:

• Determined by direct user input and not specifically determined from either system values or a list of known values. Such properties might be better suited for use as filters.
• Calculated fields; instead, join on primary keys when possible.

Avoid properties with names that are or end in the following:

• Name (CompanyName, FullName, FirstName, LastName, ProductName, UserName, GroupName, and so forth)
• Description (EventDescription, FunctionDescription, GroupDescription, and so forth)
• Desc (InvoiceDesc, OrderStateDesc, ProductStatusDesc, and so forth)
• Text (MemorialNameText, SalutationText, AddresseeText, DisplayText, and so forth)

Avoid Cross Joins when relating sources

Cross Join is an available option when defining a relation between two sources. A cross join returns rows for every unique combination of the data in each source (a Cartesian product). For example, if you have a cross join between two sources, each with 1,000 records, your query will return one million rows. This option can affect performance adversely and should only be used when absolutely needed.

Do one or both of the following to mitigate the potential performance impact of a cross join:

• Use the Limit the number of results returned option on the Filters tab combined with an appropriate sort column to help alleviate the performance issues.
• Use required filters or filters with no optional 'InSearch?' settings to reduce the number of results returned any time the query is run.

The following best practices should be followed when working in the Filters tab:

Set the maximum run limit to 30 seconds

The query Filters tab gives you the option to set the maximum amount of time a query will attempt to generate before the query times out. It is recommended that this setting is kept at 30 seconds.

Choosing 1 minute or 3 minutes is not recommended and could severely decrease the site’s performance.

Avoid using url params with date properties

When creating filters using date properties, such as Join Date or Date Added, do not use URL params. Instead, use Dynamic values.

Use filters with the "In Search?" setting set to Required or no

Adding filters with the In Search? setting set to Required or No can reduce the number of possible rows returned by the query, thus improving the response time of the query.

As an alternative to making each filter Required, the option to Require user to provide at least one valid value can be used.

Avoid Contains comparison in filters if possible

A filter using Contains can take additional processing time as it does not make use of indexes. The Starts With or Equals comparison types are preferred alternatives. Making use of indexes will perform much more efficiently.

Limit the number of results returned

To improve query response time, limit the number of results returned. Set this limit using the Limit the number of results returned option in the Filters tab. For best results, pair it with a suitable Sort column, so only the top results (as per the sort order) are returned.

Important! For complex queries, performance issues such as timeouts can occur even when retrieving a smaller number of result rows. These issues are often influenced by the complexity of the business objects rather than just the number of rows returned. If you experience performance problems, consider increasing (e.g., from 500 to 500,000) or removing the value in the Limit on the number of results returned field.

The following best practices should be followed when working in the Display tab:

Avoid the “Only display unique results” option

This option is used to ensure the query does not return duplicate rows with the same data, but it can cause the query to take longer to process. It should only be enabled when there are no other means to avoid duplicate rows, such as using source relations (joins) or filters.

Warning! When using the Only display unique results option and the Group By option, the results may not be accurate. Do not use the Group By option when Only display unique results is enabled.

Avoid overuse of the Group By option

The Group By option in a Display column is useful for creating expandable categories in your query results. However, the option can potentially slow down the query response time, so use it judiciously. When using this option, make sure to run the query and ensure that the response time is adequate.

Avoid displaying large text columns

Display columns containing large amounts of data can cause poor performance. The Length option for Display columns can be used to limit the number of characters displayed, but it is preferred to not even include columns that could potentially contain large amounts of text.

Do not use SQL expressions in the query

It is recommended to not use SQL expressions or functions in the query itself. As a security measure, only system administrators have access to the SQL Expression textbox on the query Display tab. If you must use a SQL expression, keep in mind you cannot save a query with an invalid SQL expression. You must correct any errors you receive about the SQL expression before saving.

Note: The SQL Expression textbox does not support subqueries.

The following best practices should be followed when working in the Sorting tab:

Sort by last name in ascending order

If a query contains a first and last name, it is best to sort by last name in ascending order.

Sort by newest data for dates

If a query contains a date, it is best to sort the dates with the newest data displayed first.

To enhance privacy and security, it's crucial to safeguard queries containing sensitive details, including names, email addresses, home addresses, and phone numbers. By default, access to new queries is restricted to staff members only.

For queries requiring API access, such as those integrated with third-party services like TopClass or OpenWater, ensure the Available via the REST API option is enabled. This setting should remain disabled for queries not utilized through the API.

It is important that Available via the REST API is only enabled for queries that meet at least one of the following criteria:

• The query does not contain any personally identifiable information, or
• The query is properly filtered to only show information the requesting party is asking for, or
• The query is secured so that only staff users have access to it

Tip! It is always a recommended best practice to test as you build a new query.

• Test your query as you add sources and relations. If you are building a query with multiple sources, it is advised that you test (Run tab) as you add sources.

Note: It is recommended to start building your query with two sources only.

• Test with sample filter values to determine how long the query takes. If results take more than a few seconds, reconsider adding that query to a control on a page. Every time the page loads, that wait time on the query becomes a cost.
• As you test, you can rebuild your query as performance dictates. It might be necessary to rebuild poorly performing queries.
• If you do not see any data, or the data you see is not useful, modify the returned display. Click the Display tab to change the results, then run the query again.

Note: Do not use queries that take greater than five seconds to return results. Queries that run for greater than five seconds are indicative of a poorly written query that could affect performance.

iMIS ships a number of queries you can use as examples to emulate or as starting points from which you can create your own queries. For example, select a query at $/Samples/Dashboards. You can open the query to review how it was created, how relations were defined, and how filters were applied.

Consider reviewing the Samples query folder ($/Samples) at RiSE > Intelligent Query Architect. You can also review existing queries for specific modules in iMIS.

You can build your own query to match, or you can Save As and edit a copy of the out-of-the-box query.

You can also use Easy Edit to review queries on other pages in iMIS. Simply enable Easy Edit, then review the content to locate the Source query on the content item.

Use “Show initial results for queries with optional filters” only for fast-running queries

This option can slow the page load time if the source query takes a long time to return results.

Use “Reload list from database” only when needed

This option ensures that fresh data is retrieved each time the page is refreshed, so it should only be enabled if up-to-date data is important for the content’s purpose. If Use cache results is enabled in your query, this option does not apply and so should not be enabled.

Use a lower number of results per page to improve page load time

The Results to display per page option can affect the time required to load the page; a value of 25 or less is recommended.

Use Required prompts in queries that are displayed using the Query Menu content item

Using Required filters (or the Require user to provide at least one valid value option) as opposed to Optional filters ensures that the user cannot request all results, but must filter the request to a subset of results. This can improve the response time of the query because fewer rows will be returned.