Progress
DataServer
for ODBC Guide


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:

To estimate whether performing a join by the data source might improve performance, the DataServer assesses whether these additional criteria are true:

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–12 describes how these controls interact and affect the behavior.

Table 2–12: 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 © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095