Sorry for taking mt time in responding.
This system is in Dos2.6 and there is 1,000,000 records in the main table and 7,000,000 records in one of the look-up tables.
The 1,000,000 record table has 240 fields and is quite mad!
Using indexes (40 of them) and SQL selects, we can access records nearly instantly, there is less than 1 second delay.
When we first took this system over, some of the processes took over 1 hour to run, I personally reduced these to less than 10 seconds. In particular, the customer required 10,000 records that matched a multiple field query.
Admitedly there are some mad indexes to help speed things up, some of them reference 15 fields.
Regarding the look-up table, the system is required to locate 1-10 records that again match a multiple field query, this is less than 1 second.
We have learned a great deal regarding speed.
Whenever creating a new system, we always generate 100,000 records in all tables just to analyse a worse case scenario. We have been caught out before!
Kind regards,
BrainBashers:
www.internet-uk.com/puzzles