I'm using a lookup query in a DBGrid to automatically display an article description in the lookup field as soon as the user types in the article code.
The lookup query is TQuery that's using a complex query to retrieve article information from (indexed) SQL Server tables. The result of this lookup query contains two fields: the article code and the article description. The article code is the key field that is linked to the field the user types in the grid. The article description is the result field that is displayed in another column of the grid.
The result of the lookup query is a set of about 10.000 article codes and descriptions.
Technically, this works. There is however one major problem: the lookups are rather slow. I'm having a serious performance problem caused by the lookups: the lookup time seems to increase when the user enters more records in the grid. When 30 records are entered there is a significant delay when posting a new record, the lookup takes longer than with only a few records in the grid. When about 100 records have been entered, it's almost impossible to work with the application anymore.
When I remove the link to the lookup query, there's no delay at all.
I'm using Delphi 5 Enterprise, SQL Server 7, and the TQuery linked to the DBGrid uses cached updates. As mentioned, without the lookup field there's no delay at all and below 30 records in the grid there's also no noticeable delay.
Can anyone tell me how to speed up this lookup? How can I prevent this increasing delay when the number of entered records in the grid is growing? Perhaps someone did ever have the same problem?
Any help is welcome.