Hi,

I am using a TwwDBLookupCombo and the lookup table (and all other
tables) is on a file server. It works fine but one of the tables has
25,000 records and the auto complete function is rather slow.

OK so I thought when the form opens, I should copy all the 25k records
to a local table/memory table and that will speed things up.

I would like opions of which is more suitable - a disk based table or
a memory table?

I will be using this particular type of lookup combo on about 4
different forms, so I guess I make a local table/memory table (of the
25k records) and every time I open one of the 4 forms, I just copy
from this local table into another local lookup table?

Is there a better way to accomplish this?

thanks,
Pat

Pat


A memory table is great IF it doesn't change, or at least doesn't change much. If it does then you'll probably find that the check to see it its changed and the moving of the data will slow things down even worse.

Also if its the same table you're using in each form just have one version in memory - after all DBISAM treats it like a disk table.

Roy Lambert

Roy,

>...  you'll probably find that the check to see it its changed and the moving of the data will slow things down even worse.
I thought to let the user do a manual refresh if the record he is
expecting is not found in the dropdown. Maybe the trade-off between
'speed' and 'record not found' is OK

>Also if its the same table you're using in each form just have one version in memory
I thought if the TwwDBLookupCombo on one form was using the table and
then another form's TwwDBLookupCombo (based on the same table) changed
its selected record, it would change the selected record on the first
form somehow. Are you saying mutiple TwwDBLookupCombos on different
forms can share the same LookUp table at the same time without
interference?

thanks Roy,
Pat

Pat


>I thought if the TwwDBLookupCombo on one form was using the table and
>then another form's TwwDBLookupCombo (based on the same table) changed
>its selected record, it would change the selected record on the first
>form somehow. Are you saying mutiple TwwDBLookupCombos on different
>forms can share the same LookUp table at the same time without
>interference?

I don't use those components, but if it works with a disk table it'll work with a memory table. At present do you have four copies of this lookup table?

Roy Lambert

Roy,

>> ...  Are you saying mutiple TwwDBLookupCombos on different
>>forms can share the same LookUp table at the same time without
>>interference?
>
>I don't use those components, but if it works with a disk table it'll work with a memory table. At present do you have four copies of this lookup table?

No not at present. Each of my four forms has a TDBISAMTable (v3.27) on
it as the LookUp control (pointing to the table on the file server).

So thinking ahead, I thought when my application starts up, it loads
the 25k records into a memory table (call it tblMainLookUp). This
table would be used as the LookUp for the combos on the four forms.
So, for each combo LookUpTable property I would have
frmMyMainForm.tblMainLookUp

I am not sure though if say on Form1 combo I select record #1 and then
on Form2 combo I select record #2, if Form1 combo will change to
record #2 by itself.

In other words, since the LookUp table is on the main form, if I
select a combo record on one form, does this change the selected combo
record on the other form (since they share the same memory LookUp
table). Maybe something to do with pointers or bookmarks, I don't
know.

I could set it up and test it, but I wanted to 'test the water' here
first   

Pat

Pat


>No not at present. Each of my four forms has a TDBISAMTable (v3.27) on
>it as the LookUp control (pointing to the table on the file server).

Great. The main point to remember is that an in-memory table is THE SAME AS an on-disk table as far as DBISAM is concerned.

>So thinking ahead, I thought when my application starts up, it loads
>the 25k records into a memory table (call it tblMainLookUp). This
>table would be used as the LookUp for the combos on the four forms.
>So, for each combo LookUpTable property I would have
>frmMyMainForm.tblMainLookUp

That sound right. I'd prefer to use a datamodule for sharing the table unless you have a reason for referencing the mainform in the other forms.

>I am not sure though if say on Form1 combo I select record #1 and then
>on Form2 combo I select record #2, if Form1 combo will change to
>record #2 by itself.

If it doesn't do it for an on-disk table it won't for an in-memory one.

Roy Lambert

Pat <pat@downunder.com> wrote in
news:n2p6o39ot6j5i6u705uhp49q5v8gslm0i6@4ax.com:

>>> ...  Are you saying mutiple TwwDBLookupCombos on different
>>>forms can share the same LookUp table at the same time without
>>>interference?

Pat,

 You're mixing up database tables and TDBISAMTables - you fill one
database memory table with your copy of the real table, and then you add as
many TDBISAMTable componenets to your Form or DataModule as you need, one
for each Combo, all looking at the same memory table. It's the
TDBISAMTables that have separate cursors and thus prevent interference.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

As you have found out, using a Lookup table on a WAN or even a LAN is
not going to give optimal performance. Most developers load lookup
tables at application startup.  What I did was create a base class
lookup datamodule that keeps a reference count which is incremented
each time a form needs to use that lookkup table and decremted when the
callng form is destroyed.  I inherit this base class for each lookup
table needed. If the lookup lists will be small, it loads them and
keeps them in loaded for the duration of the application.  If the
lookup list is large, then the logic keeps the table open as long as
there is a reference to it.  I use Clientdatasets to hold cache the
lookkup data locally.   If a maintenance form updates something in a
lookup list it will automatically refresh the table making the updated
list available immediatly to all calling forms. And yes, I do use the
wwDBLookupCombo componet which works with any TDataset based component
such as the ClientDataSet or Dbisam table components.

--
David Farrell-Garcia
Whidbey Island Software, LLC

David

>If a maintenance form updates something in a
>lookup list it will automatically refresh the table making the updated
>list available immediatly to all calling forms.

This bit interests me - how do you do it?

Roy Lambert

'I am using a TwwDBLookupCombo and the lookup table (and all other
tables) is on a file server. It works fine but one of the tables has
25,000 records and the auto complete function is rather slow.'

This surprises me a bit Pat.  We use Woll2Woll components extensively and as
long as you have an index on the item that is being auto-completed, I have
found it works extremely quickly.  Have you checked that the table is
indexed on the field being displayed in the drop-down?

Alex