[Stackoverflow] [Progress OpenEdge ABL] How to SELECT a single record in table X with the largest value for X.a WHERE values for fields X.b & X.c are

ryyker

Guest

May 16, 2022

I am using a query to obtain the current component serial number (tr_sim_sn) installed on the host device (tr_host_sn) from the most recent record in a transaction history table (P.tr_hist), The actual table has ~190 million records. the excerpt below contains fields to illustrate the query below:

Code:

tr_sim_sn      |tr_host_sn* |tr_host_pn     |tr_domain  |tr_trnsactn_nbr |tr_qty_loc
_______________|____________|_______________|___________|________________|___________
...            |               
356136072015140|99524135    |6684112-000    |vattal_us  |178415271       |-1.0000000000
356136072015458|99524136    |6684112-001    |vattal_us  |178424418       |-1.0000000000
356136072015458|99524136    |6684112-001    |vattal_us  |178628048       |1.0000000000
356136072015050|99524136    |6684112-001    |vattal_us  |178628051       |-1.0000000000
356136072015836|99524137    |6684112-005    |vattal_us  |178645337       |-1.0000000000
...

* key field

The excerpt illustrates multiple occurrences of tr_trnsactn_nbr for a single value of tr_host_sn. The largest value for tr_trnsactn_nbr corresponds to the current tr_sim_sn installed within tr_host_sn.

I am using the following query to obtain tr_trnsactn_nbr.

Code:

SELECT tr_sim_sn FROM PP.tr_hist   
WHERE tr_trnsactn_nbr = (SELECT max(tr_trnsactn_nbr) 
                 FROM P.tr_hist 
                 WHERE tr_domain = 'vattel_us' 
                 AND tr_lot = '99524136' 
                 AND tr_part = '6684112-001')

This query works, but it is very slow, ~8minutes. (P.tr_hist has over 900M records)

I would appreciate suggestions to improve or refactor this query to improve its speed.

Continue reading...