Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever made, the list returns pretty quickly. However, if an user then performs a filter on the list for a specific client name, this operation can take in excess of 6 minutes and returns about 15 rows out of a table that only contains 120,000 rows.
The underlying table does not contain the client name, this is held in another table (Clients). The link between the 2 tables is the clients reference number. I have defrag'd the indexes on the database, but to no avail. I have come to the conclusion that there is not a suitable index in place to improve this situation.
Q - Is it possible to create an index on the payments table but references the clients name in the clients table? Is this the solution to improve this situation or should I be doing something else (apart from telling the 3rd party vendor to improve this area in a future release)
note: I am a newbie if you haven't already guessed !