Kbase P163504: 4GL/ABL: Why is the time to execute a parent/child query much higher than time to execute the a pare
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/9/2010 |
|
Status: Unverified
GOAL:
4GL/ABL: Why is the time to execute a parent/child query much higher than the time to execute a parent only query?
GOAL:
Why does it take, on a Self Service Connection, about 5 times longer to execute a FOR EACH query joining the Customer and Order tables of the Sports2000 than to execute a FOR EACH against the Customer table alone?
GOAL:
Why does it take, on a Remote TCP Connection, about 11 times longer to execute the FOR EACH joining the Customer and Order tables of the Sports2000 than to execute a FOR EACH against the Customer Table alone?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
When executing a join over a network, the "prefetch" optimization where we pack many records into a single network message is gone. That explains the difference between local and network performance for joins.
As for a local join, it is not the same to read all the records in a table as opposed to reading them though a join. In the join case, there has to be a separate query for each parent record, over the child records. This query does not have to be re-prepared, but it does have to be reopened, since the parent join field has to be extracted and placed into the child query for the re-open. This involves extra calls to the database layer and extra processing on the client side.
The reason we do it this way is to accommodate cross-database joins among other things. When reading records for children and parents, we do not bring over an extra copy of the parent record for each of the child records (as is done in a SQL query) so for cases where there are many child records for a single parent it comes out better.
In cases over a network where this is a severe problem, we suggest using an AppServer to do the join, where the AppServer is a local client to the database. That ends up being similar to a SQL case since in any language the parent join field must be extracted to form the child query.