performance tuning, which version is better ?

I often have difficulties in improving sql-queries because i simply cant compare different versions (with same results) to each other. I have written a little forms modula that simply executes any query and tells me how long (in hundredths of seconds) it took. But the results vary very wide. E.g. if statement A first takes 1.77s and B 1.99, it can appear that the next try A requires 2.03s and B 1.80s and so on. In general, the results vary so wide that i can't find a significant difference. The problem is worse with shorter queries, but i cannot change the data i'm working on every time - and if i did, i was never sure if i changed it "significantly" or not.

The problem can not be caching: re-executing the same query again and again creates some kind of random numbers (only the first is often higher than the rest).
There should also be no influence from elsewhere: the database i'm workung on is on a dedicated machine and in most situations i am the only one working on it.
So I don't understand why my timings are not reproducable.

Has anyone a tool that gives you _reproducable_ timings of sql-statements ?
Or is there some different, more reliable method to measure the load a query creates on the database ?

I have also tried to improve queries as some books explain, but in most cases the books ask question about quantity-relations and so on i cannot answer in general. Or i get hints that should save 50 percent and nothing changes. (Or it gets worse)

Another tool often mentioned in books is "tkprof". But on one hand, it deals a lot with physical reads - which depend on caches and therefore will be completeley different when there is not only one person working with the database - and on the other hand, i already got cases when one statement is better than another in each single comparison but nevertheless it is not significantly faster (or slower).

Does anybody know a performance measuring method being reproducable, independent of influences from parallel happening actions from other users and matching to the results you get looking on the clock ???

Thanks in advance

Arnold Schommer Received on Fri Dec 04 1998 - 01:24:40 CST