Source Order Optimization

You can re-order source schemas on the top frame on the Optimize tab to improve query performance. To move a schema up or down, select the schema and click the up or down arrow buttons to the right of the list of schemas.

When a query uses data from two sources, the Liquid Data Server brings the two data sources into memory and creates an intermediate result (cross-product) using the two sources. If you specify more than two sources, the Liquid Data Server creates a cross-product of the first two sources, then continues to integrate each additional resource, one at a time, in the order that they appear in FOR clauses.

The size of a source is the number of tuples, or records, used in the query from that source. The size of the intermediate result depends on the input size of the first source multiplied by the input size of the second source and so on. A query is generally more efficient when it minimizes the size of intermediate results.

The order of the FOR clauses in the query matches the order of the data sources in the Source Order list. In general, you should order sources in ascending order by increasing size—that is, the smallest resource should appear first in the list and the largest resource should appear last.

Example: Source Order Optimization

Consider a query to find all managers and the departments they manage that contains a three-way join across three sources: Employees, Employees2 (Employees opened a second time), and Departments. This query joins the Employees schema ID field and the Employees2 schema Manager_id to return all managers, and joins on the Employees schema Dept_id and Departments schema Department_no to return the corresponding department information. The generated XQuery language looks like the following example.

for $EMP1 in document("Employees")/db/EMP
for $EMP2 in document("Employees")/db/EMP
for $DEPT in document("Department")/db/DEPT
where $EMP1/id eq $EMP2/manager_id and
	$EMP1/dept_id eq $DEPT/department_no
...

This creates a cross-product of Employees ID and Employees Manager_id, then a cross-product with Departments Department_no. If there are 100 employees, and five departments, the query would generate (100  *  100)  +  (10,000  *  5) intermediate results.

A better plan would be to combine Employees with Departments first, then combine that result with Employees2. The effect is to generates (100  *  5)  +  (500  *  100) intermediate results. The generated XQuery language looks like the following example.

for $EMP1 in document("Employees")/db/EMP
for $DEPT in document("Department")/db/DEPT
for $EMP2 in document("Employees")/db/EMP
where $EMP1/id eq $EMP2/manager_id and
	$EMP1/dept_id eq $DEPT/department_no
...