Add new compiler option to output index information on all static queries - OpenEdge 11.6 Community Input - Products Enhancements - Progress Community

 OpenEdge 11.6 Community Input

Add new compiler option to output index information on all static queries

  • Complete

There should be a new compiler option that outputs index information on ALL static queries.

Xref doesn't really satisfy this because it only shows query information for "cross-references".

This means that for example if you have a Temp-Table in a class and have a method in this class with a query in it, no index-information will be put in the xref file, the same thing happens in a *.p file where the temp-table and the query are not in an internal procedure.

A new compile option that outputs all possible query information would be hugely useful for all developers to find possible performance leaks.

Status update

Delivered in OpenEdge 11.6, October 2015.

Comments
  • Agreed... index bracketing information from a new XREF option would be extremely helpful in finding and preventing performance problems. Knowing that the query uses one or more indexes is somewhat helpful... knowing how well it uses those indexes (like an explain plan) would be extremely valuable.

  • the output should be a parsable format (for example xml)

  • The OpenEdge Engeeering team has asked for feedback on their investigation into this enhancement request. Please review and comment if this would meet your needs, and/or if there are other aspects of this that you would like Progress to address.

    ---------

    Currently, when you COMPILE with XREF, any queries (e.g. FOR EACH) against a database table or shared temp-table will display a SEARCH entry in the output XML, displaying information on the index the compiler selected. This is specifically turned off for local (non-shared) temp-tables, as they cannot be referenced from other ABL programs, and therefore technically do not belong in a file containing cross-reference information.

    However, the SEARCH entry can provide useful information to ABL developers. Knowing the selected index can help developers identify performance problems if an incorrect index or index bracket is specified.

    It is possible to get this information at runtime through the QRYINFO logentrytype, but this requires the ABL developer to execute the code in order to get this information. It would be more advantageous to get this information at compile time. This information is already provided in the XREF output for database tables and shared temp-tables. Tools that parse these files could potentially be extended to use the same information for local temp-tables.

    The enhancement is that we will write the SEARCH XREF entry for local temp-tables if the ABL developer requests it. The mechanism by which the ABL developer can request this is TBD.

    The SEARCH information will be written to both the XREF and XREFXML output. For the purposes of brevity in this discussion, XREF refers to both XREF and XREFXML.

    For example, a query against a customer database table yields the following XREF excerpt:

    cust.p cust.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE cust.p cust.p 1 SEARCH sports2000.Customer CustNum WHOLE-INDEX cust.p cust.p 2 ACCESS sports2000.Customer CustNum cust.p cust.p 2 ACCESS sports2000.Customer Name

    Of interest here is the SEARCH entry, indicating the query will use the CustNum index, and will search the whole index.

    For a SHARED temp-table, similar output is shown:

    ttgcust.p ttgcust.p 1 STRING "tt1" 3 NONE UNTRANSLATABLE ttgcust.p ttgcust.p 1 STRING "custnum" 7 NONE UNTRANSLATABLE ttgcust.p ttgcust.p 1 STRING "custname" 8 NONE UNTRANSLATABLE ttgcust.p ttgcust.p 1 NEW-SHR-TEMPTABLE tt1 ttgcust.p ttgcust.p 6 SEARCH tt1 ixnum TEMPTABLE WHOLE-INDEX

    Again, we can see the name of the index used for the query and whether the whole index is used.

    The same SEARCH output will be available for the local temp-table:

    ttcust.p ttcust.p 1 STRING "tt1" 3 NONE UNTRANSLATABLE ttcust.p ttcust.p 1 STRING "custnum" 7 NONE UNTRANSLATABLE ttcust.p ttcust.p 1 STRING "custname" 8 NONE UNTRANSLATABLE ttcust.p ttcust.p 6 SEARCH tt1 ixnum TEMPTABLE WHOLE-INDEX

  • Thanks Rob, as far as I'm concerned this is spot on for my requirements.

  • I agree with James. This meets my needs.

  • Thank you Rob, this fits my needs

    If possible the format of the "new entries" in the xref should be exactly like the current entries so existing analysis tools can be used without any updates or changes

  • The enhancement sounds good. With this change, I'd also suggest abandoning the notion / restriction that "xref" is only for inter-program entities and expand it to cover other things that are local to a given program.

  • @Tim isn't that exactly what's being suggested?

  • This would be a good first start.

    Having the bracketing information for both temp-tables and DB tables in the XREF would be invaluable in tracking down performance issues.

    Just as an example suppose you have an index defined with order-date,state,country and where clause is:

    order-date >= 1/1/2013 and

    order-date

  • This thread has been very helpful in clarifying the specific request of providing existing XREF output for local temp-tables.

    In both Tim Kuehn’s and TheMadDBA’s post, there is an additional request for the ABL to provide additional non-cross reference information.  Can you both expand on your comments and identify what specific additional information you would like the Compiler to include in the XREF output?  

    Thanks

    Evan Bleicher

    Engineering Manager

  • Evan: I would like to have something similar to an explain plan. Meaning the bracketing information is output after each SEARCH entry so you can tell how effectively the index is being used.

    Assuming we have an index with 3 columns (a,b,c)... I would like the XREF output to be able to tell me the difference between the following where clauses (and other permutations)

    a = 1 and b = 1 and c = 1     (3 clean equality brackets)

    a = 1 and b <> 1 and c = 1  (1 clean equality bracket, 2 brute force)

    a >= 1 and b = 1 and c = 1  (1 clean range bracket, 2 brute force)

    a = 1 and string(b) = "1"      (1 clean equality bracket and 2 brute force)

    a = 1 and c = 1                     (1 clean equality bracket, 1 skipped column and 1 brute force)

    a = 1 (1 clean range bracket, 2 not used)

    Right now we just have WHOLE-INDEX and "normal". Not every WHOLE-INDEX is bad and not every "normal" entry is good. We need something to help us find problem code without looking at every single line of code.

    When you have a very large code base that has evolved over the years this kind of information is extremely useful in finding problem code.

    We can tell from _TableStat and _IndexStat which tables/indexes are causing the most reads. Compiling the code base with XREF lets us identify which programs use those indexes but then we are stuck manually looking at hundreds of programs to find the few that actually have these kind of issues.

    With this kind of addition to the XREF output we could filter out all of the good/expected results and quickly key in on the sections with performance issues. We could also use this information to enhance the peer review process to prevent new issues making their way into production.

    Thanks

  • Thank you for your feedback concerning extending the XREF index information. As this particular forum is restrictive, I plan to post to a more open forum, requesting input from others on this idea as well, to hopefully arrive at a proposal that assists the majority.

    I cannot commit to completing this work in a particular release.