hello,
my application uses lookups excessively.
the application structure is as follows:
form with master/detail grid combination. the mastergrid contains the lookups. when double clicking in the grid the edit form opens using the same record (exactly same tdataset instance) as the master grid.
the lookuptables can have several thousands of rows.

i experienced the following: when doing the lookup into a real table the list (with the lookups) opens fast but opening of the edit form is much slower. i use a view instead (with even more records since it contains record history too). this slows the opening of the list but is nearly instant showing the edit form.
i guess this has something to do with row locking ... is there a best of both worlds?

indexes should be well set.
thank you

Hi Gruetzmacher,

we had also performance problems with lookups having thousands of entries, in our case matchcodes from customers within the edit-form of orders. We have solved this by creating autocomplete - lookups for cases, where we have a lot values in lookups.

this has speeded up things ... we had this in the past with advantagedatabase server ... possible that elevate does things different, but i think, loading only data which is needed is the better way than filling the complete lookup with data ...

Yusuf Zorlu
MicrotronX

Hello,

Not knowing what happens when you open the edit form I can't tell what slows it down.
What happens when that form is created/opened?

--
Fernando Dias
[Team Elevate]

Hendrik,

As Fernando indicated, we need more information in order to diagnose such an issue.  Are you using a query to populate your lookup form, or is just a table ?

But, none of this has anything to do with row locking.

Tim Young
Elevate Software
www.elevatesoft.com

thank you for your replies,
i probably have to dig deeper to give more details.
however: the edit form is based on a table. there are lookup fields displayed.
when the lookupfields are based on a table the form opens slowly, based on a view (which has the same structure but more records as the table) it opens immediately

Hendrik,

<< when the lookupfields are based on a table the form opens slowly, based on a view (which has the same structure but more records as the table) it opens immediately >>

This means that the view has an index available that the table does not for optimizing the lookups.  Lookups work just like Locate in that they will happily keep working even when they are resorting to scanning every row in the table in order to satisfy the Locate.

If you want to post your view SQL along with the CREATE TABLE statement for the table, I can tell you if that is the case in this situation.

Tim Young
Elevate Software
www.elevatesoft.com

thank you tim,
i happily accept the offer

5 tables and their corresponding views

Hendrik,

A few things:

1) Don't post SQL as comma-separated text - it's really hard to read with all of the inserted double-quotes and I can't use the SQL as-is for testing out what you're describing.

2) Which table are you performing the lookups on ?

3) What is the code that you're using for the lookup, or how are the lookup fields defined ?

4) Unrelated, but I would strongly advise against using views that are simply UNION ALL statements because the performance is going to be atrocious.  You're effectively constantly merging two tables, which won't scale very well as the number of rows in the tables increases.

Tim Young
Elevate Software
www.elevatesoft.com