Question Open Query Or For Each Will Have Better Performance

[philippoommen1]

Hi
Please have a look into below section of code . Can anyone tell us which one have a better performance. or both have same performance?
-philip-

/* With open query : */

  CASE order_type:
    WHEN "SoNbr":U OR
    WHEN "AnyOrder":U THEN
    DO:
      OPEN QUERY iq_slshdr
        FOR EACH slshdr
          WHERE slshdr.so_nbr BEGINS pv_order_nbr
            AND slshdr.so_date <> ?
          NO-LOCK
          BY slshdr.so_date DESC.
      REPEAT:
        IF pv_i > pv_record_limit THEN
          LEAVE.
        GET NEXT iq_slshdr.
        IF NOT AVAILABLE slshdr THEN
          LEAVE.
        IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
          RUN create_order_data.
      END.
    END.
    WHEN "PoNbr":U THEN
    DO:
      OPEN QUERY iq_slshdr
      FOR EACH slshdr
       WHERE slshdr.cust_po_nbr BEGINS pv_order_nbr
          AND slshdr.so_date <> ?
          NO-LOCK
          BY slshdr.so_date DESC.
      REPEAT:
        IF pv_i > pv_record_limit THEN
          LEAVE.
        GET NEXT iq_slshdr.
        IF NOT AVAILABLE slshdr THEN
          LEAVE.
        IF LOOKUP(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
          RUN create_order_data.
      END.
    END.

/* With for each: */


  CASE order_type:
    WHEN "SoNbr":U OR
    WHEN "AnyOrder":U THEN
    DO:
      FOR EACH slshdr
        WHERE slshdr.so_nbr BEGINS pv_order_nbr
          AND slshdr.so_date <> ?
        NO-LOCK
        BY slshdr.so_date DESC:
        IF pv_i > pv_record_limit THEN
          LEAVE.
        IF NOT AVAILABLE slshdr THEN
          LEAVE.
        IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
          RUN create_order_data. /* procedure to create temp table for sales order data */
      END.
    END.
    WHEN "PoNbr":U THEN
    DO:
     FOR EACH slshdr
        WHERE slshdr.cust_po_nbr BEGINS pv_order_nbr
          AND slshdr.so_date <> ?
        NO-LOCK
        BY slshdr.so_date DESC:
        IF pv_i > pv_record_limit THEN
          LEAVE.
        IF NOT AVAILABLE slshdr THEN
          LEAVE.
        IF LOOKUP(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
          RUN create_order_data. /* procedure to create temp table for sales order data */
      END.
    END.

 

[Cringer]

Why not add some timings and see for yourself? In terms of actual records read it will be the same, so we're just looking at the timings of the queries.

Did you know you can do this? I think it's neater, plus if you have to do a NEXT in the iteration you don't have to worry about making sure you always get the next record:

      OPEN QUERY iq_slshdr
        FOR EACH slshdr
          WHERE slshdr.so_nbr BEGINS pv_order_nbr
            AND slshdr.so_date <> ?
          NO-LOCK
          BY slshdr.so_date DESC.
      DO WHILE QUERY iq_slshdr:get-next:
        IF pv_i > pv_record_limit THEN
          LEAVE.
        IF NOT AVAILABLE slshdr THEN
          LEAVE.
        IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
          RUN create_order_data.
      END.