Fetching Component Tables

In SQL queries that select data from database tables AND component tables, you can influence the order in which these tables are queried. Relevant are:

•Whether a component table supports rowset size hints.

This is determined by the Rowset size hint setting on the State tab page of the Components window.

•The QUERY_ORDER_TURNING_POINT Rules Engine parameter.

•The order of the tables in the FROM clause of a SELECT statement.

Background

In SQL queries that select data from one or more component tables, this is how the Rules Engine determines which table to query first:

•Take the first component table that:

oSupports rowset size hints,

oAND provides an estimation of the expected rowset size, other than -1 (no estimation).

•If there is NO such table, the tables are queried in the order as they appear in the FROM clause. The database tables are always queried within one statement.

•If there IS such a component table, the expected rowset size is compared with the value of the QUERY_ORDER_TURNING_POINT Rules Engine parameter.

oIf the expected rowset size is lower, the component table is queried first, and for each fetched record, the values are substituted in the remaining SQL statement.

Then the remaining SQL statement is processed.

•If the expected rowset size is higher, first the remaining SQL statement is processed, then the component table.

For each fetched record returned from the remaining SQL statement, the values are substituted in the SQL statement sent to the component.

This workflow is illustrated in the "Order of Querying Tables" flowchart.

When to use

Using rowset size hints if querying the table component can take a lot of time, depending on the number of returned records.

Change the QUERY_ORDER_TURNING_POINT parameter only after thorough testing.

Use BenchMark's Tracer to monitor the order of SQL statements, and the conditions within the statements that the Rules Engine executes. The Tracer also displays INVOKE statements sent to components.

To have a table component support rowset size hints:

1.	In the Definer, open the appropriate table component.

2.	Click the State tab page.

3.	Select the Rowset size hint check box.

4.	(Re-)write the RowsetSize() method in the component code, and let this method return an estimation for the expected number of records matching a query condition.

The ROWSETSIZE method has the same input parameter(s) as the QUERYEXE method. If 'Extended query conditions' is selected, this is an XML document.

To change the Query Order Turning Point Rules Engine parameter:

1.	Open the Definer.

2.	From the menu, select Tools, Rules Engine Parameters.

3.	Query for the QUERY_ORDER_TURNING_POINT Parameter Setting.

4.	Change the Value after testing thoroughly.