1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question Open Query Or For Each Will Have Better Performance

Discussion in 'Development' started by philippoommen1, May 18, 2017 at 6:24 AM.

  1. 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-

    Code (progress):
    1. /* With open query : */
    2.  
    3.   CASE order_type:
    4.     WHEN "SoNbr":U OR
    5.     WHEN "AnyOrder":U THEN
    6.     DO:
    7.       OPEN QUERY iq_slshdr
    8.         FOR EACH slshdr
    9.           WHERE slshdr.so_nbr BEGINS pv_order_nbr
    10.             AND slshdr.so_date <> ?
    11.           NO-LOCK
    12.           BY slshdr.so_date DESC.
    13.       REPEAT:
    14.         IF pv_i > pv_record_limit THEN
    15.           LEAVE.
    16.         GET NEXT iq_slshdr.
    17.         IF NOT AVAILABLE slshdr THEN
    18.           LEAVE.
    19.         IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
    20.           RUN create_order_data.
    21.       END.
    22.     END.
    23.     WHEN "PoNbr":U THEN
    24.     DO:
    25.       OPEN QUERY iq_slshdr
    26.       FOR EACH slshdr
    27.        WHERE slshdr.cust_po_nbr BEGINS pv_order_nbr
    28.           AND slshdr.so_date <> ?
    29.           NO-LOCK
    30.           BY slshdr.so_date DESC.
    31.       REPEAT:
    32.         IF pv_i > pv_record_limit THEN
    33.           LEAVE.
    34.         GET NEXT iq_slshdr.
    35.         IF NOT AVAILABLE slshdr THEN
    36.           LEAVE.
    37.         IF LOOKUP(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
    38.           RUN create_order_data.
    39.       END.
    40.     END.
    41.  
    42. /* With for each: */
    43.  
    44.  
    45.   CASE order_type:
    46.     WHEN "SoNbr":U OR
    47.     WHEN "AnyOrder":U THEN
    48.     DO:
    49.       FOR EACH slshdr
    50.         WHERE slshdr.so_nbr BEGINS pv_order_nbr
    51.           AND slshdr.so_date <> ?
    52.         NO-LOCK
    53.         BY slshdr.so_date DESC:
    54.         IF pv_i > pv_record_limit THEN
    55.           LEAVE.
    56.         IF NOT AVAILABLE slshdr THEN
    57.           LEAVE.
    58.         IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
    59.           RUN create_order_data. /* procedure to create temp table for sales order data */
    60.       END.
    61.     END.
    62.     WHEN "PoNbr":U THEN
    63.     DO:
    64.      FOR EACH slshdr
    65.         WHERE slshdr.cust_po_nbr BEGINS pv_order_nbr
    66.           AND slshdr.so_date <> ?
    67.         NO-LOCK
    68.         BY slshdr.so_date DESC:
    69.         IF pv_i > pv_record_limit THEN
    70.           LEAVE.
    71.         IF NOT AVAILABLE slshdr THEN
    72.           LEAVE.
    73.         IF LOOKUP(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
    74.           RUN create_order_data. /* procedure to create temp table for sales order data */
    75.       END.
    76.     END.
    77.  
    78.  
    79.  
     
  2.  
  3. Cringer

    Cringer ProgressTalk.com Moderator Staff Member

    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:

    Code (progress):
    1.  
    2.       OPEN QUERY iq_slshdr
    3.         FOR EACH slshdr
    4.           WHERE slshdr.so_nbr BEGINS pv_order_nbr
    5.             AND slshdr.so_date <> ?
    6.           NO-LOCK
    7.           BY slshdr.so_date DESC.
    8.       DO WHILE QUERY iq_slshdr:get-next:
    9.         IF pv_i > pv_record_limit THEN
    10.           LEAVE.
    11.         IF NOT AVAILABLE slshdr THEN
    12.           LEAVE.
    13.         IF lookup(slshdr.entered_by_facility_id, pv_auth_facl, {&d1}) > 0 THEN
    14.           RUN create_order_data.
    15.       END.
    16.  
     

Share This Page