Progressive Lookup

[Bill Parker]

Using Jim Chapman's approach in the June newsletter for a progressive lookup, keylist_build is too slow for the 10K record table that I must use for a progressive lookup display - say a couple seconds for each key press.

A standard table lookup field rule would work if I can figure out how to get the lookup value on return from the lookup.

Lookup is from a child browse. Entering a new record and lookup is initiated by a OnRowDoubleClick, so on return from the lookup (myform.OnActivate) :Browse1:myfield.value has the value of whatever row was double clicked, not the row that is being added.

The new row is added correctly. Focus looks like it is on the new row. I just have not figured out how to address it to get the value returned by the lookup. All form and table addresses I have tried give the last row double clicked, not the added row.

Bill.

[Jim Chapman]

RE: Progressive Lookup

Hello Bill,

Yes, it is quite slow when dealing with a large table or over a network connection. I have had several correspondances on this issue, and a couple of weeks ago Al Buchholz brought this up again, and I decided to try to find a faster approach and re-visit this issue. Al and I went back and forth on this issue. This turns out to be a little stickier of a problem that I origionally thought. You can use a regular form and the onKey() event, as Peter Wayne has described on his site www.learn alpha.com, to dynamically filter and display a progressive lookup. This is quite fast if you use a pre-defined index.

On the Xdialog front, I am not aware of any way to dynamically/progressively determine the filter of the keyList_build() function without another query. We can however load the entire key list of an index into memory. This is quite fast, and I have made some progress on the Xdialog approach. On an old 1 gig system, slow HD, 768 megs Ram, I can load 24,000 records into memory in less than a second, then I can progressively filter these in a Xdialog box. However, that still doesn't answer the real problem. What happens when the table is 100,000 records, 1 million, over a slow network, etc.

There is one piece to this puzzle that would make the routines I have been playing with much quicker, and that is the ability to scroll an Xdialog list box 'to the top'. I can easily progressively scroll an Xdialog list box, but the current record selection is at the bottom of the list. To make it scroll (a misnomer as it doesn't really scroll) to the top I have to re-filter the list every time the user enters a character, hence replacing the current list rather than scrolling through the existing list.

Any thougths?

Jim

[Peter.Wayne]

RE: Progressive Lookup

jim,
what i have done in an analogous situation is to read in no than than 20 records at a time -- the number i want to show in a listbox. this is very fast, no matter how many records you have, as long as the table is properly indexed. i think it would be fairly straightforward to adopt it to a progressive lookup, but the keylist_build() would have to be abandoned.
- peter

[JerryBrightbill]

RE: Progressive Lookup

Jim

I have wrestled with this issue also as I use a keylist_build for my calendar addin. Other that using an index with the same expression as the keylist expression, I have found no way to speed up building the list. The only "solution" is to make the expression as simple as possible - one or two fields and no trimming of fields. This helps somewhat, but not much. A change made a while ago by Alpha did increase speed and stability, but it still can be slow with big files.

Jerry

[Jim Chapman]

RE: Progressive Lookup

Hello Peter,

Thanks for the thoughts. I have been trying this approach, although I have been reading in 100 records at a time. The issue that bothers me about this approach is that the user could choose to scroll after a few keystroke entries and hit the end of the subset of records.

I suppose one could 'train' the users to always enter keystrokes, but in a situation where the user is not totally sure of the spelling, scrolling usually becomes the perferred approach.

If there was a way to know where the current 'scroll' in the Xdialog list box was I might be able to anticipate this.

I have also been toying with the idea of beginning the process with a subset of records, say the first 100, and at the same time start a seperate thread that continues on reading the entire table (limited to a couple of fields per record) into memory. The problem with this is that if the table is large enough.......not good ...... so the script itself might make a decision, if the record count is 100,000 continue to do just subsets of records.............. don't like this either.........

Thoughts?

Thanks,
Jim

[Jim Chapman]

RE: Progressive Lookup

Hello Jerry,

Thanks for the thoughts. I have come down to three potential approaches:

1) Using the indexPointer.key_list_get() function. This is quite fast and allows me to just get a subset and define a beginning point for the subset. But the question remains, how fast is fast enough for a large table or over a network.

2) Using table.external_record_content_get() which is pretty fast and easy to use.

3) Just opening a table in Xbasic and do queries on it via a predefined index, then using the result to populate the list box. Most flexible but ends up being a ton of queries (even with LQO) against an open table.

Thoughts?

Thanks,
Jim

[JerryBrightbill]

RE: Progressive Lookup

Jim

I would suspect that any find that uses an index or subset of an index(indexPointer.key_list_get()) will be the fastest method. Any method that uses a query will be somewhat slower, although LQO may reduce the difference. I haven't run any tests on large tables, but I wonder if using
table.external_record_content_get() and then populating a list will be any faster then keylist_build() that populates the list directly. They both appear to use similar query syntax, so finding the records should take about the same time.

Any fast method relies on having an index in place, which restricts the usage a bit. If you don't already have an index to fit the search, then either you have to create a number of extra indexes, which has it's own issues, or you have to take the speed hit of a query. Maybe all of this will become moot when V6 comes out and we can use a SQL structure that only uses queries.

Jerry

[csda1]

RE: Progressive Lookup

Jim & Bill,

Peter's suggestion is actually the only practical suggestion that works in a networked environment.

When you do a lookup, get the 1st n entries, and place the data on the display. As a user types keys, fetch the n entries based upon the new keys. Trap the scroll up/scroll down/Page Up/Page Down/Home/End etc, and get any additional record data you would need either just before or after those currently displayed.

It may behoove you to grab a few extra entries just before and after the displayed range, as the response may be more acceptable when you scroll if you already have adjacent records (or +/- pages of n entries) already in memory. I would scroll to the new display of the record, then fetch additional entries while the user views the newly displayed data.

If you can invoke a separate thread to get the data around the current record as you receive keystrokes, you may be able to get the needed records as fast as required, even on a slow machine, by fetching the number of records based upon the current queue of keystrokes, and bypassing intermediate display refreshes if you get behind in data fetching.

I would not, however, get more records than are needed for fast response. This adds additional load to the network and data sharing, and may slow down all users.

Regards,

Ira

[russ Boehle]

RE: Progressive Lookup

This is an interesting thread, although I admit most of it is beyond my capabilities. However it would seem that a range might do the trick, you could progressively vary the range so it changes the displayed records, and the results should be quick since it uses an existing index. Won't work in SQL but might for now.


Russ

[JerryBrightbill]

RE: Progressive Lookup

Russ

To use a range, you still need an index. While ranges are still supported, the trend is toward queries. LQO queries are typically almost as fast as a range, since they use an existing index.

Jerry