Optimizing Reports

Question 1

What is the best practice for reporting when you want to:

• Partition data to match its likely use
• Minimize the number of records per object

Answer 1

Reduce the number of records to query - use a value in the data to segment the query. For example, query for only a single state instead of for all states (when using divisions)

Question 2

What is the best practice when you want to reduce the number of joins for reporting?

Answer 2

Minimize the number of:

• objects queried in the report
• relationships used to generate the report

De-normalzie data when practical - “over de-normalizing” the data results in more overhead. Use summarized data stored on the parent record for the report. This practice is more efficient than having the report summarize the child records

Question 3

What is the best practice when you want to reduce the amount of data returned when using reporting?

Answer 3

Reduce the number of fields queried - only add fields to a report, list view, or SOQL query that is required

Use report filters that emphasize the use of standard or custom indexed fields. Use index fields in report filters, whenever possible.

Question 4

Provide a solution for the following Data Aggregation issue: The customer needed to aggregate monthly and yearly metrics using standard reports. The customer’s monthly and yearly details were stored in custom objects with 4Mil and 9 Mil records respectively. The reports were aggregating across millions of records across the two objects and performance was less than optimal

Answer 4

The solution was to create an aggregation custom object that summarized the monthly and yearly values into the required format for the required reports. The reports were then executed from the aggregated custom object. The summarization object was populated using batch apex.

Question 5

Provide a solution for the following issue:
- The customer created a report that used 4 related objects: Accounts (314K), Sales Orders (769K), Sales Details (2.3M) and Account Ownership (1.2M). The report had very little filtering and needed to be optimized

Answer 5

To opimize the report, the customer:

• Added additional filters to make the query more selective and ensured that as many filters as possible were indexable
• Reduced the amount of data in each object whenever possible
• Kept the Recycle Bin empty. Data in the Recycle Bin affects query performance
• Ensured that no complex sharing rules existed for the four related objects. Complex sharing rules can have a noticeable impact on performance