>>Small tables are frequently stored in memory, but turning them into ordered cursors (no index needed) or arrays might speed things up if they are accessed frequently.
>>
>>However the time saved might be a matter of tenths of seconds - are you sure that the 'slow points' of your app are in table access?
>>
>>Of course if you're working with SQL-Server or other remote data then it would definitely be a good idea to turn small lookup files into cursors or arrays early in the program and reuse them.
>>
>>HTH
>>Barbara
>>
>>>
>>>So, do you suggest to select code from LookupTable into cursor curLookupTable
>>>index on code tag code for all lookup tables in order to put them into memory?
>>>Do you think, it would speed up the process?
>
>I have found that where I need to recursively hit a lookup (sometimes ones that are dynamically created based on the user) that its MUCH faster to bring it into an array, and do ASCAN's to get the row I need. Many times I have seen speedups that appear to be factors of 4 or higher.

This is very important observation. Do you have any timing measures and simple cases to re-create?

Basically, our situation is, that we have lots of small Lookups tables, like RoofMat, RoofType, BuildStyle, etc. These fields contain about 10 records each. We'd like to validate our database (each table has ~3-5mln. records). Currently each "lookup" validation functions checks, if necessary table is open and them uses IndexSeek. We can open create arrays for each Lookup and use ascan in these functions. We'll test this option.