Subject Re: Deactivating cache to test query's performance
Author EvelyneGirard
--- In, "Ann W. Harrison"
<aharrison@i...> wrote:
> A couple of thoughts. First, the improvement from having the cache
> loaded is not "artificial" or cheating - improving performance is
> the reason for keeping recently used pages in cache.

I'm aware of that, what I am trying to know is the approximate time
it will take for my query to run the first time it will run on the

> Fourth, if you want to measure the number of page references
> required for the test, use the performance measuring tools in the
> rather than trying to guess with timings. Check the documentation
> the tools you're using - something should return the number of
> writes, marks, and fetches.

I don't. As for Reads and Writes, I don't understand anything to it :

The first time I run my query, I have 6949 reads against 20849 the
second time I run the same query. Writes pass from 37 to 45. If I
wanted to measure performances, I would try to have reproducible
results, which I can't have anyway...

> I hope the answer is obvious. Since even the minimum cache improves
> your performance markedly, I'd guess you're still working on pretty
> simple tests.

I see I didn't made myself clear, maybe it's my English that is not
good enough... I don't want to improve performances in a production
environment, if I did, I would maximize the Cache not minimize it!!!

> A single user inserting 10,000 records without indexes
> active can be done with a tiny cache since the only pages that are
> revisited are system pages and the pointer pages for the table.
> you begin testing multi-way joins over varying conditions, under
> significant contention, you'll find that a larger cache offers more
> benefits.

I'm testing queries with multiple joins and sometimes, the
performance is not good enough so I try to change it to an exists()
conditions or anything else but I can't determine if this is better
or worse because the server now has cached the pages it needs and
gives me results in 0.313 sec instead of 10 seconds.

I only wanted to know if there was a way to compare my results with
an objective measure. What I do most of the time, I compare the
plans used but this is subjective and it needs a better comprehension
of the database than needed to compare objective values like
Execution Time... this would have simplified things for some of the
less experienced programmers who are working with me. The answer
seems to be that it is not possible ...

Thank you.