Quicker Drop Down List Box

[Jamin Dunivan]

I have a listbox that is populated by a table with 4174 records in it. It takes about 20 seconds for the listbox to be populated. The listbox is a variable and needs to remain that way. The only other idea is to make a custom lookup form that diplays the data, but I would like to pursue this avenue first.

My code:

dim mac as p
dim idx as p
dim count as n
dim i as n

mac=table.open("roy_device_type")
idx=mac.index_tag_add("Device_Name","device_type_name")
count=idx.records_get()+1
device_type.choice.list.redim(count)
mac.fetch_first()
device_type.choice.list[1].text=""
for i=2 to count
device_type.choice.list[i].text=mac.device_type_name
device_type.choice.list[i].value=mac.device_type_name
mac.fetch_next()
next
mac.close()

Thanks
Jamin

[martinwcole]

RE: Quicker Drop Down List Box

with that many items, I would make a dialog form with an embedded browse, and use Dr. Wayne's script to isolate records as the user enter characters on the keyboard.

[martinwcole]

RE: Quicker Drop Down List Box

Also - I would not use the word "count" as a variable - it is a reserved word, and using reserved words as variables will often create problems - also, if you only want to populate the embedded browse with "some" of the records from the table - you can create a query.

[Jamin Dunivan]

RE: Quicker Drop Down List Box

I didnt want to do that. It looks better the other way but it looks like I will have to give in.

Thanks
Jamin

[Tom Cone Jr]

RE: Quicker Drop Down List Box

Jamin,

I don't disagree with Martin's idea, however two other thoughts occur to me:

1) you might want to use table.batch_begin() and batch_end() to see how that affects the speed at which you fetch through the table.

2) you might want to preload the array while the user is waiting for some other process to be finished... for example, it is theoretically possible to declare the array as global and populate it while the app is first opening. The user might tolerate a single delay better than having to wait each time they branch to this particular module.

3) why do you use tbl.index_tag_add() instead of tbl.index_primary_put() ? I should think 'adding' a new index tag to your existing index file is going to be pretty slow (relatively speaking).

-- tom

[Jamin Dunivan]

RE: Quicker Drop Down List Box

Thanks for the reply tom

1) I thought batch_begin and batch_end were only for entering and changing records. I will give it a try.

2)That is basically what this does

3)Why I like tbl.index_tag_add() by Jamin Dunivan
It will not add a new index every time. It only adds it if it does not exist.

from the manual on index_tag_add():

"Add a new index tag, or finds an existing index tag, in the production index file
for the table referenced by the object pointer, . When called, this function
first searches the index file to see if this index already exists. If an index with
identical specifications (i.e., the same Order Expression, Filter Expression, and
Index Type) is found, .index_tag_add() returns an object pointer
() to this index."

[Jamin Dunivan]

RE: Quicker Drop Down List Box

2 second difference using batch. from 36 to 34

[JohnZaleski]

RE: Quicker Drop Down List Box

I think Tom is correct ( what a surprise ). If you know the way you are going to access the table and it appears you do, just open the table with that index and do your thing.

Easy to test: In define indexes create an index tag named "typename" indexed on device_type_name.
Instead of:
idx=mac.index_tag_add("Device_Name","device_type_name")
Use:
idx = mac.index_primary_put("typname")
Then see if it speeds up the process.
JOhn

[Jamin Dunivan]

RE: Quicker Drop Down List Box

Hi John

Went from 34 seconds to 35 seconds. I like index_tag_add() because it finds the index first if it exists like index_primary_put(). If there is no match it adds the index. I use this for a safety feature. If for some reason an index is involuntarily dropped. This will recreate it if it has to.

thanks
Jamin

[JohnZaleski]

RE: Quicker Drop Down List Box

Jamin,

I didn't see that the index_tag_add looked for that tag in the production index first before adding. I'm curious what happens if it doen't find the tag and tries to add it in a multi-user environment with others using the table.

Just a thought here. If you run and time just the following, is it a signifigant amount of time:

mac=table.open("roy_device_type")
idx=mac.index_tag_add("Device_Name","device_type_name")
'rem start the timing
count=idx.records_get()+1
'rem end the timing

If it is significant, is the file extremely volatile? Could you get the file size in a global variable upon starting the app, add a number to it, and redim the variable to that?
Then in the code do a while .not. mac.fetch_eof() to populate the variable.

John

[Jamin Dunivan]

RE: Quicker Drop Down List Box

John

I believe the indexes are used locally in your shadow table and not at the main app.

I tried your suggestion and reached 34 seconds. I think I am just going to have to make a custom lookup form.

Thanks for trying.
Jamin