I have an application that has to access a large table of employee pay data in order to perform a calculation. The table sits unindexed and external - each query of it is taking over a minute which the users think is a drag.


The table has about 100K records. The record looks like


pay period (date)
employee id (integer)
paydate (date)
other numeric pay values


the logic of the query selects records for an individual employee (by id number) after a specific date. Employees have a record for each pay so there are multible records for each employee id in the file.

I had thought about bringing the table into the project and setting up an index on employee id to help the query but the improvement was zero.

Can anyone suggest a strategy?


TIA
Chris