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