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!
The Index Rulez.pdf
The "Index Rules.pdf" file refers to some programs. They are in the attachment.TheIndexRules.zip
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....
You received this notification because you subscribed to the forum. To unsubscribe from only this thread,
post as spam/abuse.
Architect of the SmartComponent Library and WinKit
I have send the mail with the attachment to email@example.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 ;)
Thx Jan, the rich format indeed had a working attachment button
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. :)