Hi all, I have a question regarding the following query:
FOR EACH customer WHERE customer.sales-rep EQ 'DKP' NO-LOCK BY name
When I look at the indexes being used then only the Sales-Rep is there. When removing the WHERE it is the Name index. The combination of WHERE and BY thus causes that it performs server side WHERE resolution but the client side sorting.
Both are single component. I have expected that both the Sales-Rep (for WHERE resolution) and Name (for sorting) would be used.
The questions are:
1) Is this correct behaviour?
2) Can this be optimized, so it would perform server side sorting instead of client side? Not by using USE-INDEX!
It is all under OE11.6.
Thanks in advance!
Kind Regards,
Tom.
[View:/cfs-file/__key/communityserver-discussions-components-files/19/The-Index-Rulez.pdf:320:240]
The "Index Rules.pdf" file refers to some programs. They are in the attachment.[View:/cfs-file/__key/communityserver-discussions-components-files/19/TheIndexRules.zip:320:240]
Attached is a more extensive explanation of the index rules, I wrote some years ago for my courses
Pity, can't figure out how to attach a file....
I have send the mail with the attachment to tu.oe.development@community.progress.com. However it doesn't appear here.
If you click use rich formatting next to the reply button there's an 'attach file' button. No idea if it works.
Alternatively mail it to someone (james [at] principal [dot] ie) and ask them to get it attached for you ;)
[View:/cfs-file/__key/communityserver-discussions-components-files/19/The-Index-Rulez.pdf:320:240]
Thx Jan, the rich format indeed had a working attachment button
Hi Tomas,
Take a look at the attached "The index rules.pdf" above.
- Single-index rule nr.3 is the one that progress selects. After that Progress is done and doesn't continue with the other rules.
- SIngle-index rule nr 5 (handling the BY) is never reached.
Thanks for that document Will, it's a keeper! Very detailed. Would you be willing and able to share the code referenced in it?
Thanks all for replying!
Well, I have read this many times before but always would have thought that it would choose two indexes.
Now reading this many times again and again, I am getting to the point of realising that the multiple indexes are only selected if there are OR / AND in WHERE phrase. But the BY is not really included into multiple index selection unless the rule for most active sort match is applicable. Am I correct by stating this?
That would mean that if there is WHERE and BY then the BY will be ignored in majority of cases and thus all these will be Client Sorted.
Hmmm ... is it really so? So far all my tests seem to confirm this. My queries (simple and complex) had resolved nicely the WHERE phrase but as soon as there was both WHERE and BY involved then so far 100% of them is Client Sort: Y. :(
Yes, now I have the picture. Well ... one learns a new thing every day. :)
The "Index Rules.pdf" file refers to some programs. They are in the attachment.[View:/cfs-file/__key/communityserver-discussions-components-files/19/TheIndexRules.zip:320:240]
That is correct Tomas.
In your case where you have salesrep in the where and name in the BY, progress uses the alesrep index to select a number of rowid's that conform to the where clause. After that these rowid's are sorted using the BY, So if you have a BY you double the number of database reads.
> The "Index Rules.pdf" file refers to some programs. They are in the attachment.
Great, thank you!
You could add an index consisting of the fields sales-rep and name...
> You could add an index consisting of the fields sales-rep and name
That would ensure that the BY doesn't force Progress to reread all the records already selected by the WHERE
Will, Peter and Paul (per procurationem),
Thank you all for your patience and valuable help! I understand the mechanism.
Still think that two pass index selection one for WHERE and one for BY would improve the QUERY resolution performance. ;)
Agreed, put in an enhancement request :-)
Just created an Enhancement Request ... please vote! :D
