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:
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.
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.
The following best practices should be followed when working in the Sources tab:
Every additional source in a query affects performance. In general, the more complex the query, the greater the cost to performance.
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 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.
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.
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)
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.
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.
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.
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.
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.
The following best practices should be followed when working in the Display tab:
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.
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.
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.
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.
The following best practices should be followed when working with iPart configuration options:
This option can slow the page load time if the source query takes a long time to return results.
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.
The Results to display per page option can affect the time required to load the page; a value of 25 or less is recommended.
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 to make sure there are no cross joins in 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.
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.