openedge - QUERY-PREPARE() taking too long to load. Is it the best option?

Question

Every it-codigo has 1 or more es-codigo. I'm trying to find all the es-codigo of the it-codigo input, but it's taking too long. Did I do anything wrong in my code? For what I have seen, it's all right, unless there's something I don't know about that I'm doing wrong. Is QUERY-PREPARE() the best option in this case?

    DEF VAR qEstrutura AS CHAR.

IF pi-cod-emitente <> 0 THEN DO:
    qEstrutura = " WHERE item-cli.cod-emitente = " + QUOTER(pi-cod-emitente).
END.

IF pc-it-codigo <> "" THEN DO:
    IF qEstrutura = "" THEN
        qEstrutura = " WHERE estrutura.it-codigo = " + QUOTER(pc-it-codigo).
    ELSE
        qEstrutura = qEstrutura + " AND estrutura.it-codigo = " + QUOTER(pc-it-codigo).
END.

IF pc-item-cli <> "" THEN DO:
    IF qEstrutura = "" THEN 
        qEstrutura = " WHERE item-cli.item-do-cli = " + QUOTER(pc-item-cli).
    ELSE
        qEstrutura = qEstrutura + " AND item-cli.item-do-cli = " + QUOTER(pc-item-cli).
END.


cQuery = cQuery + " FOR EACH item-cli, ".
cQuery = cQuery + " EACH estrutura ".
cQuery = cQuery + qEstrutura + " BREAK BY estrutura.es-codigo".


QUERY qConsulta:QUERY-PREPARE(cQuery).

QUERY qConsulta:QUERY-OPEN().

GET FIRST qConsulta.

DO WHILE AVAILABLE item-cli:

    IF QUERY qConsulta:FIRST-OF(1) THEN DO:
    
        CREATE tt-estrutura.
        ASSIGN
        tt-estrutura.it-codigo = estrutura.it-codigo
        tt-estrutura.es-codigo = estrutura.es-codigo
        .
        GET NEXT qConsulta.
    END.
END.

QUERY qConsulta:QUERY-CLOSE().


FOR EACH tt-estrutura:
    DISP tt-estrutura.
END.

question from:https://stackoverflow.com/questions/65829559/query-prepare-taking-too-long-to-load-is-it-the-best-option

Answer 1

I believe it's the QUERY-OPEN() that takes time. Not QUERY-PREPARE().

Your query is only performing selection (WHERE) and sort (BY) on the second table. That makes is difficult to utilize indizes. The OpenEdge ABL query engine does not support flipping the buffer-sequence. Try turning the query around:

FOR EACH estrutura WHERE ......, FIRST item-cli.