Performance Problem with simple name lookup

[Mike Carver]

I am having a performance problem when I use the following command and when there is a lot of records in the "Contacts" Table:

X.dbDisplayLookUpList="(;|)Cid||SELECT Cid, FirstName & ' ' & Lastname as Name FROM Contacts"

The GRID has less the 20 records.

Response Time:

Sub-second if I do not use the X.dbDisplayLookUpList
Sub-Second if Contacts has is < 10 records
5-6 Seconds if I have 913 records in Contacts

Any ideas on how to speed this up. What if I have 3000 records in Contacts?

I have a PII 350mhz with 128kb of RAM. If I look at the Performance monitor while hitting the REFRESH (Reload) button it hit 100% for the 5 seconds mentioned above. &#34;Cid&#34; is the Primary Key with NO duplicates.

Thanks

Mike

[Mike Carver]

Maybe I should be doing a JOIN. I am not familiar with it. The application is this:

1) I currently have 913 Contact Records that have fields like Company,FirstName,LastName,Phone..... (I hope to have a lot more Contact Records if Business grows.)

2) I have several applications where I want to have a Detail file that has the ContactID in each record but would not like to duplicate all the detail from the Contact Record in each detail record.

Your example on your web site showed that you were using this feature to lookup Employee Records getting the FirstName and LastName. By your comment &#34;Interesting....3000 in lookup list ? &#34;, were you only thinking about companys that only have 20 to 30 Employee&#39;s when that example was created?

Based on your example I thought the LookUpList was the best way to do it.

Should I be using a JOIN for these type&#39;s of applications?

As far as only startup being slow, I am not sure since the application requires that i do the &#39;dbReset&#39; quite often because the dbSQL keeps changing. If I do a Refresh on the Browser it is still slow. Not sure if I do a Refresh using your Refresh button. I could test again when I rebuild my Contact Database. I am now testing with a small table.

I am sorry that I can&#39;t send you a copy of the Contact file since my company considers it confidential information.

Thanks for your help,

Mike


------------
FK at 7/27/99 5:26:31 PM

Mike,
Interesting....3000 in lookup list ? Do the following for me -

1. Is this a legi procedure, very seldom a lookup list is this big. Can you use Joins to pull the lookup values into the table instead. Describe your app to me a bit.

2. The lookup list is stored in memory instead of openning the destination table for every lookup. That is why for small lookup, it doesn&#39;t take any time at all. Pay attention to whether the performance is slow &#34;all the way&#34; or just at the startup in which the entire 3000 lookups are read into memory. Subsequent lookups should not take any time.

3. If you case is a legi one, I&#39;ll have to come up with a better algorithm for the huge lookups. How big is you DB, can you zip a copy and send to me.


FK



------------
Mike Carver at 7/27/99 4:31:53 PM


I am having a performance problem when I use the following command and when there is a lot of records in the &#34;Contacts&#34; Table:

X.dbDisplayLookUpList=&#34;(;|)Cid||SELECT Cid, FirstName & &#39; &#39; & Lastname as Name FROM Contacts&#34;

The GRID has less the 20 records.

Response Time:

Sub-second if I do not use the X.dbDisplayLookUpList
Sub-Second if Contacts has is < 10 records
5-6 Seconds if I have 913 records in Contacts

Any ideas on how to speed this up. What if I have 3000 records in Contacts?

I have a PII 350mhz with 128kb of RAM. If I look at the Performance monitor while hitting the REFRESH (Reload) button it hit 100% for the 5 seconds mentioned above. &#34;Cid&#34; is the Primary Key with NO duplicates.

Thanks

Mike