Query and report performance

Overview

The Report Manager can produce simple tabular reports, queries, and graphs that combine data from multiple records into a single presentation. If you need a multiple-record presentation that is beyond the capabilities of the Report Manager, you can use the Advanced Reporting feature. Maximo Real Estate and Facilities uses Eclipse Business Intelligence Reporting Tools (BIRT) as the enabling technology for the Advanced Reporting feature. For more information on reporting for Maximo Real Estate and Facilities, see Reporting in IBM® TRIRIGA®.

When isolated application processes seem slow, conduct a performance log analysis. If you identify slow-running queries as the primary bottleneck, the following recommendations can help with tuning.

Query tuning

Review custom queries and reports for efficient SQL and indexes. Improving the efficiency of user queries also improves the efficiency of the workflows that use them.

Many long-running queries and reports are not properly filtered. Also, users generate most of the filters in queries and reports with run-time filter operators and filter operators that are defined in the Report Manager. Long-running queries and reports can produce inefficient SQL.

In the Maximo Real Estate and Facilities Administrator Console, use the Platform Logging page to turn on debug-level SQL query information to determine the origin of a query from the Maximo Real Estate and Facilities platform.

IN versus NOT IN

Using NOT IN or != results in a full table scan. Instead, use IN or NOT EXISTS.

Wildcard character

Do not use a leading % wildcard character. % triggers a full table scan.

Index columns

Order index columns so that the number of rows that is returned is reduced earlier. For example, using a Boolean type column in the first position of the index eliminates about half the rows. Using a column such as SPEC_ID in the first position also gives a smaller result set.

Reverse association

Setting the reverse association as No provides a significant performance improvement. If the reverse association must be Yes, then understand why it is needed. For more information, see section Reverse association queries.

Association filter

Using a separate association per module or per business object is better than against all modules or all business objects.

Association filter queries

Check whether the query can be changed to a multiple business object query by using an association. This query is faster than using association filter queries.

Multiple business object (BO) queries versus direct smart section

Depending on some factors, you might need to decide on when to use a multi-BO query versus choosing the smart section fields directly. Try running queries both ways, either by using the smart section directly in the field chooser or by adding the BO using the exact association as a multi-BO query.

Nested query optimization

If a query has more than two nested levels, analyze the requirements and determine whether the levels can be reduced by adding filters.

Use system variables correctly

Make sure that system variables such as $$RECORDID$$, $$USERID$$, and $$ORGANIZATION$$ are correctly used.

Portal Section, Manager, Master Detail, and Find Queries

Select the Prompt Before Query option in the query definition for large datasets. Avoid returning all records with all statuses. Instead, use as many design-time filters as possible to avoid full table scans.

Sorting

Reduce the number of sorting elements to reduce the system resources that the query uses.

Where used

Using the Where Used tab in queries helps to determine the places it impacts before the change.

Home portal and other portals

The Home portal has sections that contain queries and reports. Ensure that any portal section queries are tuned to avoid performance issues. Tune all predefined queries for optimal performance across the system.

Many portal sections on the Home portal or any other portal can contribute to slow performance. In the portal, set the portal section to be collapsed by default whenever possible, so a limited number of sections are open on any portal. Portals that are set to auto refresh can also cause slow performance.

Fields and extended formulas

Review the following checklist to optimize the performance of fields and extended formulas:

Association queries

Check the association queries used in the formula. See whether the queries can be improved.

Regular formula

See whether it is possible to change the formula to a regular formula.

Field requirements

Verify whether the field with the extended formula is required for the functions of the business object or form. Extraneous extended formula calculations can be introduced when a field is copied from a business object in which the extended formula calculation is used into a place where the result is not needed. For example, straight-line calculations are required in a lease abstract but may not be required for the triRentStraightLineNU field

Associated data

Consider using the query on an extended formula to get the associated data instead of using the field type to get the data that is being associated. Using a query instead of a field improves performance.

Sorting

Avoid sorting on the query when used for an extended formula. For more information, see Query Sort Order.

Where used

Using the Where Used information on fields helps to determine whether this field is used on a form.

Reverse association queries

For performance reasons, the Reverse Association flag is deprecated.

The ALLOW_REVERSE_ASSOCIATION property is added to TRIRIGAWEB.properties. The default for this setting is TRUE, which allows reverse associations to be run in queries and reports as defined in Report Manager. If you want to disable reverse associations from being run for testing purposes, set ALLOW_REVERSE_ASSOCIATION to FALSE so that no queries are run with reverse associations. Perform this action on a test environment first to verify the integrity of data coming back from queries. Usually, the flag is not necessary, and in cases that it is, some minor metadata changes can be made to render it unnecessary.

For any queries or reports that are defined in the Report Manager with the Association Filter set with the Reverse Association flag, the ALLOW_REVERSE_ASSOCIATION=FALSE setting ignores this flag and interprets and runs queries as if this flag was not set, so only forward associations are returned.

The query that is generated to return both the forward and reverse association can lead to poorly performing queries that can consume significant CPU, which can slow down other queries and operations on the database. Usually, it adds unnecessary overhead without affecting the query results. The platform does not allow new queries to be created with the Reverse Association flag, nor does it allow queries to be copied with the flag.

If you want to run your system with this property set to FALSE to evaluate the performance implications, do so in a development or test environment first. You might encounter a query on a manager, query section, or workflow that no longer has the results you were expecting. If this event happens, first examine the Association tab of the records in question and note the correct forward association string. Then open the query in Report Builder, and in the Association Filter examine the association string that is used. If the two strings are different, update the report to use the correct association string. If your records have only a one-way association, create the forward and reverse association in Association Manager and then update your data by using a workflow to set the forward and reverse association.

Query sort order

Case-insensitive sorting on query results cause reduced performance when processing a large volume of data. You can turn off the forced case-insensitive Order By on query results by using the REPORT_CASE_SENSITIVE=NATIVE_DB_CASE_SORT property and setting in the TRIRIGAWEB.properties file.

Restart the application server for changes in the TRIRIGAWEB.properties file to take effect. Turning off the case-insensitive Order By might result in improved database performance. But the sort results might be ordered differently.

The following examples show how DB2® case-insensitive and case-sensitive sorting can give different results:

• REPORT_CASE_SENSITIVE=FORCE_CASE_INSENSITIVE: Case-insensitive sorting. Note the UPPER(T1.NameTX).

  SELECT T1.NameTX AS T1_1002, T1.ControlNumber AS T1_1001, T1.SYS_TYPE1 AS 
  T1_SYS_TYPE1, T1.SYS_GUIID AS T1_SYS_GUIID, T1.SPEC_ID AS T1_SPEC_ID FROM 
  T_TEST T1 WHERE T1.SYS_PROJECTID = 1 AND T1.SYS_OBJECTID > 0 ORDER BY 
  UPPER(T1.NameTX)

• REPORT_CASE_SENSITIVE=NATIVE_DB_CASE_SORT: Native database case-sensitive sorting.

  SELECT T1.NameTX AS T1_1002, T1.ControlNumber AS T1_1001, T1.SYS_TYPE1 AS 
  T1_SYS_TYPE1, T1.SYS_GUIID AS T1_SYS_GUIID, T1.SPEC_ID AS T1_SPEC_ID FROM 
  T_TEST T1 WHERE T1.SYS_PROJECTID = 1 AND T1.SYS_OBJECTID > 0 ORDER BY 
  T1.NameTX

The following are examples where SQL Server case-insensitive and case-sensitive sorting give the same result:

• REPORT_CASE_SENSITIVE=FORCE_CASE_INSENSITIVE: Case-insensitive sorting. Note the UPPER(T1.NameTX).

  SELECT T1.NameTX AS T1_1002, T1.ControlNumber AS T1_1001, T1.SYS_TYPE1 AS 
  T1_SYS_TYPE1, T1.SYS_GUIID AS T1_SYS_GUIID, T1.SPEC_ID AS T1_SPEC_ID FROM 
  T_TEST T1 WHERE T1.SYS_PROJECTID = 1 AND T1.SYS_OBJECTID > 0 ORDER BY 
  UPPER(T1.NameTX)

• REPORT_CASE_SENSITIVE=NATIVE_DB_CASE_SORT: Native database case-sensitive sorting.

  SELECT T1.NameTX AS T1_1002, T1.ControlNumber AS T1_1001, T1.SYS_TYPE1 AS 
  T1_SYS_TYPE1, T1.SYS_GUIID AS T1_SYS_GUIID, T1.SPEC_ID AS T1_SPEC_ID FROM 
  T_TEST T1 WHERE T1.SYS_PROJECTID = 1 AND T1.SYS_OBJECTID > 0 ORDER BY 
  T1.NameTX

Using report run history to track query performance

Track the performance and monitor who runs queries and reports in the Report Manager. Set a flag on the report definition that tracks who runs the report and the time it took to retrieve the information from the database.

To enable the report run history, open the report in the Report Manager, click the General tab, and select Track History.

The data can answer the following questions:

• Which reports run the longest overall?

  SELECT MAX(duration), rth.rep_name
  FROM REGRESSION35X.rep_history rh, REGRESSION35X.REP_TEMPLATE_HDR rth
  WHERE rth.REP_TEMPLATE_ID = rh.REP_TEMPLATE_ID
  GROUP BY rth.rep_name
  ORDER BY MAX(duration) DESC;

• Of the reports that run, which have the average longest run times?

  SELECT AVG(duration), rth.rep_name
  FROM REGRESSION35X.rep_history rh, REGRESSION35X.REP_TEMPLATE_HDR rth
  WHERE rth.REP_TEMPLATE_ID = rh.REP_TEMPLATE_ID
  GROUP BY rth.rep_name
  ORDER BY AVG(duration) DESC;

• For the users in the system, who runs the most reports?

  SELECT COUNT(*), uc.user_account
  FROM REGRESSION35X.rep_history rh, REGRESSION35X.user_credentials uc
  WHERE rh.user_id = uc.user_id
  GROUP BY uc.user_account
  ORDER BY COUNT(*) DESC;

• Of the users in the system, who ran the longest reports?

  SELECT MAX(duration), uc.user_account
  FROM REGRESSION35X.rep_history rh, REGRESSION35X.user_credentials uc
  WHERE rh.user_id = uc.user_id
  GROUP BY uc.user_account
  ORDER BY MAX(duration) DESC;

Advanced BIRT reporting

Follow the same principles for reporting when designing and building BIRT reports. Use the following optional features of the Maximo Real Estate and Facilities platform to improve the performance of the front-end application server.

BIRT Report offloading

Specify BIRT reports to be offloaded and processed by a separate Maximo Real Estate and Facilities server that is configured as the BIRT process server. If you do not configure a separate BIRT process server, the report processing is done locally.

The following system properties enable BIRT report offloading. Report offloading can eliminate system resource usage by BIRT reports on a server with user sessions by sending the report processing to another JVM. The offloaded server is simply another Maximo Real Estate and Facilities server that can be dedicated to this task or be the process server that is already set up for backend agents.

• BIRT_PROCESS_SERVER_HOST_NAME
• BIRT_PROCESS_SERVER_PORT
• BIRT_PROCESS_SERVER_LISTENING_PORT

For more information, see Configuring BIRT Process Servers

Asynchronous queued reports

To run a BIRT report asynchronously and notify the user when the report is ready to view, mark the report as queued. For reports that take more than one minute to run, consider running them asynchronously instead of synchronously. Running a report asynchronously runs the report on the server when the server is not busy with other tasks. If a queued report has runtime filters or BIRT filters, they are presented to the user before the report is queued, as they are for a nonqueued report.

You can start and access queued BIRT reports in My Reports, Report Manager, manager query lists, and query sections. You cannot start and access queued BIRT reports in portal sections and form actions. But if a queued report is attached to a portal section, the system starts it in a nonqueued manner.

When a queued report finishes processing, the system sends a notification to the user’s Notifications portal. To access the report, the user clicks the hyperlinked subject line. A queued BIRT report that is attached to a workflow notification runs with the notification. However, if the report contains filters that do not have a default value, the report generation might end in error because there is no user to provide the values. You need to set the default values for all filters when you define such a report.

Maximum available server memory

Change the maximum percentage of available server memory that can be used to create the BIRT report query results. Use the BIRT_MEMORY_USAGE_LIMIT system property. For information on using this property, see TRIRIGAWEB.properties in System properties.

Geography and organization security check

You can choose an alternative way to run a Geography and Organization security check on a query. The generated SQL is less resource-intensive and might perform more efficiently depending on your Geo-Org hierarchy structure. To use this alternative method, you must comply with the following limitations:

• The triPathSY field must be part of the Organization and of all Geography business objects.
• The values of triPathSY fields on Organizations and all Geography records must be consistent with the hierarchy.
• The Organization and Geography that is used within any security group must contain fewer characters than the size of triPathSY field on the Organization and Geography business objects.

IBS_SPEC_STRUCTURE

You can determine whether your Geography and Organization hierarchies are in sync with their triPathSY values by using the Database Manager in the Administrator Console. In the Database Manager, clickDatabase Admin Tasks. Then select Check Organization Hierarchy or Check Geography Hierarchy. Read the message and click OK to confirm. This check process might take a long time to run depending on the size of your Geo and Org hierarchy. You can monitor the server.log file for feedback regarding any out-of-sync entries and process completion.