x

Some options for managing large volumes of data in applications include:

• HOLD files

  HOLD files can improve response times and reduce connections to the back-end databases. A HOLD file is a data extract on which you can build calculations, defined fields, joined tables, and other functions. HOLD files can be used by multiple applications.

• Drop-down list boxes

  Drop-down list boxes can contain only the values needed in the list box or drop down. For instance, if there are more than 50 values listed, you may want to consider one or more subcategory list boxes. Too many values listed in the list box may add to the time it takes to load the HTML launch page. Fewer values allow users to easily find the values they need.

• Dynamic versus static data for launch pages

  Static data allows for faster loading of HTML launch pages because the data is loaded from a data extract that was previously created, possibly using ReportCaster or an ETL job. Depending on the frequency of data updates, the data extracts may have to be refreshed on a regularly scheduled basis.

• Dynamic data may be a better choice if using highly efficient data retrieval against an optimized data mart, materialized query table (MQT), DBMS view, or cubes (dimensional data).
• Aggregated data versus detailed data

  Summary reports can be built against aggregated data and also provide users with drill-down capabilities to detailed data. Data extracts for detailed data can be created using ReportCaster jobs scheduled during off peak hours.

x

• Limit the number of rows that the DBMS returns to WebFOCUS. The less data that the WebFOCUS query requests, the faster the report runs.
• Specify a selection criteria with the WHERE clause that limits the number of rows that the DBMS returns to WebFOCUS.
• Specify READLIMIT in the request to retrieve only the first (n) rows of a table. Depending on the DBMS, READLIMIT is translated to the appropriate SQL syntax. The syntax is passed to the DBMS to limit the number of rows being transmitted across the network, significantly improving performance against larger tables.
• Select only the DBMS columns that are required for the report since unnecessary columns will slow down the data retrieval.

x

• Minimize the number of requests per page.
  • Reports and Graphs
  • Controls
  • Drop downs
• Use conditional chaining with drop downs, where applicable.
• Database design is consistent with report requirements.
• Database optimized for quick retrieval.
• Excessive use of common code (-INCLUDE) can create issues under load.
• Minimize the number of controls that are linked to a procedure (<15 dynamic controls) for high performance dashboards.
• Utilize "conditional" chaining when it can be applied to improve dynamic chained drop-down controls. This will help initial load time as users navigate within the hierarchy.
• Limit each Dashboard to approximately seven reports and charts.
• Use cached/staged data, where possible.
• Conduct extensive code review by an experienced architect early in the development process. This will eliminate code inefficiencies.
• Reduce the number of separate data requests within each report procedure.
• Reduce the use of common Managed Reporting procedures (-INCLUDE).
• Limit the use of -INCLUDE procedures within each report (<=5 -INCLUDEs) for high performance reports.

x

To verify if WebFOCUS 4GL queries are generating optimized SQL syntax, there are several options that will allow developers to view the SQL syntax being generated.

• Enable traces in the report request. Traces must be disabled when they are no longer needed so performance is not impacted.

  SET XRETRIEVAL = OFF - Specifies that no retrieval is to be performed.
  SET TRACEOFF = ALL - Deactivates all traces for all components.
  SET TRACEON = SQLAGGR//CLIENT - Provides optimization information (formerly FSTRACE3). The CLIENT destination displays the trace output on the screen of an online session.
  SET TRACEON = STMTRACE//CLIENT - Displays generated SQL statements (formerly FSTRACE4). The CLIENT destination displays the trace output on the screen of an online session.
  SET TRACEUSER = ON - Turns dynamic traces on for a user and sends the trace to a named file.
  SET TRACESTAMP = OFF - Omits the timestamp from the trace output.

• Enable SQL tracing in App Studio.
• Capture generated SQL from the Web Console Session Log window.
• Run requests with the Run with SQL trace option from the BI Portal tree.