Status: Unverified
SYMPTOM(s):
DataServer for MS SQL Server is not generating server-side joins when querying across multiple SQL Server databases
Joining tables from multiple SQL Server databases results in a client-side join
Performance impacted due to DataServer for MS SQL Server generating client-side joins when joining / querying tables from multiple SQL Server databases
Example #1 - joining related tables from different databases:
FOR EACH sports102A.customer,
EACH sports102B.order_ WHERE sports102B.order_.custnum = sports102A.customer.custnum:
DISP sports102A.customer.custnum sports102B.order_.custnum sports102B.order_.ordernum sports102B.order_.orderDate.
END.
Result from dataserv.lg shows 2 separate SELECT queries are executed (1 per table) and results in a client-side join:
Execute on 0x14b1538 Firehose Connection Level 1 new: SELECT custnum, country, name, address, address2, city, state, postalcode, contact, phone, salesrep, creditlimit, balance, terms, discount, comments, fax, emailaddress, PROGRESS_RECID, PROGRESS_RECID_IDENT_ FROM Sports102A.dbo.customer ORDER BY country, postalcode, PROGRESS_RECID (15069)
..
Execute on 0x14b2718 Firehose Connection Level 1 new: SELECT ordernum, custnum, orderdate, shipdate, promisedate, carrier, instructions, po, terms, salesrep, billtoid, shiptoid, orderstatus, warehousenum, creditcard, PROGRESS_RECID, PROGRESS_RECID_IDENT_ FROM Sports102B.dbo.order_ WHERE ((custnum = ?)) ORDER BY custnum, ordernum (15069)
Example #2 - joining related tables from the same database:
FOR EACH sports102B.customer,
EACH sports102B.order_ WHERE sports102B.order_.custnum = sports102B.customer.custnum:
DISP sports102B.customer.custnum sports102B.order_.custnum sports102B.order_.ordernum sports102B.order_.orderDate.
END.
Result from dataserv.lg shows a single SELECT query that joins both tables and results in a server-side join:
Execute on 0x14b2718 Firehose Connection Level 1 new: SELECT T0.custnum, T0.country, T0.name, T0.address, T0.address2, T0.city, T0.state, T0.postalcode, T0.contact, T0.phone, T0.salesrep, T0.creditlimit, T0.balance, T0.terms, T0.discount, T0.comments, T0.fax, T0.emailaddress, T0.PROGRESS_RECID, T0.PROGRESS_RECID_IDENT_, T1.ordernum, T1.custnum, T1.orderdate, T1.shipdate, T1.promisedate, T1.carrier, T1.instructions, T1.po, T1.terms, T1.salesrep, T1.billtoid, T1.shiptoid, T1.orderstat (15069)
[YY/MM/DD@HH:MM:SS.###+0100] P-###### T-###### 2 MSS CURSOR Connection 0x14b2718 @{#8}
[YY/MM/DD@HH:MM:SS.###+0100] P-###### T-###### 1 MSS -- us, T1.warehousenum, T1.creditcard, T1.PROGRESS_RECID, T1.PROGRESS_RECID_IDENT_ FROM Sports102B.dbo.customer T0, Sports102B.dbo.order_ T1 WHERE ((T0.custnum = T1.custnum))
Using JOIN-BY-SQLDB query-tuning parameter causes errors 3237 and 3264
Program <program-name>, line <line-number> warning: Cannot join this query by the database server. (3237)
Program <program-name>, line <line-number>: PROGRESS will perform the join by the client. (3264)
Adding SEPARATE-CONNECTIONS query-tuning parameter has no effect
Definitions for SQL Server databases are stored in separate schemas / schema holders
FACT(s) (Environment):
Client-side joins result in a performance hit
Progress 9.1x
OpenEdge 10.x
Windows
CAUSE:
This is expected behaviour. Per the OpenEdge® Data Management: DataServer for Microsoft® SQL Server manual, one of the criteria that must be met is that all tables in the join are in the same logical OpenEdge database; that is, they are contained in the same DataServer schema. In this case, table definitions are stored in different logical schemas which renders server-side joins impossible.
FIX:
Pull definitions from different MS SQL Server databases into a single logical schema / schema holder. This can be achieved by pulling the definitions from one SQL Server database, then changing ODBC DSN to pull definitions from other SQL Server database(s). For more information, please refer to solution P183705.
Note that this option will only work for databases located in the same MS SQL Server instance.