LQO

[gaby_h]

I am trying to learn more about LQO and it is still fuzzy in my head. In the help file there is just 1 page that talks about what you need to have before you can use LQO, but not not how to use it. I did a search and found this:

TableMax() can do the same thing, IF the filter expression can invoke LQO with an index,

What I don't get is: is LQO a process that A5 automatically starts when the conditions it needs are met or is it something that the user has to programatically or manually starts?
Referring to the above quote, how do you get the filter expression in Tablesum() to invoke LQO? I don't see an option in the syntax that says use LQO or don't use LQO.
Thanks in advance.

[Steve Workings]

Re: LQO

LQO works when a query expression matches an index expression. They need to be an exact match. And the help doc tells you what functions are best to use.

If your index expression is this:
alltrim(CustomerID)

then make sure your LQO expression is something like this:

alltrim(CustomerID) = YourKeyHere

That help?

[Stan Mathews]

Re: LQO

I am trying to learn more about LQO and it is still fuzzy in my head. In the help file there is just 1 page that talks about what you need to have before you can use LQO,

True

but not not how to use it.

Not so much.

Lightning Query Optimization is a feature that Alpha Five activates to increase the speed at which queries are run.

To take advantage of this time-saving feature, both the query you design and the pre-existing indexes must be compatible with Lightning Query Optimization.

how do you get the filter expression in Tablesum() to invoke LQO

The filter expression must take advantage of existing indexes and use the operators outlined in the documentation.

A query consists of a filter expression and/or an order expression. Any time you use a filter expression, Alpha runs a query.

[gaby_h]

Re: LQO

Yes it's more clear now,... just to make sure I understand this:
If I have an index by State, a query/filter using the expression State > "FL" would use LQO, whereas a filter expression like Amount > 100 won't?

Thanks guys.

[csda1]

Re: LQO

Hi Steve,

LQO works when a query expression matches an index expression. They need to be an exact match.

That's not quite correct Steve. It does not have to be a match, but needs to match the beginning field(s) of the index expression.

I have placed similar up to date info in my LQO tips area for future thread viewers.

LQO (Lightning Query Optimization) is a process that essentially happens automatically whenever you apply any filter or query to a table (except for the filter inside an index definition). It creates a dynamic range to an index. Back in Alpha Four, (and actually is still doable in Alpha Five), you might do a search (query) by setting a range that reduced the number of records that needed to be searched.

E.g. If you wanted only people whose last name began with P, your range for an index whose expression was LastName+FirstName would be "P" through "Pz". Alpha would only access those "P" records.

In order to be more generic than a range, LQO takes a part of the expression of the filter (e.g Between(LastName,"P","Pz) and looks to see if there is an index that uses the field of that part of the expression at the beginning of the index expression. It then tries to set a range that restricts the range of records that need to be accessed, and then applies the filter to only that record. So if your filter expression was

(FirstName="Ira") .and. Between(LastName,"P","Pz)

then ideally it would search the records only starting with "P".

Unfortunately, LQO isn't super intelligent. You need to help it a bit to find a matching index. It is best that the beginning part of the filter expression that you want to match to an index rearrange the filter terms to either start with a matching equality (or >= or <=) statement or use a Between() with constants for the 2nd and 3rd arguments, as in

Between(LastName,"P","Pz) .and. (FirstName="Ira")

or

LastName="Perlow" .and. FirstName="Ira"

So for a last name search, Alpha Five would be just as happy with index expressions of

LastName
LastName+FirstName
LastName+FirstName+Zipcode

It may not work with

trim(lastname)+trim(firstname)

LQO will not help (and could slow it down a bit) if the range of selected records is a large percentage of total records. This is not typical, but happens if you create filter and index expressions incorrectly.

When you get the LQO right, your speed of accessing records for large tables will increase a lot. That's the only way to really tell if LQO is happening (unfortunately it would be nice if Alpha had an LQO evaluator which would show you when it would actually happen)