[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Newbie questions about dynamic query preparation in ABL

[Patrice Perrot]

Hi I think there is a workaround to avoid multiple Query-prepare. Times came from a breakout session did at Noordwick in october 2016. Elapse time for a "query-prepare" : 0.000990 sec Elapse time for a "workaround" : 0.000044 sec Explanation With Multiple Query Prepare : the-ShipTo = "ShipTo" . CREATE BUFFER bh-ShipTo FOR TABLE the-ShipTo NO-ERROR . the-Invoice = "Invoice". CREATE BUFFER bh-Invoice FOR TABLE the-Invoice NO-ERROR . the-State = "State" . CREATE BUFFER bh-State FOR TABLE the-State NO-ERROR . EACH LOOP will be : CREATE QUERY hQry. hQry:SET-BUFFERS(bh-ShipTo, bh-State). qprepare = "FOR EACH ShipTo WHERE ShipTo.CustNum = " + string( I-custnum) + " NO-LOCK , FIRST State WHERE State.State = ShipTo.State NO-LOCK " . logbid = hQry:QUERY-PREPARE(qprepare) NO-ERROR. IF logbid AND hQry:QUERY-OPEN THEN hQry:GET-FIRST(NO-LOCK). DO WHILE NOT hQry:QUERY-OFF-END : .... The Workaround : You prepare one time your query the-ShipTo = "ShipTo" . CREATE BUFFER bh-ShipTo FOR TABLE the-ShipTo NO-ERROR . the-Invoice = "Invoice". CREATE BUFFER bh-Invoice FOR TABLE the-Invoice NO-ERROR . the-State = "State" . CREATE BUFFER bh-State FOR TABLE the-State NO-ERROR . CREATE QUERY hQry. hQry:SET-BUFFERS(BUFFER ttparameter:HANDLE, bh-ShipTo, bh-State). qprepare = "FOR Each ttparameter no-lock , EACH ShipTo WHERE ShipTo.CustNum = ttparameter.ParamInt1 NO-LOCK , FIRST State WHERE State.State = ShipTo.State NO-LOCK " . logbid = hQry:QUERY-PREPARE(qprepare) . The loop will be : (one update of the record of the temp-table, no more Query-prepare) FIND FIRST ttparameter NO-LOCK NO-ERROR . IF NOT AVAIL ttparameter THEN DO : CREATE ttparameter . END. ASSIGN ttparameter.ParamInt1 = I-custnum . VALIDATE ttparameter. IF hQry:QUERY-OPEN THEN hQry:GET-FIRST(NO-LOCK). DO WHILE NOT hQry:QUERY-OFF-END :

Continue reading...