Resolved Case faster than a if in a browse widget open-query

Hi,

OS: Windows 2012 r2
Openedge 10.02B

I want to optimize my browse widget open-query trigger.

My case is has followed. I make an app to follow and valid some reception of orders thats was made by our users.
So my user with this app should see there ordre and can valid the reception of thus.
I have two steps of filters:
1. the status of the orders: open, closef, all the order. (my first "If") (variable iCdStat -> 1,2,3)
2. some filter for the by of my query: by order date (acddat), by order Id (acdnum) or by user (dpeutidem) (variable iCdTri -> 1,2,3) :

this is my code:
Code:
IF iCdStat = 3 THEN DO: /* All Orders */

    IF iCdTri = 1 THEN /* by Date */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat   >= dDatDeb
                                                AND   SACEBA.acddat   <= dDatFin    NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acddat DESCENDING.
    ELSE IF iCdtri = 2 THEN /* By order Id */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat   >= dDatDeb
                                                AND   SACEBA.acddat   <= dDatFin    NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acdnum .
    ELSE /* By User */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                            AND   SACEBA.acddat   >= dDatDeb
                                            AND   SACEBA.acddat   <= dDatFin
                                            AND   SACEBA.acdetat   = lEtaCde NO-LOCK USE-INDEX SACEBAI04,
                               FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                            AND   SACDPE.acddemnum = SACEBA.acddemnum
                                            AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                               FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                            BY SACDPE.dpeutidem .
END.
ELSE IF iCdStat = 2 THEN DO: /* Order Closed */
    IF iCdTri = 1 THEN /* By Date */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat   >= dDatDeb
                                                AND   SACEBA.acddat   <= dDatFin
                                                AND   SACEBA.acdetat   = lEtaCde NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acddat DESCENDING.
    ELSE IF iCdTri = 2 THEN /* by Order Id */
         OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat   >= dDatDeb
                                                AND   SACEBA.acddat   <= dDatFin
                                                AND   SACEBA.acdetat   = lEtaCde NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acdnum .
    ELSE /* By User */
         OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat   >= dDatDeb
                                                AND   SACEBA.acddat   <= dDatFin
                                                AND   SACEBA.acdetat   = lEtaCde NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACDPE.dpeutidem .
END.
ELSE DO: /* Order open */
    IF iCdTri = 1 THEN /* By Date */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat    = SACEBA.acddat
                                                AND   SACEBA.acdetat   = lEtaCde   NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acddat DESCENDING.
    ELSE IF iCdTri = 2 THEN /* by Order Id */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat    = SACEBA.acddat
                                                AND   SACEBA.acdetat   = lEtaCde   NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACEBA.acdnum .
    ELSE /* By User */
        OPEN QUERY {&SELF-NAME} FOR EACH SACEBA WHERE SACEBA.stecod    = c-stecod
                                                AND   SACEBA.acddat    = SACEBA.acddat
                                                AND   SACEBA.acdetat   = lEtaCde   NO-LOCK USE-INDEX SACEBAI04,
                                   FIRST SACDPE WHERE SACDPE.stecod    = c-stecod
                                                AND   SACDPE.acddemnum = SACEBA.acddemnum
                                                AND   SACDPE.dpeutidem = (IF lAdmin THEN SACDPE.dpeutidem ELSE c-uticod) NO-LOCK USE-INDEX SACDPEI02,
                                   FIRST VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK
                                                                BY SACDPE.dpeutidem .

END.

So my question is: Is the "case" structure more suitable in words of efficiency or the "if" structure.

Of course I'm open to all over observation about my code. I'm willing to progress .


Thank you in advance and Best regards
 

TomBascom

Curmudgeon
Yes, CASE, is faster. Probably not in any significant way though. It is not like you have it embedded in the heart of a code block where it accounts for a notable proportion of total execution (the OPEN QUERY etc will dwarf it...)

The better reason to use CASE is that it is cleaner and much more readable.
 

Cringer

ProgressTalk.com Moderator
Staff member
You seem to be using USE-INDEX quite a lot in your code. This is generally considered to be bad practise. It is much better to structure your query to ensure the correct index is used where possible. If your indexes change, or you add a more suitable index in future you will have to refactor all the code that has USE-INDEX in order to improve the performance.
Also, I would consider building a dynamic query and attaching it to the browse dynamically. You have a lot of repeated logic here that will be hard to maintain in future. With a dynamic query you can build the query string in a more reusable fashion. This will reduce the points of maintenance.
 

TomBascom

Curmudgeon
If USE-INDEX is being included to force a sort order it would be much better to simply specify the sort order with a BY clause.

The "FIRST" gunk and (IF ... THEN ... ELSE ...) functions in the WHERE clauses are also "worst practice" candidates.

Given that the original question was about performance I (very) strongly suspect that this application has performance problems. IMHO the source of those problems is much more likely to be found in the way that these queries are being put together.
 
Hi,

Maybe it's a bad practice. I was told by the people who work with me on progress that you if use a field order corresponding to other index than your primary indexes you need to specify it in the code.

So if I understant what your saying. I just have to wrote the corerct order of field in my query and Progress will use the right index by himself? If it's that, do I need to write every field of the index I wanna used or just the first I have data for it.

Example of index in my case:
Code:
SACEBAI04 :
stecod             /* wrote */
acddat          /* wrote */
acdnumenr       /* don't wrote */

Thank you again for your advice :)
 

Cringer

ProgressTalk.com Moderator
Staff member
Compile with xref is your friend for static queries. The xref will tell you what index is used by each query.
Whoever told you that about indexes is mistaken. Progress is usually much better than you at selecting the best index. So let it. Using USE-INDEX is something that should be the exception not the norm.
 

TomBascom

Curmudgeon
The 4gl compiler uses rule based query optimization. When you specify an index with USE-INDEX you stop the optimizer and force one particular index to be used. As a result:

ONLY that one index can be used. The potential for multiple-indexes to be used to resolve the query is immediately lost.

If that index is not a good match for selecting data the query will perform very poorly -- many more records than necessary will need to be sent to the client for selection.

Using USE-INDEX to force a sort-order is almost certainly because the the sort order is a poor match for the WHERE clause. A BY clause will fix that without killing performance. "Selection, then sorting" means that it is much better to sort the records that you actually need then it is to sort a larger set of records and then select the ones that you want to keep.

Putting USE-INDEX in your code is almost always a mistake. Like anything else there are occasional exceptions. But they are very, very rare and deserve to be very extensively documented with detailed test cases so that future programmers can verify that your reasoning for needing it is still applicable. I cannot recall that I have *ever* found a case where USE-INDEX was necessary in my own code.
 
I see know how to use it.
Before that in my mind, with what I have learned, I was thinking that when you need to use another index than the primary, you need to write down with the USE-INDEX phrase now I understand it's not and it's a bad habit. The Xref file help a lot, even though I don't master is use case. But it's a greet help.

I thank you a lot for your help. You have a great place in my understanding of progress code :)

Thank you.
 
Top