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 20-5 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
|
|
Figure 20-5
|
Select each item in the query execution profile to see more information about that item. For example, click the filter, as shown in Figure 20-6, 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.
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. As discussed at the beginning of this chapter, 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 20-7 shows an example of the SELECT statement in the original query. Figure 20-8 shows an example of the SELECT statement in the modified query.
As Figure 20-8 shows, BIRT changes the original query to add a filter condition (WHERE clause) and a sort condition (ORDER BY clause).
The performance of a report improves when data is processed by the database rather than by BIRT. Data filtering in particular 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:
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:
|
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"]:
