Speeding up query

[Kladkul]

Morning all,

I'm rewriting a screen and the browse in it is based on data pulled from several tables. The way I've got it now, the user selects data from a few selection boxes (multi-selection available) and based on the data selected the query is ran:

/* The variables contain the selected data from selection boxes 
    Example: 
var1 = "test,data,for,looking,up". 
var2 = "more,test,info,for,seeking". */
 
FOR EACH tbl1 NO-LOCK WHERE 
     LOOKUP(tbl1.fld1,var1) > 0, 
EACH tbl2 NO-LOCK WHERE 
     LOOKUP(tbl2,fld1,var2) > 0 AND 
     tbl2.fld2 = tbl1.fld2: 
 
/* ... code here ... */

Is there a quicker alternative to using the LOOKUP function for this? Maybe I should be loading the selected data differently?

 

[RealHeavyDude]

You say nothing about your Progress version - I am just guessing it's at least 9.1e.

Whenever you use a function like you do in a query there is no way that an index bracket can be used and therefore it will cause a table scan.

The only way you can avoid that is to use ORs for each possiblity when using a static query (or FOR EACH in your case).

If the list of possibilities is variable, determined at run time, I would us a dynamic query for which you build the query predicate dynamically at run time. But dynamic objects are only available starting with V9.

Regards, RealHeavyDude.

 

[LarryD]

As RHD stated, there is no way as written to prevent a table scan.

An alternative, ONLY provided that tbl1.fld1 is an index and tbl2.fld2 is an index (or either is a primary part of an index):

def var i as int no-undo.
def temp-table tt no-undo
    field tbl1-recid as recid
    field tbl2-recid as recid.

repeat i = 1 to num-entries(var1,","):

    FOR EACH tbl1 NO-LOCK WHERE tbl1.fld1 = entry(i,var1,","), 
        EACH tbl2 NO-LOCK 
               WHERE tbl2.fld2 = tbl1.fld2
               and LOOKUP(tbl2.fld1,var2) > 0:
        create tt.
        assign tt.tbl1-recid = recid(tbl1)
               tt.tbl2-recid = recid(tbl2).
     end.
end. 

for each tt,
    each tbl1 no-lock where recid(tbl1) = tt.tbl1-recid,
    each tbl2 no-lock where recid(tbl2) = tt.tbl2-recid:
 
    /* ... code here ... */
end.

I didn't include sorting, but you could add the fields necessary for sorting in the temp table and set an index = the fields for sorting.

 

[Kladkul]

Yea, I knew I would need to rewrite it because the function causes a whole-index.

I like the example you have above but the second lookup causes an issue doesn't it?

repeat i = 1 to num-entries(var1,","):

    FOR EACH tbl1 NO-LOCK WHERE tbl1.fld1 = entry(i,var1,","): 
 
    do x = 1 to num-entries(var2): 
        for EACH tbl2 NO-LOCK 
               WHERE tbl2.fld2 = tbl1.fld2
               and tbl2.fld1 = entry(x,var2):

        create tt.
        assign tt.tbl1-recid = recid(tbl1)
               tt.tbl2-recid = recid(tbl2).
     end.
end.

 

[LarryD]

Not knowing what your specific key structure for tbl2 means I don't really know if it would or wouldn't.

Your example should work also, especially if tbl2.fld2 and tbl2.fld1 are a single key or tbl2 only has a key with tbl2.fld1.

FWIW, I had just copied what you did in your example with the "assumption" that tbl2.fld2 was the key in tbl2.

 

[tamhas]

Depending on the number of selected values and whether or not the corresponding field in the table is indexed, the fastest approach might be N separate searches, one on each selected value, thus avoiding a table scan, and build a temp-table with the results.