Make Your Report Run Faster

Data Access Studio uses many automatic performance techniques to make your report run fast. You can do the following additional techniques to make your report go faster as well:

Symptom Do This
Report takes a long time to Select or Count Use the Quick Form Assistant
Reports take a long time to load large number (>100,000) of rows 1. Add more filtering. Try to reduce the number of rows by adding more filters.

2. In a single table report, hide any unused columns. The fewer columns you have, the faster a larger data set load will go.

3. A DASVIEW is automatically created for table joins, but one can be created on singular table reports by selecting Design > Table Joins. If you are using a DASVIEW, remove columns from the table join selector that are not used. The fewer columns in a report, the faster a larger dataset load will go.
Calculating takes a long time with Table Lookup calculations Consider using a Table Join instead of a Table Lookup.
Report loads > 100,000 records over a WAN connection If you are loading a large number of records over a slower network connection, consider scheduling the report to run on the DAS Web Server. Once the report runs there, the scheduler can email you the result. You must have the Automator license for this option.
An interactive report that summarizes a large amount of data but does not require the detail data If allowed, leverage the Database and/or Server Summarization feature.
Note

To easily review performance metrics on your report, utilize Design Notes.

Quick Form Assistant

The Quick Form Assistant lets you create better running queries on large tables. The Quick Form Assistant option box shows a list of all valid indices for the currently active object. You should show all columns before using the Quick Form Assistant. Upon selecting an index from this list, Data Access Studio creates report parameters based on the index you choose (existing parameters will be left untouched at the top).

Once you apply the index:

  • To take advantage of an index, select an index that most closely matches the query you require.

  • Notice the index columns appear as report parameters.

  • Fill in index values from top most to bottom most. Remember, the database will not use an index if you do not fill in values from the beginning of the index. For instance, if your index is "Doc Type", "Document Number", and "Doc Co", the index will not be used if you only specify values for only "Document Number" and "Doc Co". All left-most index columns must be specified first for the index to take effect.

  • Depending on the database, even if you specify all fields of an index, the database will not always use that index. In this event, the database decides that another execution path will provide better performance. If you notice that a fully specified index does not result in a fast query, please notify your JD Edwards (EnterpriseOne/World) database administrator. Provide your Database Administrator (DBA) with the index columns you used and the time the query took to run.

  • To undo the Quick Form Assistant, simply select the blank Index at the top of the combo box. DAS will remove any Quick Form indexes from your report.

Server and Database Summarization

Many reports display summaries of large amounts of data. For example, a report may show the total number of orders by customer or the average order amount by month. By default, DAS will download all the data to the client and then summarize it. This can take a long time and use a lot of bandwidth. But what if the recipients of this type of report only need the summary information? In this case, it is much faster to summarize the data on the server or database and then download only the summary information.

DAS provides the capability for different types of server-side summarization (via group-by SQL operations and/or DAS server summarization):

  • Summarize on Server: This option summarizes the data retrieved from the database on the DAS server before sending it to the client. This may provide improved performance for allowed reports (see report restrictions below), but it may not be as fast as summarizing on the database.
  • Summarize on Database: This option summarizes the data on the database server before sending it to the DAS server. This will usually provide the best performance, but it is only available for certain types of reports.
Note

The option to summarize on database or server is only shown on the group column right-click menu. Since only summarized records are included on the report, reports that need to display detail information cannot use this feature.

Key User Security Permissions

The following permissions are required to see the Summarize on Server and Summarize on Database options in the right-click menu of a grouped column:

  1. User must have the Can do grouping on server permission to see any Summarize on Server options
  2. User must have the Can enable sort and grouping at database permission to see the On Database option

Important

The reason these permissions are required is that summarization will impact CPU and memory usage on the database and/or DAS server. Sending all detail data to the client and allowing it to summarize is much less resource-intensive for the shared server resources. Therefore, the DAS administrator may choose to limit these permissions to only certain users or groups of users. If you do not see the options in the right-click menu, please contact your DAS administrator for assistance.

Summarize on Server

Server-side summarization is different from database summarization in that this mode will query the database with SQL that does not include groupings or aggregation. The DAS server will then summarize the data before sending it to the client. This option is available for more report configurations, but it may not be as fast as summarizing on the database. After selecting this option and re-running the report, you should see fewer detail records under each group in the report. The report should also run much faster and use less bandwidth and memory.

This option will consume CPU and memory resources on the DAS server but will not consume additional resources on the database server.

Summarize On Server Requirements

Even with a grouped report and the appropriate permissions, the server summarization option is not allowed on reports with any of the following conditions:

  1. Report uses Count or Average summaries on any column (hidden or visible).
  2. Report uses any of these calculations (hidden or visible):
    1. De-duplication
    2. Parent/Child
    3. Random Row Index
    4. Conditional that uses the multiplication option
    5. Math and Statistical Functions: Absolute Value, Average, Ceiling, Floor, Ln, Log, Median, Multiplication, Power, Sign, Square, Square Root, Remainder, Truncate, Uniform Buckets
Note

A report that has server or database summarization enabled will no longer summarize on the server or database if one of these calculations is added. Therefore, it is important to carefully design and maintain the report for summarization from the beginning.

Summarize on Database

This option will summarize the data on the database server using standard SQL Group By operations and Aggregation functions. If the report structure fits the requirements, this option will usually provide the best performance. After selecting this option and re-running the report, you should see far fewer detail records under each group in the report. The report should also run much faster and use less bandwidth and memory.

This option will consume CPU and memory resources on the database server but will not consume additional resources on the DAS server.

Summarize On Database Requirements

The database summarization option is not allowed on reports with any of the following conditions:

  1. All restrictions listed in the Summarize on Server section above
  2. Report uses First summary on any data column (hidden or visible)
  3. Report is based on a union
  4. Report has any non-numeric data columns with summaries (hidden or visible) (E1 limitation only. These columns are supported in World and with External Data)

Server and Database Summarization FAQ

  1. Why is there more than one record under each group after summarization? Shouldn't each group contain just a summary record?
    • The additional records are the result of additional hidden groupings added to the summarization request. These additional groupings are from inputs into calculations or other report features. These additional groupings result in additional records under the visible main grouping of the report.
    • If there are a large number of dependencies in the report resulting in the detail rows not being much smaller than when Summarize on Server is off, this feature may not be a useful technique. Instead, consider limiting dependencies before changing the summarization behavior.
  2. Why are the summarization options still disabled after removing unsupported calculations?
    • Verify that only the needed columns have summaries. Even if a column is not visible, if it has a summary, it will be considered in the summarization process. If the column is not needed, remove the summary. Note that some calculations might auto-add summaries to the report in possibly hidden columns.
  3. Do subscribers need to have the same permissions as a designer to run a report with server or database summarization?
    • No. A report leveraging server or database summarization will still execute on the server or database even if the subscriber does not have the permissions.
  4. Why are my report results different when I summarize on server than when I'm not summarizing on server?
    • If you are performing computations on a summarized value whereas before the computation was done on a detail value (which is no longer returned due to summarization behavior), the results of the report may differ. Be sure to cross check any report results when switching to summarize on server.
  5. Why aren't Math or Statistical Functions allowed to be used for reports with summarize on server?
    • Some math and statistical functions - like averages - require access to all the underlying data to calculate accurate results. When Summarize on Server is enabled, the server may group and total data early, before these functions are applied. This changes the order of operations and can lead to unexpected results.