Thanks Tom!
My colleagues and I do not think that the code is the bottleneck.
Well, the code can of course be improved a bit, which we did. But it has not added much.
I enclose an simple example of the difference between SQL and Progress Question.
SQL is 16 times faster than Progress.
(We have not run profiler on any program. Yet
)
Code:
//the table to be filled with the result of SQL-query
DEF TEMP-TABLE tt_item_price
FIELD ITEM_price_obj LIKE ITEM_price.ITEM_price_obj
FIELD agreement_obj LIKE agreement.agreement_obj
FIELD ITEM_obj LIKE ITEM.ITEM_obj.
DEFINE VARIABLE ii AS INTEGER NO-UNDO.
DEFINE VARIABLE cSQL AS CHARACTER NO-UNDO.
DEFINE VARIABLE ttHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE dd AS DATETIME NO-UNDO.
ttHandle = TEMP-TABLE tt_item_price:HANDLE.
//SQL-query, similar to progress query
csql =
"select item_price_obj, agreement.agreement_obj, item.item_obj
from item_price
join item on item.item_obj = item_price.item_obj
join agreement ON agreement.agreement_obj = ITEM_price.agreement_obj
where item_price.item_price_fromdate = '2021-04-01 00:00:00.000'
and item_price.item_price_status_code <> '9'
and item.item_status_code <> '9'
and agreement.agreement_status_code <> '9' "
.
//runs the SQL-query
ETIME(YES).
DO TRANSACTION:
RUN STORED-PROC ccmsql.send-sql-statement LOAD-RESULT-INTO ttHandle (cSQL) .
END.
//loops the result of the query
FOR EACH tt_item_price NO-LOCK.
ii = ii + 1.
END.
MESSAGE ii STRING(INT(ETIME / 1000),"HH:MM:SS") VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
//ii = 143.382, time = 2 seconds
dd = datetime(DATE("20210401")).
ii = 0.
ETIME(YES).
FOR EACH agreement FIELDS(agreement_obj) WHERE agreement.agreement_status_code <> '9' NO-LOCK,
EACH ITEM_price FIELDS (item_price_obj item_obj)
WHERE item_price.agreement_obj = agreement.agreement_obj AND
item_price.item_price_fromdate = dd AND
item_price.item_price_status_code <> '9' NO-LOCK,
FIRST ITEM FIELDS (item_obj )WHERE item.item_obj = item_price.item_obj AND
item.item_status_code <> '9' NO-LOCK.
ii = ii + 1.
END.
MESSAGE ii STRING(INT(ETIME / 1000),"HH:MM:SS") VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
//ii = 143.382, time = 37 seconds
//agreement has 10889 records in the database
//item_price has 19103933 records in the database
//item_price has 1213806 records in the database
//index in the tables agreement, item_price, item_obj
/*
Table: agreement
Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
agreement_change_date_i N/A 1 + agreement_change_dat
agreement_id_idx N/A 1 + agreement_id
agreement_locked_shelf_ N/A 1 + agreement_locked_she
agreement_lock_status_i N/A 1 + agreement_lock_statu
agreement_performance_i N/A 2 + agreement_valid_from
+ agreement_valid_to
agreement_quarantine_id N/A 1 + agreement_quarantine
agreement_send_date_idx N/A 1 + agreement_send_date
agreement_template_flag N/A 1 + agreement_template_f
agreement_template_obj_ N/A 1 + agreement_template_o
branch_obj_idx N/A 1 + branch_obj
ccm_idx N/A 1 + agreement_ccm_flg
export_trygg_flg N/A 1 + export_trygg_flg
pu pidx N/A 1 + agreement_obj
u PK_agreement_obj N/A 1 + agreement_obj
resale_idx N/A 2 + agreement_resale_flg
+ agreement_resale_ven
skapatisql_parent1 N/A 2 + agreement_parent1_ob
+ agreement_status_cod
skapatisql_parent2 N/A 2 + agreement_parent2_ob
+ agreement_status_cod
status_code_idx N/A 1 + agreement_status_cod
Table: item_price
Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
agreement_comp_idx N/A 6 + agreement_obj
+ item_obj
+ item_price_bracket
+ item_price_delivery_
- item_price_fromdate
+ item_price_status_co
agreement_idx N/A 1 + agreement_obj
fromdate_desc_idx N/A 1 - item_price_fromdate
item_idx N/A 1 + item_obj
item_price_change_date_ N/A 1 + item_price_change_da
item_price_net_flg_idx N/A 1 + item_price_net_flg
item_price_parent_idx N/A 1 + item_price_parent_ob
pu pidx N/A 1 + item_price_obj
status_code_idx N/A 1 + item_price_status_co
Table: item
Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
brand_idx N/A 1 + brand_obj
expire_idx N/A 1 + item_expiering
item_id_idx N/A 1 + item_id
item_market_code_idx N/A 1 + item_market_code
item_name2_idx N/A 1 + item_name2
item_name_idx N/A 1 + item_name
item_replaced_by_idx N/A 1 + item_replaced_by
item_subclass_idx N/A 1 + item_subclass_obj
item_type_idx N/A 1 + item_type
pu pidx N/A 1 + item_obj
u PK_item_obj N/A 1 + item_obj
skapatisql_item_status_ N/A 2 + item_obj
+ item_status_code
status_code_idx N/A 1 + item_status_code
vat_code_idx N/A 1 + item_vat_code
vendor_idx N/A 1 + vendor_obj
*/