Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

> ## Join by SQL DB

> For queries that include joins issued in `FOR` `EACH` and `OPEN` `QUERY` statements, the DataServer evaluates the queries and in some cases instructs the ODBC data source to perform the joins. A join performed by a data source, called a join by SQL DB, can improve performance; however, you receive the associated query results in an order consistent with the data source, not with Progress. To get results that are consistent with Progress, 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.

> For each join, the DataServer evaluates whether the ODBC data source can perform it and estimates whether doing so improves performance. To determine whether a join by SQL DB 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.

> - The query does not include a USING phrase for any of the inner tables. For example, a join by SQL DB will not occur for this query:

>     FOR EACH customer, EACH order OF customer USING order-num:

> - 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.

> - 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.cust_num = order.cust_num:

> The DataServer also performs a join by SQL DB for the following query:

>     FOR EACH customer, EACH order
>     		WHERE customer.cust_num = order.cust_num AND cust_num > 100:

> For the following query, however, the DataServer instructs the client to perform the join because of the `OR` option:

>     FOR EACH customer, EACH order
>     		WHERE customer.cust_num = order.cust_num OR customer.cust_num > 100:

> By default, the DataServer instructs an ODBC 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.

> Table 2–11 describes how these controls interact and affect the behavior.

> Table 2–11: Controlling join by SQLDB behavior
> QUERY–TUNING | Startup parameter | Behavior
> --- | --- | ---
> `JOIN–BY–SQLDB` | `-nojoinbysqldb` | The ODBC data source performs the join if possible.
> `JOIN–BY–SQLDB` | None | The ODBC 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 ODBC data source performs the join if possible and the join contains the selection criteria described previously.

> A join by SQL DB 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 ODBC 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.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095