At 10:07 AM 10/21/2004, EvelyneGirard wrote:

>When testing queries, we are sometimes mislead to think we have
>improved it because the server executes it faster the second time.
>Of course, we can look at the plan to have an idea if the query is
>optimized or not... but I'd like to know how much time my query would
>take to execute and i can't because the query will pass from 10
>seconds the first time it executes to 0.5 seconds after this, until I
>reboot the system (local server).

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. Over time, the
most frequently used pages will become cache resident. These include
the upper levels of indexes and pointer pages for the tables in use.

Second, unless your application is very unusual, the cache is going
to make a difference to it. Very few applications use one table or
group of tables exclusively, then switch to a totally different set
of tables, invalidating the cache. Even that sort of application
benefits somewhat from the cache because a number of pages are needed
for any database operation - transaction pages, the database header
page, free space pages - and those too become cache resident.

Third, you shouldn't need to reboot the system to clear the cache -
shutting down and restarting the Firebird server is quite adequate.

Fourth, if you want to measure the number of page references required
for the test, use the performance measuring tools in the database
rather than trying to guess with timings. Check the documentation for
the tools you're using - something should return the number of reads,
writes, marks, and fetches. In the API, it's part of the database info
call. A read or write is a physical I/O operation. A fetch is a page
reference satisfied in the cache. A write is a page change made to the
cache but not written out. If you store twenty records on a page then
commit the transaction the page will represent one write and twenty marks.

>I supposed it was the server caching that did this so I tried to set
>the Cache to 0 and then to 1 (in firebird.conf) without any
>difference.

There's a minimum number of cache pages (about 100 from memory) that
the system absolutely needs and will take, regardless of the number
you specify. Cache pages are the buffers that Firebird uses to hold
database pages. Without any cache, it can't open the database.

>Is there a way to disable this feature completely for development
>purposes ?

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. 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. When
you begin testing multi-way joins over varying conditions, under
significant contention, you'll find that a larger cache offers more
benefits.

Regards,


Ann