Dynamic queries with WHERE, BY and indexes - Forum - OpenEdge Development - Progress Community

Dynamic queries with WHERE, BY and indexes

 Forum

Dynamic queries with WHERE, BY and indexes

This question is answered

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.

Verified Answer
  • abl_code_performance_redux.pdf
    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.
  • The Index Rulez.pdf

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

All Replies
  • abl_code_performance_redux.pdf
    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.
  • 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....

  • 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.

     

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

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

  • The Index Rulez.pdf

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

  • BY will be ignored when the field is part of an equality match. There’s some info in the doc I attached.
  • 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.TheIndexRules.zip