Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

DataServer generates different queries depending on use of USE-INDEX or BY clause

« Go Back

Information

 
Article Number000064823
EnvironmentProduct: OpenEdge
Version: 11.3.x, 11.4.x, 11.5.x, 11.6x,11.7x
OS: All supported platforms
Other: DataServer for Oracle, DataServer for Microsoft SQL Server
Question/Problem Description
DataServer generates different queries depending on use of USE-INDEX or BY clause.

DataServer generates different queries depending on use of USE-INDEX or BY clause when it uses its single-shot query mechanism

Query processes differently depending on use of USE-INDEX or BY clause. 

SQL query generated by the DataServer can use a client-side join or server-side join depending on whether USE-INDEX is specified in the query or if the index fields are specified in the BY clause.

OpenEdge Data Management: DataServer for Oracle, (page 46) explicitly states that these should be equivalent:
       
"Index definitions support ABL USE-INDEX modifier. ABL translates USE-INDEX to ORDER BY for DataServer operations. For example, if you define city-dept as an index on the city and department fields, the following ABL statements are equivalent when accessing an Oracle database:

FOR EACH employee USE-INDEX city-dept:
FOR EACH employee BY employee.city BY employee.department:"

 
Steps to Reproduce
Clarifying Information
SQL query generated by the DataServer can use a client-side join or server-side join depending on whether USE-INDEX is specified in the query or if the index fields are specified in the BY clause when the query uses the single-shot query mechanism.

The single-shot query mechanism will be used when a query uses a format similar to the following:
FOR EACH <table1>,
   FIRST / LAST <table2> OF <table1>:
END.

Sample queries against Sports2000:

For this query, The join will be processed server-side:
FOR EACH customer NO-LOCK USE-INDEX custnum WHERE custnum = 1, 
FIRST order OF customer USE-INDEX custorder: 
END.

For this query, the join will be processed client-side:
FOR EACH customer NO-LOCK WHERE custnum = 1, 
FIRST order OF customer 
BY customer.custnum BY order.custnum BY order.ordernum: 
END.
Index rules used by single-shot query mechanism not covered in DataServer Documentation.
 
Error Message
Defect/Enhancement NumberDefect PSC00343302 / ODIA-940
Cause
This is expected behaviour and part of the single-shot query mechanism. When this mechanism is used, queries that have both a WHERE clause and a BY clause will use a client-side join.
Resolution
Documentation Defect PSC00343302 / ODIA-940 has been logged requesting that the Index rules used by the single-shot query mechanism be added to the DataServer manuals, where the OpenEdge 12.0 DataServer manuals include this detail.

Single-shot Server side Join selection is excluded for the scenarios listed below:
  • When the ABL queries have the BY clause in it.
  • When the ABL queries have multiple single shot statements.
  • When the ABL queries have OUTER-JOIN.
Single-shot evaluation on server is ON by default. It can be turned OFF and evaluated at client side by:
  1. Using client startup parameter -nojoinbysqldb.  
  2. It can be turned OFF at run time with QUERY-TUNING switch NO-JOIN-BY-SQLDB.
Workaround
Notes
References to Other Documentation:

OpenEdge Development: Use the Oracle Data Server:
  • Additional Features to Enhance DataServer Performance, Improving join performance for queries with FIRST or LAST criteria
OpenEdge Data Management: DataServer for Microsoft SQL Server,
  • Additional Features to Enhance DataServer Performance, Improving join performance for queries with FIRST or LAST criteria
Progress Article:

000094715, Oracle DataServer returns rows in the wrong order  
Attachment 
Last Modified Date5/9/2019 7:30 AM