Here is the scenario. The software we are designing is used to maintain the inventory of a vault that houses magnetic media (tapes, CD's, etc.). There may be five hundred customers with a total of one million tapes.

Now, in working on the data entry screen, the software will need to search through the list of tapes for the customer for which the user is performing data entry. If the user has to perform data entry for say, thirty tapes, then switch to the next customer, the user will end up waiting longer for the view to perform the query then it takes the user to perform the data entry. I will also need to quickly locate a single tape within the query results--a result set that could easily approach fifty or even one hundred thousand records. This causes concerns about the speed of the seek/locate commands that I would be using.

A common example would be that when the user deposits a tape into the system, the software must first check the result set for that tape number. We don't want the user to redeposit a tape by mistake. So if the dataset is seventy five thousand records without an index, then the locate would be excruciatingly slow. Now if I were to create an index to speed the locate, then the inital load (query/index creation) would take an excessive amount of time and our users would complain about the speed. So what would you do?