LQO Limits

[spain246]

Has anyone noticed a limit to LQO? I'll run a date range and when I query a range that is just a copule of days LQO works really well. When I query a year it seems that LQO stops working.

I understand the obvious answer that there are more records over a year's worth of data vs. 2 days worth, but the same formula for LQO should work through both I'm fairly certain.

Anyone else seen this? Is there a 1 million record cap on this?

Steve Hall

[csda1]

Steve,

There is no practical limitation to the number of records, nor LQO's ability to work with them. If you are using a date, it sounds like you are LQO'ing with a date effectivelly ordered as if mmddyyyy rather than yyyymmdd.

Without knowing the particulars of the field type, index expression, and the query expression you want LQO to happen on, I can't tell you much more.

[spain246]

cdate with LQO

Hello Ira,
I've tried using the date conversion yyyymmdd and it would work, but then it would stop working. Can you do me a quick favor and look at my query? The query information is located under the "coin reports" button on my "coin reports" form. I am using temp table creation due to the sheer size of the data I am working with. (millions of records in my "cdr" table)

Thanks!

Steve Hall

[csda1]

Steve,

I see no current index and several queries in the script. There is also a subdirectory. Can you be more specific as to which query in the script etc.

[spain246]

Oops! Sorry Ira!
The subdirectory contains all of the table data etc. (My version of houscleaning). As far as queries, options 1 (All Anis), 2 (Single Ani), and 3 (Serial Number) are the 3 big ones (I can extrapolate options 4 & 5 from option 3 once I figure it out.) As far as a current index, where would I typically set and find that?

Steve Hall

[csda1]

Steve,

LQO requires an index. To create, go to control panel (or use XBasic comands), go to table tab, then right click on table and select "Define Indexes..." The query expression must start with a similar expression as the start of the index's expression for LQO to happen, otherwise it just filters it without LQO.

Thus if index expression is ID+LASTNAME+STATE, then the Query filter might be ID+LASTNAME+FIRSTNAME. Alpha looks at the fields in the query and finds an index expression (from potentially many defined indexes) that matches best the beginning of the Query. So if the query expression was the above, Alpha would find that the beginning ID+LASTNAME was the same for both, and apply LQO if the format of the query filter it understood. I have found Alpha 5 is most consistent using the BETWEEN() construct like this

"BETWEEN(ID+LASTNAME+FIRST,'"+varname_beg+"','"+varname_end+"')+"+additional criteria here"

It places a constant in the begin and end comparisons (surrounds the text by single quotes), rather than the name of the variable. Of course if a number type, then eliminate the single quotes, or if a date type, enclose in { } french braces instead of single quotes.

[spain246]

Ok,
So if my date index is : Offhookdat Filter: cdate(offhookdate)

Then my query should be:

query.filter = "between(Offhookdat,"{+begindate+}","{+enddate+}")+"

With or without the extra "+" it keeps telling me that I have extra characters.

Steve Hall

[csda1]

Ok,
So if my date index is : Offhookdat Filter: cdate(offhookdate)

Then my query should be:

query.filter = "between(Offhookdat,"{+begindate+}","{+enddate+}")+"

With or without the extra "+" it keeps telling me that I have extra characters.

Steve Hall

Since your index is a date field, the query filter must be a date type (the 1st parameter of between)

query.filter = "between(Offhookdat,{"+DTOC(begindate)+"},{"+DTOC(enddate)+"})"

resulting in a piece of text that will be evaluated as an expression. If you displayed query.filter, it will look something like

"between(Offhookdat,{02/01/2006},{03/05/2006})"

[spain246]

Ira,
Sorry for the delay. When I set my index to just "offhookdate" and query just a couple of days, (09/22/01-09/23/01) LQO works. When I try to query a year (09/22/01-09/21/02) it starts to rebuild the index. I've tried setting my date range using cdate(offhookdate) and just using it normal without the cdate. It runs the same both ways. There is obviously more data over a year than over 2 days, but it seems to rebuild the index when I query over a year. Any ideas?

Steve Hall