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:
Do not use SQL expressions in the query
It is recommended to not use SQL expressions or functions in the query itself.
Make use of the Use cached results option on the Summary tab
The Use cached results option caches summary query results so that future requests for the query data can be served faster. This option is meant to be used for summary queries, for example, queries that display on the Staff site Dashboards.
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 as this can affect performance.
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 PartyMembershipGroupData
The PartyMembershipGroupData business object can be used in place of the following business objects:
- GroupData
- GroupMemberData
- GroupMemberDetailData
- PartyData
Use PartyWithDefaultAddressData
Use the PartyWithDefaultAddressData business object instead of PartyData.
Start building your query 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.
Relate your 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.
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)
Leaving your sources unrelated creates a cross join which can lead to poor performance. In most cases a cross join results in undesired behavior and should be avoided.
In the rare case where you must use a cross join, filter the results as much as possible to avoid getting more results than expected.
Do one or both of the following to mitigate the potential performance impact of an unrelated source or 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 with unrelated sources.
- Use required filters or filters with no optional prompt to reduce the number of results returned any time the query is run.
Minimize the number of sources in a single query
There is no prescribed maximum, but every additional source in a query affects performance.
In general, the more complex the query, the greater the cost to performance.
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
- 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.
Review the queries that are shipped out-of-the-box 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.
0 Comments