I am working on a quite big Visual Foxpro application, using a mixture of native Foxpro database functions (SELECT, SET ORDER TO, SKIP e.t.c.) and SQL (SELECT * FROM..).

I have an impression, that we do not use indexes correct, like we have an index on a file saying "KEY3", but we always search it with "UPPER(KEY3)" e.t.c. which does'nt make use of Rushmore optimization (at least when used in SQL-SELECT statements, according to Microsoft's "Using Rushmore to Speed Data Access").

I was wondering, whether there is a tool, where you can trap theese errors - which are kind of hard to find in a very large project.

Let me give you some background: I used to program RPG on a AS/400, where the search is quite similar to a SELECT blablafile, SET ORDER TO blablaindex, SEEK blablavalue, IF FOUND() and so on. Further there is also quite big similarities to DO WHILE NOT EOF(), blbla, SKIP and so on. We had a system, that could monitor all the programs, and tell us how many read's that were done. So we could extract a list, where we could see, that program xyz was doing 200,000 reads, where we could figure it should only do 10. This could give quite good information on which programs could use some optimization, in order to use the created indexes properly.

I hope you understand my question: Are there any similar tools to Foxpro, that can tell where we don't read the database correctly (sort of). The problem is, that performance gets really bad, when not using indexes properly, but you normally find the results, so it does'nt appear as an error.

Thanks,

Hans-Henrik Andersen