Optimize Joined Column Fetch

Specify whether joined columns should be fetched together with the main query.

Background

You can set the Optimize Joined Columns property to Yes (default No) to make the Rules Engine fetch joined columns together with the main query columns with a join. The result is that when records are queried, the SELECT statement sent to the RDBMS fetches virtual columns as a join with the main query, instead of using a separate COMPUTE_VIRTUAL query.

When to use

Consider setting this property for large tables from which users often query multiple records, and that contain a limited number (1 or 2) of virtual columns. You can easily measure the effectiveness by performing a comparative test with BenchMark.

To fetch joined columns together with the main query of a window:

1.	Use the Class Catalog to select and open the appropriate window.

2.	Open the Property Inspector, and click the General tab page.

3.	Set the Optimize Joined Column Fetch property to Yes.1

Limitations

You can see the resulting difference in the SQL statement in BenchMark Profiler output only. Perhaps confusingly, the standard main query generated by USoft and displayed at property Underlying Query remains the same. Because joins are generally faster on the server than separate statements, Optimize Joined Columns might seem like a good idea. However, multiple table joins where more than about three or four tables are queried are slower than the standard handling of virtuals.