fast way to query data?

[ken tjia]

I am not sure if this is the best way, but I have used an array for some of my routine to check for existance of record, in such away, i have to use "index" and filter at the same time. My databse can be as big as 50,000 records.
it is getting very slow to perform basic query.

I use the following:
DIM inputp as p
DIM NRECS AS N

INPUTP=table.open("REQUEST_HEADER",file_ro_shared)
INPUTP.INDEX_PRIMARY_PUT("repair_job")

query.description="GET LIST OF ALL REPAIR JOB"
query.order="REQ_HEADER"
Query.filter="REP_BY_ID=ALLTRIM(VAR-"CIDD)"
' HERE I SPECIFICALLY WANT RECORD BELONG TO EQUAL TO VAR-"CIDD, USUALLY RESULTING TO AOUT 10 TO 20 RECORDS, OUT OF THE 50,000
query.options="M" 'force alpha to generate a new query list

ndx=inputp.query_create()
nrecs=ndx.records_get()

DIM CAT_ARRAY(NRECS) AS C
DIM I AS N
DIM J AS N

J=NRECS

INPUTP.CLOSE()

SELECT
CASE J"0
GOTO AA
CASE J=0
GOTO NO_ACTION
END
CASE J"0
GOTO NO_ACTION
END

END SELECT
END

AA:
BROWSE1.DISABLE()
HIDE_BANNER.PUSH()
sys_send_keys("{F5}")
range.flags = RANGE_INDEX .or. RANGE_FILTER .or. RANGE_DESCENDING
range.index_handle = index_handle_get(db_current(), "REPAIR_JOB")
range.filter = "REP_BY_ID = ALLTRIM(VAR-"CIDD) " '.AND. REQ_STATUS = 'ASSIGN'"
range_add(db_current(), " ", " ")

SLEEP(1/2)
sys_send_keys("{F5}")
BANNER.TEXT = ALLTRIM(STR(J))+" RECORDS FOUND"

SHOW_BANNER.PUSH()
CHECK.PUSH()

END

NO_ACTION:
SHOW_BANNER.PUSH()
CHECK.PUSH()
END

ALL ABOVE WORK VERY WELL, EXCEPT I SUSPECT THE ARRAY ROUTINE TEND TO BE SLOW, AS IT GOES THROUGH THE ENTIRE 50,000 AND THEN THE RANGE INSIDE THE INDEX DOES THIS AGAIN.

THE REAL REASON TO MAKING SURE THAT THE FILTER RECORD EXIST, BEFORE ATTEMPTING TO PERFORM THE RANGE INDEX FILTER,
ELSE THIS WOULD SLOW DOWN DO DO FUNNY THING TO MY APPLICATION, IN SUCH AWAY THAT EMPTY RECORD FORCE ALPHA TO SEARCH FOREVER???

YOUR ASSISTATNCE IS GREATLY APPRECIATED

KEN JIA

[Tom Cone Jr]

RE: fast way to query data?

Ken,

I've read your script several times and still don't understand your question.

The array you declare is never populated so it's hard for me to see that it's slowing things down.

The script runs two separate queries. I'm not sure why.

If you simply need to know if a particular record is present you can check using the exist() function instead of running a query against the entire table simply to find out if the number of records in the query list is greater than zero. Maybe exist() will speed things up for you.

-- tom

p.s. Unless you're visually impaired, using all cap letters in your messages is harder to read, and is often interpreted as SHOUTING.

[martinwcole]

RE: fast way to query data?

It appears that your browse is the same table you are querying. If so:

t=table.get("request_header")
query.filter="rep_by_id=var-"cidd"
query.options=""
query.order="req_header"
idx=t.query_create()
if idx.records_get() " 0
browse1.fetch_first()
else
idx.drop()
ui_msg_box("Sorry","No records found.")
end
end if

[ken tjia]

RE: fast way to query data?

Martin,

Thank you for your reply, the idea of using "Range" and "Index" in place of the query that i often use.
Is to achieve a speedy result.

Such performance had given me excellent result.
However, I dicovered situation, when "no record" actually found within the range/index run. The following events usually disaster and seems like going forever to find it.
Thus brought me the idea to first check for existance of such records before attempting to perform range index.

I use "array" a great deal, it is slow, but never failed on me and always get me, versus others seems to have leakage, as in the multi user case, when new record added in real time by other user do not seems to get included, while the array was happy to included new records for me.

So, I was stupid enough to use array and in hope that it works everytime, but it gets really slow for sure

[martinwcole]

RE: fast way to query data?

Kenn, if you have the field indexed that you are searching on (basing the query on,) the code I gave you is as fast as it gets - it uses LQO - Lightening Query Optimization. If there are no records, it tells you so.

[ken tjia]

RE: fast way to query data?

Hi Martin.

Thanks you for the reply, just have the chance to tackle these "speed" requirment.

Such codes work well, but suppooses that the "filter"
is far more complecated, such as:

******************************************
query.filter="rep_by_id=var-"cidd .and. req_type "" 'rx' .and. req_type "" 'wo' "

*******************************************



t=table.get("request_header")
query.filter="rep_by_id=var-"cidd .and. req_type "" 'rx' .and. req_type "" 'wo' "
query.options=""
query.order="req_header"
idx=t.query_create()
if idx.records_get() " 0
browse1.fetch_first()
else
idx.drop()
ui_msg_box("Sorry","No records found.")
end
end if

Question One:

If i would like to take advantage of the Ligthning speed query, do I need to create an INDEX that exactly matching the query filter? This one really confused me.

Question Two:

If this is the ultimate speed we can get out, then in the future when the file grow toward 80,000 records. i am going to have a problem with longer delay.
Currently I am on 33,000 records and it is acceptable,
but in fear of future slow processing.

Is there a way to insert "Index" routine for example right from the beginning say,We have an index on "rep_by_id" and just fetch for "rep_by_id = var-"cidd"

Then apply Query after indexing to look for Req_type = "Wo" etc.

I appreciate your comment on my questions above,.

Thanks

Ken Tjia

[martinwcole]

RE: fast way to query data?

I will admit that I only vaguely understand the complexities of lightening query optimization - but as I understand it, if you have an index on each of the fields that are included in the query that it will use LQO.

but one thing is for sure, querying a table that is already open and part of your form's set will be quicker than the approach you were taking.

I would also suggest that you search the documentation and the phorum for info on LQO.

But remember that speed is also an aspect of your LAN, how many users are on, etc.

good luck

[martinwcole]

RE: fast way to query data?

if you have an index on the field, as I understand it, the query will use the index (which is what LQO is, as I understand it,) so there would be no speed advantage using an index.

[Tom Cone Jr]

RE: fast way to query data?

I would create an index on the rep_by_id field being careful to define it the same way you pass your variable to the query filter expression. -- tom

[ken tjia]

RE: fast way to query data?

HI Tom,

Are you suggesting, to include all those filter(s) into the actual index, I have done some in this fashion.
But do not know, if "lightning" actually striking hi.

I truly do not know what is being considered "Lightning Query" versus regular. I have a feeling I am already reach the ultimate and is in lighting.

Bill suggested that I try using " in between", I also believe as you described, just create an index sorting for
"Rep_by_id" and then hopefully the filter will pick the rest up and piggy back on the lightning based on this single index.

I am wondering if I apply "Range/Index" isn't this suppossed to be faster the lightning? I have done few test, but ran into trouble when there is no record on file, the fetching or finding record just keep going and going in some cases.

Ken Tjia

[martinwcole]

RE: fast way to query data?

"pre"
Lightning Query Optimization

Lightning Query Optimization is a feature that Alpha Five activates to increase the speed at which queries are run. By using information contained in the indexes you have created, Alpha Five can dramatically speed up queries.

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. The following sections explain this compatibility.

Building Compatible Indexes

For Lightning Query Optimization to use a pre-existing index, the index must:

Use at least one of the same fields used in the query.

The data type of the data in the index key must match the data type of the field you are searching.

Use an order expression that contains either a single field, or two fields concatenated using the plus operator (+), such as in the expression Lname+Fname.

Not use the Unique Only option

Not use a filter expression

Building Compatible Queries

For a Query to work with Lightning Query Optimization, at least one of the condition expressions in the query must compare a field used in one of your indexes with a constant. For example, State = "MA", is suitable, provided you have a pre-existing index which sorts records using the State field.

The condition expression must use operators that are compatible with Lightning Query Optimization. These include:

Equals (=)

Exactly equals (==)

Greater than or equal to ("=)

Greater than (")

Less than or equal to ("=)

Less than (")

Not equal to ("")

BETWEEN(..)

SMATCH() - see note below

Wildcards (* and ?) - see note below

.NOT.

.AND.

.OR.

Note: The SMATCH() function and the wildcards * and ? can only be used with Lightning Query Optimization if they appear at the end of the expression. For example, a search for McC* can be optimized since the wildcard operator appears at the end, while a search for *stein cannot be optimized, since the wildcard operator begins at the beginning.

Partial Compatibility

In some cases, only part of your query�s selection expression can be used with Lightning Query Optimization. In this case, Alpha Five applies the optimization algorithm to the compatible part, and uses Alpha Five�s standard query algorithm to evaluate the rest.

For example, you have a table of customers for which you have created an index that sorts records by the field City. You then create a query which uses the following selection expression:

(CITY = "BOSTON").AND.("Commonwealth" $ ADDRESS1)


Because the second half of the expression uses an operator that is not compatible with Lightning Query Optimization and a field which is not used in a pre-existing index (ADDRESS1), Alpha Five applies Lightning Query Optimization to only the first part of the expression.

Furthermore, in cases where the selection expression evaluates to TRUE for most of the records in the table or set, Alpha Five will analyze the query and might determine that the standard query algorithm is more suitable. In this case, Lightning Query Optimization is not used.

See Also

SMATCH(), BETWEEN()

"/pre"