Tips for Improving Report Queries and Generation

The following principles can be applied to improve the performance of reports:

• Use as few tables as possible. For example, if the accounts table is not necessary, remove it from the report. 
   
• When joining tables, use inner joins instead of outer joins whenever possible. 
   
• If the join between tables is a one-to-many join, use the table with the "one" record as the first table and then join the table with the "many" records to it. 
• If the join between tables is a one-to-one join, use the table with the fewer records (based on the filtering you intend to use) as the first table and the table with more records as the second table. 
   
• Use fixed filters wherever possible. 
   
• Filter the data as early as possible in the configuration. That is, fixed filters are preferred over node filters or group filters. 
   
• If you never change a run-time filter, make it a fixed filter. 
   
• Select specific default values for run-time filters. 
   
• Include all appropriate filters to reduce the dataset to the records of interest. For example, when joining to the transaction table, include the tbl_id and trans_type as fixed filters to restrict the dataset to the specific transaction type for a certain table. 
   
• Filter on an index whenever possible. Refer to the data dictionary to determine table indexes. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products. 
   
• To count the number of records, use count(1) instead of count(*). Similarly, use count(1) when it gives the same result as counting over a specific data element or field, such as count(answers.ans_id). 
   
• Remove any output field that is not absolutely necessary. 
   
• Ensure that staff members understand the importance of limiting what they output in a report.It is best practice to configure reports to return the smallest dataset necessary in order to provide the best performance. While pagination can sometimes be used to make reports visually more efficient, it can also be a performance hit on the backend so limiting yourself to only the necessary data set is very important.

Additional Information: The items below also apply to improving report performance:

• Break large reports into smaller reports and use a dashboard to display all reports. 
   
• Change date filters that use the between operator to use two distinct filters: one filter uses date > value and the other filter uses date < value. Though the query is similar, the processing can be significantly reduced. 
   
• Avoid using OR connectors ( | ) between filters if possible. With reports, you can frequently convert an OR join into two reports and then use a container to display both reports.
• Only use Initial Search and Auto Refresh with reports that can only return a limited number of records such as less than a thousand records. 
   
• If the report is to be used primarily for text searches such as keywords or phrases, add a default text string to the run-time filter such as "Enter Search String Here". As a result, the initial search is performed on a specific entry that returns 0 entries instead of having a default configuration that returns thousands of entries.

Additional Considerations

Editing Reports: When editing a report, review the configuration to remove unnecessary tables, filters, or output columns. If you have edited a report to remove filters or output columns, verify that all of the tables are still needed to define the remaining filters or output columns.

Accounts Table: Special consideration should be given to the Accounts table. If the only information you need from the Accounts table is the name of the Assigned To staff member, you do not actually need to include a join to the Accounts table.

For example, if your report is based on incidents, use the incidents.assign_acct_id field from the Incidents table to list the staff member's name as it is configured in their staff account. You do not need to use the accounts.full_name field from the accounts table.

Evaluating Types of Joins Used: When possible, use inner joins to join tables within the report. By default, tables are joined with an inner join. While it may be necessary to use an outer join in your report, when possible use the inner join to better restrict which records can be included in the report.

For example, when joining the contacts table to the incidents table, since a contact record is required for each incident, you can use an inner join to relate the two tables. That is, for each incident there is a corresponding contact record which allows you to use the inner join.

For more information on inner and outer joins, refer to Answer ID 1839: Types of table joins used with reports.

Open Report Without Prompting for Search Parameters:  When an initial search is performed for a report, the query is run when the report is first selected.  Depending on the default settings for the fixed and run-time filters, the initial search may result in querying an entire database table such as all of your incidents or contact records which may be a significantly large number.

Therefore, only enable the initial search if you know that the number of records to be returned based on the filters is fairly low, such as fewer than 100 records or so.

To do so, edit the report, navigate to the Options in the top ribbon and enable or disable the Open Report Without Prompting for Search Parameters.

Auto Refresh Option:  Reports should only use the auto-refresh feature if there are adequate fixed or run-time filters in place so that a limited number of records are returned each time the query is run.

The auto-refresh feature is enabled or disabled from Options button on the report's ribbon, while the report is opened in edit mode, i.e. Automatically refresh report at a regular interval.

Using Fixed Filters:  Where possible, use fixed filters to restrict records to a specific value or range. For example, reports that list all of today's solved or updated incidents could use a fixed filter for the status and last updated fields. For sites with multiple interfaces, consider adding fixed filters for the interface or language so that records from only that interface are accessible.

Set Specific Defaults for Run-Time Filters: In cases where a fixed filter is not desirable, you can still set a preferred default value or range for the run-time filters. For example if your report uses the date created for an incident as a filter, set the default range to be within the last week or the last month so that the default search will return the most recent incidents that are typically of greatest interest.

Including specific default values for your run-time filters reduces the number of rows that the query needs to evaluate. You can also include default search values with text run-time filters such as "enter search text here". This forces staff members to type in what they are actually looking for before doing a search.

Train Staff to Use Filters Effectively: A report can be as streamlined as possible, but still have problems if a user performs a search with all the run-time filter values selected and no specific search text entered. That is, if the staff member sets the run-time filters to search all available options and does not search by specific search text, the query will still be evaluating potentially thousands of rows that are not necessarily of interest.

Thus, part of improving report performance is to train staff members to use search filters responsibly, and to limit the reports which staff members can access. Some staff members tend to use reports in an attempt to get a general overview of records in the database rather than returning a small number of records in order to manage them.