Kbase P120701: Query performance with a join is very poor with client/server connection
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/15/2010 |
|
Status: Unverified
SYMPTOM(s):
Query performance with a join is very poor
Query with a join run via a client server connection can be very slow.
Query with a client server connection can be up to 10 times slower than a shared memory connection.
A dynamic query can be very slow client server. For example:
vcQry = "FOR EACH RoleResource FIELDS(RoleResource_ID Role_ID) NO-LOCK"
+ " WHERE RoleResource.Role_ID = 7328,"
+ "FIRST Resources FIELDS(Resource_ID) NO-LOCK"
+ " WHERE Resources.Resource_ID = RoleResource.Resource_ID".
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
Enhancement Request# 0000003588
CAUSE:
This is due to the way query resolution is currently designed to work when a join is involved.
If there isn't a join and records are being read NO-LOCK, the server can "read ahead" and pack lots of records into a single message. However if there is a join, tables records are interleaved and the "read ahead" is limited to only those records that belong to a given parent. This results in much more network activity and subsequently there is less advantage from the groupings of records per message.
FIX:
This is not implemented yet.
In some situations improvements in performance could be made by using PRESELECT instead of FOR and using FIELD LISTS. But this should be evaluated in each situation.