如何使进度4gl的数据库快速访问？

Question

我必须使数据库访问快速，其中包含大数据的唯一索引mstr_nbr，如何使它快速。在其中，获取第一个mstr获取0ms和获取下一步mstr占用大部分时间0ms，但有时使用1ms意味着在180000中每次mstr运行12000次运行需要1ms，这将增加时间，如果12000 ms秒时没有串行循环增加，这是一个生成的网页，我如何使它成为fast...anyone帮助？

            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.

mstr表索引

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

表系列索引：

 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和serial_c11都是字符数据类型。

表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

考虑到我们所知道的，这就是我将如何编码它：

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.

使用临时表可以避免在每次“串行”表的迭代中重新获取所有“关闭”和“关闭”记录。

可能缺少了一些上下文，但是滚动查询和行的重新定位似乎毫无意义。

选择“串行”记录看起来不太有效，但我需要知道哪些索引可用，以及serial_pallet、serial_key和serial_c11中的数据是什么样子的。如果只有几个离散的值，那么可能有更好的方法来编写它。

在每个字段( serial_pallet、serial_f_chr11和serial_key )上都有单个组件索引。所以那里没有多少帮助。

这是猜测，但是，如果( serial_f_chr11中的离散值的数量很小)，那么使用一系列相等的匹配和OR可能会更好。假设有效值是方框、JAR、袋子和松散值。在这种情况下，而不是：

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

你可以写：

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 )

如果在serial_pallet + serial_f_chr11上有一个复合索引，那就更好了。

如果离散有效serial_f_chr11值的数量更大，或者如果可能添加新值，则最好将它们添加到临时表中，然后将它们连接到临时表中。

另一种选择是，不要循环遍历所有主记录以查找匹配的详细信息，而是首先选择与序列化匹配的详细信息。然后找到适当的主记录。这就消除了一整层的循环。但这取决于"det“表中的适当索引。您必须有一个以serial_parent和detlot作为主导组件的索引。

如果mstr_nbr与"nbr“字段不同(如索引清单所示)，则需要构建TT并在mstr_nbr上添加索引。但是，如果nbr实际上与mstr_nbr相同，那么您可以跳过TT，直接高效地查询数据库表。