Edit

Share via

Troubleshooting performance issues in ER configurations

This article explains how to find and solve performance issues in Electronic reporting (ER) configurations.

Typically, performance investigation consists of several steps.

  1. Collect data.
  2. Analyze the collected data.
  3. Based on the results of the analysis, use ER configurations to make changes, or decide to collect more data.

Troubleshooting

Analyze execution time

Execution time can depend on unpredictable factors, such as other tasks that run in the same environment and caching that uses data when it's called for the first time. Therefore, you should repeat the execution and measurement several times.

Sometimes, performance issues aren't caused by an ER format configuration that you use for reporting. Instead, X++ code that you use to open that ER format configuration causes the issues.

  1. In either the Action center or the event log, look at the execution time of the report.

  2. Compare the execution time of the report with the total execution time in the scenario.

  3. If the execution time of the report is much less than the total execution time, consider the amount of data that the report processes:

    • If the report processes a small amount of data, the issue might involve the loading time of the configuration.
    • If the report processes a large amount of data, the issue might involve preprocessing X++. Use Trace parser to analyze this case.

    For other cases, see the next sections.

  4. Run multiple tests that involve different amounts of data to determine how the execution time depends on the amount of data.

Analyze Trace parser traces

Prepare a small example, or collect several traces during random parts of the report generation.

Then, in Trace parser, perform a standard bottom-up analysis and answer the following questions:

  • What methods consume the most time?
  • What part of the overall time do those methods use?

Answer the same questions for queries.

If you see that methods are prefixed with "ER," move on to the next section.

If you see that methods or queries originated in the application suite, consider generic optimizations (for example, create indexes).

Analyze the number of calls. If the number is higher than expected, consider caching the corresponding nodes of the configuration.

Analyze database calls

Prepare an example that has a small amount of data, so that you can collect an ER trace.

Then open the trace in the ER model mapping designer, and look at the bottom of the page. Answer the following questions:

  • Is there any query duplication? If there is, consider one of the following fixes:

  • Does the number of queries and fetched records correspond to the overall amount of data? For example, if a document has 10 lines, do the statistics show that the report extracts 10 lines or 1,000 lines? If you have a substantial number of fetched records, consider one of the following fixes:

Analyze PerfView traces

PerfView is a tool for experienced developers. For more detailed information about the following steps, see Wall Clock Time Investigation Basics.

  1. Collect a trace by using thread time.

  2. Include only stacks that use runUnattended, to filter only the thread that has configuration execution. (Add runUnattended to the IncPats input box.)

  3. Fold all CPU, network, and blocked time.

  4. Load ER presets for PerfView.

  5. Select ER > Other preset.

  6. Look at the names:

    • You probably see the platform code that consumes the most time.

    • You can double-tap (or double-click) and go up through callees.

      If you find classes that have the prefix "ERExpression," and if they're functions related to formulas, you can guess the function name based on the class name, and you can look at the ER repo to view the attributes.

Fixes

  • If queries consume most of the CPU time, try to reduce the number of queries:

    • Review the ER trace for duplicated queries.
    • Check how many records are fetched, and evaluate how much data should theoretically be fetched.

  • If functions consume most of the CPU time, try to find the place in the configuration that consumes the most resources.

  • If data collection functions consume most of the CPU time, consider replacing them with SQL group by on the model mapping side.

Collecting data

Depending on your environment, you can collect available data in several ways:

  • Get the total running time:

    • From the Action center
    • From the event log

  • Profile the execution:

    • By using ER tools
    • By using Trace parser
    • By using PerfView

Collecting data in a production environment

Sometimes, you can reproduce issues only in a production environment. Collect data in the following ways:

Collecting data in a development environment

In addition to the tools that you can use in a production environment, use several tools in a development environment:

  • Event log (Microsoft-Dynamics-ElectronicReporting). This log can give you the total execution time.
  • Common .NET tools, such as PerfView.

Additionally, a development environment gives you more flexibility to experiment. For example, you can turn off parts of reports to see how the execution time is affected.

Tools

Execution time in the Action center

ER can show the execution time of the configuration in the Action center. This option works only for a specific user and a specific company, and only for interactive sessions. To make this feature available, follow these steps.

  1. Go to Organization administration > Electronic reporting > Configurations.
  2. On the Configurations page, on the Action Pane, on the Configurations tab, in the Advanced settings group, select User parameters.
  3. In the User parameters dialog box, set the Show file generation time option to Yes.

Execution time in the event log

  1. Open Windows Event Viewer.
  2. Under Applications and Services logs, open Microsoft-Dynamics-ElectronicReporting/Operational.
  3. Look for FormatMapingRun events where EventID=2, because these events contain the information about elapsed time.

Trace parser traces

Because ER is implemented in X++, you can use common X++ tools to analyze performance. For more information, see Take traces by using Trace parser.

There are a few limitations to this approach. Because part of ER is implemented in C#, not all the details are available. However, you can view the data usage details. Additionally, long report runs can exceed trace storage limitations.

ER traces

ER can collect its own traces, and it has visualization and analysis tools for those traces. For more information, see Trace the execution of ER formats to troubleshoot performance issues.

PerfView

Because both X++ and ER are implemented on top of the .NET platform, you can use common .NET tools. For example, you can use the free PerfView tool.

You can also collect data from the command line. For example, the following Windows PowerShell script collects the execution time until any format execution is stopped on the machine.

c:\programs\PerfView collect "e:\traces\$(date -format "ddMMyyyy_hhmm").etl" `
    -CircularMB:20000 -ThreadTime `
    -NoNGenRundown `
    -StopOnEtwEvent:Microsoft-Dynamics-ElectronicReporting/FormatMappingRun/Stop

There are a few limitations to this approach. You must have administrative access to the machine. Additionally, you must be an experienced developer, because there's a steep learning curve.

Making changes

Use caching

Although caching reduces the amount of time that it takes to fetch data again, it costs memory. Use caching in cases where the amount of fetched data isn't large. For more information and an example that shows how to use caching, see Improve the model mapping based on information from the execution trace.

Reduce volume of data fetched

You can reduce memory consumption for caching by limiting the number of fields in the records of an application table that you fetch at runtime. In this case, you fetch only those field values of an application table that you need in your ER model mapping. You don't fetch other fields in that table. Therefore, the volume of memory that is required to cache fetched records is reduced. For more information, see Improve performance of ER solutions by reducing the number of table fields that are fetched at runtime.

Use a cached, parameterized calculated field

Sometimes, you need to look up values repeatedly. Examples include account names and account numbers. To help save time, you can create a calculated field that has parameters on the top level, and then add the field to the cache.

Use this approach only when the size of the cached data is small. For more information, see Improve the performance of ER solutions by adding parameterized CALCULATED FIELD data sources.

Use a JOIN data source

A JOIN data source enables one query to fetch several connected records. You don't need a separate query to fetch each connected record. For example, if you have 1,000 lines, and you fetch item data for each line by relation, you have 1,001 queries (= 1,000 + 1). If you use a JOIN data source, you use only one query to fetch the same data. For more information, see Use JOIN data sources in ER model mappings to get data from multiple application tables.

Use the FILTER function instead of the WHERE function

The FILTER function runs conditions on SQL Server, whereas the WHERE function fetches all data from the list, one record at a time, and applies the condition for each record. For example, you want to select one record out of 1,000 records. If you use WHERE, you fetch all 1,000 records. However, if you use FILTER, you fetch exactly one record. FILTER can also use indexes on the database side.

Use collected data functions or an accumulated data source

If your configuration has a group by component that summarizes previously fetched data by report, the component fetches all the data again. By using collected data functions, you enable ER to accumulate data during the first fetch. For more information, see ER Configure format to do counting and summing.

Rewrite parts of the configuration in X++

ER supports calling methods of tables and classes, including extensions. Consider rewriting parts of the model mapping in X++ to help improve performance.

ER can consume data from the following sources:

  • Classes (object and class data sources)
  • Tables (table and table records data sources)

The ER application programming interface (API) also provides a way to send precalculated data from the calling code. The application suite contains numerous examples of this approach.

  • Last updated on