For queries that include joins issued in FOR EACH and OPEN QUERY statements, the DataServer evaluates the queries and in some cases instructs the MS SQL Server data source to perform the joins. A join performed by the data source, called a join by SQLDB, can improve performance; however, you receive the associated query results in an order consistent with the data source, not with the OpenEdge database. To get results that are consistent with the OpenEdge database, turn off JOIN-BY-SQLDB, either with the QUERY-TUNING phrase at the query level or with the Server Join (-nojoinbysqldb) startup parameter when you compile. If the order of returned records is important to your application, specify a sort order on the query.
In DataServers, JOIN-BY-SQLDB is the default behavior for outer join operations.
For each join, the DataServer evaluates whether the MS SQL Server data source can perform it and estimates whether doing so improves performance. To determine whether a join by SQLDB is possible, the DataServer assesses whether the following criteria are true:
All tables in the join are in the same logical OpenEdge database; that is, they are contained in the same DataServer schema.
Every table, except the innermost one, has a unique record identifier (ROWID or RECID support).
The query does not include a USING phrase for any of the inner tables. For example, a join by SQLDB will not occur for this query:
FOR EACH customer, EACH order OF customer USING order.ordernum:
The query does not include a BY phrase that contains expressions or array fields.
The query does not include a request for an EXCLUSIVE-LOCK on any of the tables in the join.
There is no nested FOR block in any of the tables in the join.
The join does not exceed 10 levels.
To estimate whether performing a join by the data source might improve performance, the DataServer assesses whether these additional criteria are true:
The join uses an OF clause or a WHERE clause for each of the inner table loops. For example, the following query requires a field-to-field correspondence between two tables:
FOR EACH customer, EACH order OF customer:
The WHERE clause includes either an operator or the AND option. The following example includes the equals (=) operator:
FOR EACH customer, EACH order
WHERE customer.custnum = order.custnum:
The DataServer also performs a join by SQLDB for the following query:
FOR EACH customer, EACH order
WHERE customer.custnum = order.custnum AND customer.custnum GT 100:
However, for the following query, the DataServer instructs the client to perform the join because of the OR option:
FOR EACH customer, EACH order
WHERE customer.custnum = order.custnum OR customer.custnum GT 100:
By default, the dynamic joins, the inner joins, and the outer joins are performed by the MS SQL database server, provided the criteria set by the DataServer is fulfilled. For more information on joins, see OpenEdge Data Management: SQL Development.
An Unknown value (?) in OpenEdge is mapped to a NULL value in MS SQL Server; When you perform a JOIN-BY-SQLDB, MS SQL does not join the records in the parent table to the records in the child table on NULL join key values. For an outer join performed with JOIN-BY-SQLDB, if the results set contains a parent record with a NULL join key value and a non-matching child record, all field values in the child record are set to NULL. In contrast, when you perform a join by client, records that are joined on the Unknown value (?) (to which NULLs correspond), the results set includes any matching parent and child records in the join results of both inner and outer joins.
By default, the DataServer instructs a MS SQL Server data source to perform a join when possible and when desirable. However, you can control the default behavior by using either the QUERY-TUNING NO-JOIN-BY-SQLDB phrase or the Server Join (-nojoinbysqldb) startup parameter. The QUERY-TUNING phrase controls the behavior for a single query. The -nojoinbysqldb parameter controls it at the session level. The query-level setting overrides the session-level setting.
The following table describes how these controls interact and affect the behavior.
Table 25. Controlling join by SQLDB behavior
QUERY-TUNING
Startup Parameter
Behavior
JOIN-BY-SQLDB
-nojoinbysqldb
The MS SQL Server data source performs the join if possible.
JOIN-BY-SQLDB
None
The MS SQL Server data source performs the join if possible.
NO-JOIN-BY-SQLDB
-nojoinbysqldb
The client performs the join.
NO-JOIN-BY-SQLDB
None
The client performs the join.
None
-nojoinbysqldb
The client performs the join.
None
None
The MS SQL Server data source performs the join if possible and the join contains the selection criteria described previously.
A join by SQLDB does not occur by default for the following query because the DataServer determines that it does not increase performance:
FOR EACH customer, EACH order:
You receive a warning if you specify JOIN-BY-SQLDB when the MS SQL Server data source cannot perform the join and the DataServer performs the join instead. You receive a warning at compile time if you specify JOIN-BY-SQLDB when the data source can perform the join but it is not optimal for it to do so.
When the join keys contain the Unknown value (?) for OpenEdge, those record matches are included in the join results. This is in contrast to the NULL value for SQL databases whereby
All tables in the join are in the same logical OpenEdge database; that is, they are contained in the same DataServer schema.
