Query Performance Considerations

For performance reasons, you should avoid using too many large queries in one report. In addition, you should only run a query from script in cases where you cannot directly bind the query. In general, it is more efficient to use the 'Data Binding' dialog box to bind the query to an element, which allows the query to automatically run as part of report generation.

There are two complimentary methods for controlling and improving the performance of a report.

• Size limit: You can limit the number of rows that the query returns.
• Time limit: You can limit the amount of time a query may execute.

For example, if you only use the first few rows of a query, you should set a tight limit on the query size. You can make these settings at the query level or data binding level. See Advanced Toolbar Buttons in the Data Modeler to limit at query level, and Precautions and Safeguards in the Report Designer to limit at binding level.

Query Example

Assume that you need to create a table containing sales summary information: Total sales volume, product with highest volume, and customer with the highest volume. There are several possible ways that you can design this table:

• Write a stored procedure to return the three unrelated aggregates as the result of a single query, and bind the table to this query. However, designing such a stored procedure is awkward and requires advanced knowledge of database programming. Therefore, this approach is not recommended.
• Use a Data Worksheet to compute the three desired aggregates, and merge these into a single Data Block. Then bind the table to this Data Block. (See the Data Worksheet for details).
• Design three simple queries to return the three desired aggregates. Use script to independently run the three queries, and then explicitly assign the results to corresponding table cells.

The following example illustrates the third approach. Assume that you have defined the following three simple queries in the Data Modeler:

• 'total sales' – returns total sales volume
• 'top product' – returns product with highest volume
• 'top customer' – returns customer with the highest volume

Follow the steps below to create the table that displays these values:

1. Create a new table with three rows and two columns.
2. Select each of the three cells in the left column, and enter the following strings into the cells:
1. “Total Sales”
2. “Top selling product”
3. “Highest Volume Customer”
3. Right-click the table, and select 'Script' from the context menu. This opens the Script Editor for the table.
4. Enter the following script. This runs each query in turn, and assigns the result to the corresponding table cell.

var sales = runQuery('total sales');

var product = runQuery('top product');

var customer = runQuery('top customer');

table[0][1] = formatNumber(sales[1][0], "$#,###.00");

table[1][1] = product[1][0];

table[2][1] = customer[1][0];