24.5 Accessing the Data

If your performance measuring tools show that the report spends a large amount of time accessing data from the data source(s), you must review the structure of the data and determine how the data is being used. Inefficient schema design has a dramatic affect on the performance of a report. For example, an overly normalized data model can result in many avoidable joins or queries.

This section discusses ways to review and improve the efficiency of the data used in your report:

24.5.1 Non-SQL Data Sources

To publish data from any data source, use the pluggable data source architecture in Oracle Reports. Out-of-the-box Oracle Reports supports non-SQL data sources, such as XML, Text, and JDBC pluggable data sources. Both XML and Text pluggable data sources can be accessed through a remote URL (even across firewalls). If speed is a concern, download the data locally and use the local data stream rather than a remote URL. You can also specify the domains for which you can bypass a proxy server.

The XML pluggable data source supports runtime XML data validation. Select the Validate Data Source check box in the XML Query Wizard to ensure that the XML data is verified as it is fetched against the data definition specified in the DTD or in the XML schema. This is a very costly operation and proves to be useful only when you develop the report and not during production. You will see a noticeable performance difference when the XML data stream is very large.

You can specify either an XML schema or a DTD schema for the data definition. An XML schema forces type checking, whereas a DTD schema does not require type checking as all data is treated as strings.

Note:

Ensure that the data types of the non-SQL sources match column wise.

You can also specify an extensible style sheet language (XSL) file for the XML data stream to convert it from any format into a simple row set/row data feed. It is better to have data in the correct format to start with, unless you want to apply the XSL at run time.

Pluggable Text data sources support the use of cell wrappers. This causes the file format level delimiter to be ignored for every field that has a wrapper defined. Avoid using cell wrappers unless really required.

The JDBC pluggable data source supports JDBC bridges, as well as thick and thin JDBC drivers. Selecting the driver directly impacts the fetching of data. The choice depends on the application and the database being used. Using a native driver generally results in better performance. For more information, see Chapter 14, "Configuring and Using the Pluggable Data sources".

24.5.2 Database Indexes

Columns used in a SQL WHERE clause should be indexed. The impact of indexes used on columns in the master queries of a report are minor, as these queries access the database once. To improve performance significantly, indexes should be used on any linked columns in the detail query.

Note:

Lack of appropriate indexes can result in many full-table scans and slows down performance.

24.5.3 Calculations

Within a report (either through summary or formula columns), ensure that most of the calculations are performed by the data source. In case of SQL queries, calculations are performed on the database rather than on the data retrieved by the report. User-defined functions and procedures stored by the database can also be included in the query select list of an Oracle Database or a JDBC query. This is more efficient than using a local function, since the calculated data is returned as part of the result set from the database.

Example

The following PL/SQL function can be stored in the Oracle Database:

CREATE OR REPLACE FUNCTION CityState (
  p_location_id world_cities.location_id%TYPE)
  RETURN VARCHAR2 is
    v_result VARCHAR2(100);
BEGIN
  SELECT city || ','||state
  INTO v_result
  FROM world_cities
  WHERE location_id = p_location_id;
  RETURN v_result;
END CityState;

This function returns the city separated by a comma, a space, and the state. This formatting is done at the database level and passed back to the report to display.

In the report, the SQL query would look like:

SELECT location_id, citystate(location_id)"City
& State" FROM world_cities

The result would look like this:

LOCATION_ID CITY & STATE
----------- -------------------------
          1 Redwood Shores, California
          2 Seattle, Washington
          3 Los Angeles, California
          4 New York, New York

24.5.4 Redundant Data

A report's query should ideally select only required columns. The fewer queries you have, the faster your report will run. Single-query data models execute more quickly than multiquery data models. However, situations can arise where a report not only needs to produce a different format for different users, but also needs to utilize different query statements. Although this can be achieved by producing two different reports, it may be desirable to have a single report for easier maintenance. In this instance, the redundant queries should be disabled using the SRW.SET_MAXROW built-in procedure.

Note:

For a description of the SRW built-in package, including the SRW.SET_MAXROW built-in procedure, see the Oracle Reports online Help.

Example

The following code used in the Before Report trigger will disable either Query_Emp or Query_Dept, depending on the user parameter:

IF :Parameter_1 = 'A' THEN
  SRW.SET_MAXROW('Query_Emp',0);
ELSE
  SRW.SET_MAXROW('Query_Dept',0);
END IF;

Note:

The only meaningful place to use the SRW.SET_MAXROW built-in procedure is in the Before Report trigger (after the query has been parsed). Calling the SRW.SET_MAXROW built-in procedure after this point raises the SRW.MAXROW_UNSET built-in exception. The query will still be parsed and bound, but no data will be returned to the report.

You can define a query based either on an XML or a Text pluggable data source by selecting the fields to be used in the query (that is, all available fields or a subset). If you must use a subset of the fields, do so at the query level using parameters, as opposed to fetching all the values and filtering them using a group filter or layout level format triggers.

24.5.5 Break Groups

Limit the number of break groups to improve your report's performance. Oracle Reports sets the break level for each column in the data model that has the break order property set except the lowest child group.

For a SQL query, Oracle Reports appends this as an extra column to the ORDER BY clause in the query. The fewer columns in the ORDER BY clause, the less work the database has to do before returning the data in the required order. Creating a break group may render an ORDER BY clause redundant in spite of defining it as part of the query. Remove any such ORDER BY clauses as it requires extra processing by the database.

If your report requires the use of break groups, set the Break Order property for as few columns as possible. A break order column is indicated by a small arrow to the left of the column name in the group in the Reports Builder Data Model View. Each break group above the lowest child group of a query requires at least one column to have the Break Order property set. Removing the break order from columns where sorting is not required increases performance.

Limit break groups to a single column whenever possible. These columns should be as small as possible and be database columns (as opposed to summary or formula columns) wherever feasible. Both conditions help the local caching that Oracle Reports does, before the data is formatted for maximum efficiency. Clearly, these conditions cannot always be met but can increase efficiency whenever utilized.

24.5.6 Group Filters

Group filters reduce the number of records displayed. Filtering takes place after the query returns the data (from the data source) to Oracle Reports. Even if the filter is defined to display only the top five records, the result set will contain all the records returned by the query. Hence, it is more efficient to incorporate the group filter functionality into the query's WHERE clause or into the Maximum Rows property, whenever possible. This restricts the data returned by the database.

24.5.7 To Link or Not To Link

There are a number of ways to create data models that include more than one table. Consider the standard case of the dept/emp join, with the requirement to create a report that lists all the employees in each department in the company. You can create either of the following:

  • Single query:

    SELECT d.dname, e.ename
    FROM emp e, dept d
    WHERE e.deptno(+) = d.deptno
    

  • Two queries with a column link based on deptno:

    SELECT deptno, dname FROM dept
    SELECT deptno, ename FROM emp
    

When you design the data model in the report, minimize the actual number of queries by using fewer large multitable queries, rather than several simple single-table queries. Every time a query is run, Oracle Reports needs to parse, bind, and execute a cursor. A single query report returns all the required data in a single cursor, rather than many cursors. With master-detail queries, the detail query will be parsed, bound, and executed again for each master record retrieved. In this example, it is more efficient to merge the two queries and use break groups to create the master-detail effect.

Keep in mind that the larger and more complex a query gets, the more difficult it is to be maintained. You must decide when to achieve the balance between performance and maintenance requirements.