Dynamic queries with WHERE, BY and indexes

Posted by tomas.kucera on 29-Nov-2017 04:47

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.

Posted by Peter Judge on 29-Nov-2017 07:52

I’d point you to the slides from Paul Koufalis’ Index part of his and my “ Mastering 4GL Code Performance - Profiling & Indexing” talk from the recent PUG challenge in Prague, if they were posted. He does a good job of describing the index rules and how they’re selected for a query.
 
Attached are the slides. Slides 27-28 describe the rules, and then you’ll see a series of worksheets describing how you can figure out which indexes are used.
 
You can see which indexes are used and where the sorting happens via the LOG-MANAGER’s QryInfo LOG-ENTRY-TYPE.
 

Posted by WJCPvanBeek on 29-Nov-2017 08:53

[View:/cfs-file/__key/communityserver-discussions-components-files/19/The-Index-Rulez.pdf:320:240]

Posted by WJCPvanBeek on 29-Nov-2017 10:19

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]

All Replies

Posted by Peter Judge on 29-Nov-2017 07:52

I’d point you to the slides from Paul Koufalis’ Index part of his and my “ Mastering 4GL Code Performance - Profiling & Indexing” talk from the recent PUG challenge in Prague, if they were posted. He does a good job of describing the index rules and how they’re selected for a query.
 
Attached are the slides. Slides 27-28 describe the rules, and then you’ll see a series of worksheets describing how you can figure out which indexes are used.
 
You can see which indexes are used and where the sorting happens via the LOG-MANAGER’s QryInfo LOG-ENTRY-TYPE.

Posted by WJCPvanBeek on 29-Nov-2017 08:23

Attached is a more extensive explanation of the index rules, I wrote some years ago for my courses

Posted by WJCPvanBeek on 29-Nov-2017 08:27

Pity, can't figure out how to attach a file....

Posted by Mike Fechner on 29-Nov-2017 08:30

Easiest is reply via Email
Von: WJCPvanBeek [mailto:bounce-WJCPvanBeek@community.progress.com]
Gesendet: Mittwoch, 29. November 2017 15:28
An: TU.OE.Development@community.progress.com
Betreff: RE: [Technical Users - OE Development] Dynamic queries with WHERE, BY and indexes
 
Update from Progress Community
 

Pity, can't figure out how to attach a file....

View online

 

You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

Flag this post as spam/abuse.

 

Posted by WJCPvanBeek on 29-Nov-2017 08:37

I have send the mail with the attachment to tu.oe.development@community.progress.com. However it doesn't appear here.

Posted by jankeir on 29-Nov-2017 08:43

If you click use rich formatting next to the reply button there's an 'attach file' button. No idea if it works.

Posted by James Palmer on 29-Nov-2017 08:45

Alternatively mail it to someone (james [at] principal [dot] ie) and ask them to get it attached for you ;)

Posted by WJCPvanBeek on 29-Nov-2017 08:53

[View:/cfs-file/__key/communityserver-discussions-components-files/19/The-Index-Rulez.pdf:320:240]

Posted by WJCPvanBeek on 29-Nov-2017 08:53

Thx Jan, the rich format indeed had a working attachment button

Posted by WJCPvanBeek on 29-Nov-2017 08:58

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.

Posted by Rob Fitzpatrick on 29-Nov-2017 09:45

Thanks for that document Will, it's a keeper!  Very detailed.  Would you be willing and able to share the code referenced in it?

Posted by tomas.kucera on 29-Nov-2017 09:47

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. :(

Posted by Peter Judge on 29-Nov-2017 09:50

BY will be ignored when the field is part of an equality match. There’s some info in the doc I attached.

Posted by tomas.kucera on 29-Nov-2017 09:52

Yes, now I have the picture. Well ... one learns a new thing every day. :)

Posted by WJCPvanBeek on 29-Nov-2017 10:19

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]

Posted by WJCPvanBeek on 29-Nov-2017 10:22

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.

Posted by Rob Fitzpatrick on 29-Nov-2017 11:05

> The "Index Rules.pdf" file refers to some programs. They are in the attachment.

Great, thank you!

Posted by Patrick Tingen on 29-Nov-2017 14:20

You could add an index consisting of the fields sales-rep and name...

Posted by WJCPvanBeek on 30-Nov-2017 02:24

> 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

Posted by tomas.kucera on 30-Nov-2017 04:53

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. ;)

Posted by WJCPvanBeek on 30-Nov-2017 05:17

Agreed, put in an enhancement request :-)

Posted by tomas.kucera on 30-Nov-2017 06:32

Just created an Enhancement Request ... please vote! :D

Separate Index Selection for WHERE and for BY

This thread is closed