Poorly written queries can cause performance issues in iMIS. Not only can poorly written queries under-perform for a user, but they can negatively affect overall system performance, both in your system and for other hosted clients.
Review the following before beginning to create your own IQA queries:
| Recommended | Not Recommended |
|---|---|
|
|
Summary tab
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.
Sources tab
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 rows 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 prompt to reduce the number of results returned any time the query is run.
Filters tab
The following best practices should be followed when working in the Filters tab:
Set the maximum run limit to 30 seconds
When in Advanced mode, 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.
Use filters with Required prompt or no prompt
Adding Required filters or filters with no prompt 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 rows returned
Limiting the number of rows returned is another way to reduce the number of possible rows returned by the query, thus improving the response time of the query. This option is found in the Filters tab, and is best paired with an appropriate Sort column, as only the top results will be returned as determined by the sort order.
Display tab
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 Subtotal option, the results may not be accurate. Do not use the Subtotal option when Only display unique results is enabled.
Avoid overuse of the Subtotal option
The Subtotal 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.
Warning! When using the Subtotal option and the Only display unique results option, the results may not be accurate. Do not enable Only display unique results when using the subtotal option.
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 only supports subqueries.
Query menu iPart configuration options best practices
The following best practices should be followed when working with iPart configuration options:
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 filter prompts (or the Require user to provide at least one valid value option) as opposed to Optional filter prompts 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.
Review the SQL query output in the Advanced view
Review the SQL query output in the Advanced view to make sure there are no cross joins in the query.
Test the query
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.
- 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: It is recommended to start building your query with two sources only.
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.
Review the queries that are shipped with iMIS
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.
