LQO speed - Is this compatable?

[Mattyau]

I have the following filter expression in a field rule's lookup section.
Supplier = var->vsuppliername
I have also tried the following
between(supplier,var->vsuppliername,var-vsuppliername)
Do these expression method utilize LQO? I seem to have speed problems with the lookup suggesting that LQO is not being utilized - i.e. In the status bar (bottom of screen) it is counting through the 20000+ records.

When I use a "static" expression in the field rule's lookup section, the lookup is pretty much instantaneous with no counting and noticeably much quicker than the above methods.
i.e. Supplier = "Restek"

In the "lookup" table I have defined an Index called Supplier and have Supplier in the order expression and included ALL

So my question is, referring to the top two expressions, are my filter expressions compatable to utilize LQO or have I mis-read the information regarding LQO and lost "the plot" trying to get it to work using a "variable" rather then a "static" text

Any help/clarification on the "LQO and variables method" would be much appreciated

[whanigsberg]

RE: LQO speed - Is this compatable?

Matthew,

I have been working with this very issue lately. It probably has nothing to do with LQO.

I think the problem is that the variable is being evaluated for each record which can take time if you have enough records. That the process runs much faster with a string literal in the filter is consistent with this explanation.

One way to manage this is to use replace_parameters() which does what it sounds like it does: evaluates the variable and substitutes the outcome for the variable; but it only happens once.

So if your filter is
Supplier = var-"vsuppliername
you could substitute
Supplier = replace_parameters(vSuppliername,local_variables())

I know this has a big impact on the speed of the process. However, I do *not* know whether the built in field rule will accept this approach. But it is worth a try.

Please report back. I would like to know how the field rule works out. My efforts have been aimed at Xdialog boxes but the issue was the same: slow with a variable but fast with a literal in the filter.

Bill

[Finian Lennon]

RE: LQO speed - Is this compatable?

For LQO, the format of your query must exactly match the format of the index. One way to ensure that this is the case is to define your index (if character, 16 for example) as padr(alltrim(fieldname),16). You then need to ensure that every query sumitted to the index is also padded in the same way. I always use the Between() syntax.

Finian

[CALocklin]

RE: LQO speed - Is this compatable?

Finian,

Any particular reason for not leaving the index alone and just making the filter:
replace_parameters(padr(vSuppliername,16," "),local_variables())
where "16" is the size of the field as defined in the table? (And, of course, the field is a character field.)

Cal Locklin
www.aimsdc.net

[Mattyau]

RE: LQO speed - Is this compatable?

The "field rule approach" did not like this method at all. It was painstakingly slow compared to what I have already.

[Mattyau]

RE: LQO speed - Is this compatable?

I tried the padr() approach as you mentioned above, however performance was marginal if not a fraction slower. This time however in the status bar it counted the total number of records in the table as well as the progress % status.

When I used my original expression
"b"Supplier = var-"vsuppliername"/b"
it was only counting the number of filtered records. Maybe i need to look at filtering the lookup table so that it displays less record??

[Mattyau]

RE: LQO speed - Is this compatable?

I tried the padr() expression, while leaving the index alone. It didn't seem to make any difference. For field rule expressions I probably suggest not to use replace_parameters() as a general rule for lookups as it made the process time incredibly slow.

[JohnZaleski]

RE: LQO speed - Is this compatable?

LQO is being invoked - but Alpha is making a query out of the records that satisfy the filter expression, the more records that satisfy the query, the slower it is.
If I remember correctly, it would be almost instantaneous if the filter expression was based on the linking fields - I believe Alpha then uses a range and doesn't need to create a query.

[whanigsberg]

RE: LQO speed - Is this compatable?

The above interventions explain why I am replacing my more problematic and oft-used field rule lookups with hand-scripted xdialogs.

In a network situation with medium-large tables I haven't been able to get the field rule lookups with variables in the expressions to work fast enough.

This doesn't mean there isn't a way--just that I don't know it.

Bill