Getting information about query execution
When a report accesses data from a database, it is useful to understand what queries the report sends to the database, and how charts and tables get their data. For example, if you create a dynamic filter on a table to display sales data for certain products only, does BIRT send a query to retrieve sales data for all products then filter at the table level to display data for specific products, or does BIRT send a query that retrieves only data for specific products? Answers to questions such as this can help you optimize the performance of a report.
To get information about the queries that are executed, right-click a report element, such as a table or a chart, then choose Show Query Execution Profile. Figure 2‑34 shows an example of a query that is executed for a table. In this example, Query Execution Profile shows the following information:
*The data set (Products Data Set) that is bound to the table, the original query specified, and the query modified by BIRT and sent to the database
*A sort definition that sorts data rows by product name in ascending order
*A filter condition (row["QUANTITYINSTOCK"] > 5000
*A group definition that groups data by vendor
*Data bindings associated with the table
Figure 2‑34 Query execution profile for a table
Reviewing where sorts, groups, and filters are executed
Sorting, grouping, and filtering data are resource-intensive activities. The performance of a report improves when data is processed by the database rather than by BIRT. To see where data sorting, grouping, and filtering are processed, select each of these items in the query execution profile.
For example, click a specific filter, as shown in Figure 2‑35, to see whether the filter is executed in BIRT or at the database level. In the filter information, “Push Down: applied” means that the filter is pushed down to, or executed by, the database. Similarly, select the sort and group definitions to see where these tasks are executed.
Figure 2‑35 Filter information displayed in the query execution profile
Reviewing the query that is sent to the database
Another piece of useful information that the query execution profile provides is whether, and how, BIRT modifies a query when you sort, group, or filter data using the graphical tools. BIRT can modify a query to perform these tasks at the database level if the report accesses the database through an information object or a JDBC connection for query builder data source.
Select the Original: SELECT statement to see the query specified originally. Select the Effective: SELECT statement to see the query modified by BIRT. Figure 2‑36 shows an example of the SELECT statement in the original query. Figure 2‑37 shows an example of the SELECT statement in the modified query.
Figure 2‑36 Original query displayed in the query execution profile
As Figure 2‑37 shows, BIRT changes the original query to add a filter condition (WHERE clause) and a sort condition (ORDER BY clause).
Figure 2‑37 Modified (effective) query displayed in the query execution profile
Where data filtering occurs can affect performance significantly because filtering can mean the difference between retrieving hundreds or millions of rows of data.
When you create filters using the graphical filter tool, BIRT pushes a filter to the database if the filter condition can be mapped to a SQL expression (if using the JDBC connection for query builder data source) or an Actuate SQL expression (if using an information object data source). Using that criterion, the following are examples of when BIRT pushes a filter to the database:
*The filter uses an operator supported by the database, for example, <, >, =.
BIRT-specific operators, such as Match, Top Percent, and Bottom Percent, do not have SQL equivalents, so a filter that uses any of these operators is not pushed to the database.
*The filter uses an expression that refers to a field in a database table. For example, the following filter condition is pushed to the database if SalesTotal is a column in the database table:
row["SalesTotal"] Greater than 5000000
On the other hand, the following filter condition is not pushed to the database if Profit is a computed column derived from other columns, for example, row["Sales"] - row["Cost"]:
row["Profit"] Greater than 2000000