How can i make database access fast of progress 4gl

Question

i have to make a database access fast which contains large data of unique index mstr_nbr how can i make it fast. in which get first mstr taking 0ms and get next mstr takes most of the time 0ms but sometimes takes 1ms means in 180000 for each mstr runs 12000 runs takes 1 ms which is increasing the time and if no of serial loop increases every time 12000ms second also increase this is a webspeed generated webpage how can i make it fast...anyone help

            DEFINE QUERY Mstr FOR mstr scrolling.
            OPEN QUERY Mstr FOR EACH mstr no-lock 
                        where (Mstr_status = "close" or Mstr_status = "closed").
            FOR EACH serial
                WHERE (serial_pallet = f_pallet AND serial_f_chr11 <> "BOX") 
                       or (serial_key begins f_pallet)
                    NO-LOCK    BREAK BY serial_pallet by serial_parent by serial__chr11 QUERY-TUNING(LOOKAHEAD CACHE-SIZE 32768 DEBUG EXTENDED):
                GET FIRST Mstr.
                define variable roID as rowid no-undo.
                roID = rowid(mstr).
                DO WHILE NOT QUERY-OFF-END('Mstr'):
                    for each det fields(detnbr detmodel detlot detqty) no-lock
                    where (detnbr = mstr_nbr) and (detmodel = serial_parent and detlot = serial__chr11):
                        tinspected = tinspected + detqty.
                    end. /* for each */
                    GET NEXT Mstr.
                END.
                reposition mstr to rowid roID.
            end.

index for mstr table

index-name     field-name

badgenew       1 badgenew      Active    
datenew        1 datenew       Active    
nbridx         1 nbr           Unique Primary Active                   
pallet         1 pallet        Active    
proddesc       1 proddesc      Active    
prodline       1 prodline      Active    
status         1 status        Active    
type           1 type          Active

indexes for table serial:

 actual_prod_day    1 dte04         2 serial_chr01 Active
 actual_prod_line   1 serial_pallet 2 serial_dte04 3 serial_chr01 4 serial_line Active
 pallet_prod        1 serial_pallet 2 serial_dte04 Active
 pallet_prod_line   1 serial_pallet 2 serial_dte04 3 serial_line Active
 
 serial_chr01       1 serial_chr01 Active
 serial_chr05       1 serial_chr05 Active
 serial_chr06       1 serial_chr06 Active
 serial_chr11       1 serial_chr11 Active
 serial_chr14       1 serial_chr14 Active
 serial_dte04       1 serial_dte04 Active
 serial_int01       1 serial_int01 Active
 
 serial_line        1 serial_line Active
 
 serial_pallet      1 serial_pallet Active
 
 serial_parent      1 serial_parent Active
 
 serial_serial__key 1 serial_serial__key 2 serial_parent Unique Primary Active

serial_pallet serial_key and serial_c11 all are character data type

indexes for table det:

detidx      1   detnbr 2    detpallet 3 detprodline 4   detbox 5    detlot 6 detshift Unique Primary Active  
detlot      1   detlot Active    
detmodel    1   detmodel Active  
detnbr      1   detnbr Active    
detpallet   1   detpallet Active     
detprodline 1   detprodline Active

Answer 1

Given what we know this is how I would code it:

define temp-table tt_mstr
  field mstr_nbr as integer                /* or whatever the proper data type is */
  index mstr_nbr-idx is primary unique     /* I am assuming mstr_nbr is unique    */
.

for each mstr no-lock
   where mstr.mstr_status = "close"
      or mstr.mstr_status = "closed":

  create tt_mstr.
  tt_mstr.mstr_nbr = mstr.mstr_nbr.  
      
end.

for each serial no-lock
   where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )       /* <> "box" is going to perform poorly, there may be better ways to do this     */
      or ( serial_key begins f_pallet ):

         /* break by serial_pallet by serial_parent by serial__chr11: ** this sort of pointless, you're just adding up "tinspected", the order and the break groups have no impact */  
  
  for each det fields( detnbr detmodel detlot detqty ) no-lock
     where detmodel = serial_parent and detlot = serial__chr11:

    find tt_mstr where tt_mstr.mstr_nbr = detnbr no-error.       
      if available tt_mstr then
        tinspected = tinspected + detqty.               
    
  end.
  
end.

Using a temp-table avoids refetching all of the "close" and "closed" records with every iteration of the "serial" table.

Maybe there is some context missing but the scrolling query and repositioning of the row seem pointless.

The selection of "serial" records does not look very efficient but I would need to know what indexes are available and what the data in serial_pallet serial_key and serial_c11 looks like. If there are just a few discrete values there may be better ways to write that.

You have single component indexes on each of the fields serial_pallet, serial_f_chr11, and serial_key. So there is not much help there.

This is speculation but if the number of discrete values in serial_f_chr11 is small you would probably be better off with a series of equality matches and OR. Suppose the valid values are BOX, JAR, BAG, and LOOSE. In that case, instead of:

where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )
   or ( serial_key begins f_pallet )

you could write:

where ( serial_pallet = f_pallet and serial_f_chr11 = "jar" )
   or ( serial_pallet = f_pallet and serial_f_chr11 = "bag" )
   or ( serial_pallet = f_pallet and serial_f_chr11 = "loose" )
   or ( serial_key begins f_pallet )

That would be even better if you have a composite index on serial_pallet + serial_f_chr11.

If the number of discrete valid serial_f_chr11 values is larger or if new values might get added then it would be better to add them to a temp-table and join on that instead.

Another option is that rather than loop over all of the master records to find matching details, select details that match the serials first. Then find the appropriate master record. That eliminates a whole layer of looping. But it depends on proper indexes in the "det" table. You must have an index that has serial_parent and detlot as leading components.

If mstr_nbr is not the same field as "nbr" (as shown in your index listing) then you need to build the TT and add an index on mstr_nbr. If, however, nbr is actually the same as mstr_nbr then you could skip the TT and directly query the database table efficiently.