Managing Application Performance
- Application User Interface must be designed around
providing optimal performance.
- Reports must be coded efficiently to meet expected response
- Ensure that data attributes such as primary keys, indexes, and
foreign keys are specified correctly in the WebFOCUS metadata and
match those in the data structures.
- Data retrieval performance must be efficient and optimal and
is dependent on:
Data structures. An inefficiently
defined data structure will result in bad performance and will affect
both WebFOCUS and SQL Passthru requests.
Database optimization. The database must be tuned optimally
to manage the workload.
Query optimization. Queries must be tuned for fast retrieval.
Data volume. Consideration must be given to the amount
of data being accessed and the resulting answer set.
Managing Large Data Volumes
Some options for managing large
volumes of data in applications include:
- HOLD files
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.
- 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.
- Minimize the number of requests per page.
- Reports and Graphs
- 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
- 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
- Reduce the use of common Managed Reporting procedures (-INCLUDE).
- Limit the use of -INCLUDE procedures within each report (<=5
-INCLUDEs) for high performance reports.
Verifying Queries Are Optimized
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.