Arnold Schommer wrote:
>
> 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
You need to use logical reads for the query
Use tracing and tkprof, because tkprof will give you the logical as
well as physical reads. If you are interested mainly in the cost
of query, use the explain=username/password option with tkprof
and you'll see the rowcounts along with the different execution
steps for the statement, which'll simplify tuning.
Virag
Received on Fri Dec 04 1998 - 10:28:14 CST
