Subject Re: [firebird-support] Re: Performance
Author Helen Borrie
At 12:56 AM 29/04/2007, you wrote:
>--- In, Helen Borrie <helebor@...>
> >
> > Remind us: what version of Windows do you use and what is the name
> > of your database file?
> >
>I'm running WinXP SP2, Core2Duo 1.66GHz, 1GB Ram. The name of db is
>test.gdb. I have AVG anti-virus turned on.
> > Also: how do you know it is sluggish? I mean, tell us what is your
> > first encounter with the database after the machine has been
> > cold-started. What is it meant to do, and what does it do instead?
> >
>I do a simple select * from my_table where primary_key = 1. There's
>only one record returned. I used IBOConsole to do it. The plan used
>is correct (i.e. using the primary key). I expect this to be instant
>but there is a lot of disk io instead.

OK, you have at least two, possibly three things going on here:

1. The first time the server attempts to open the database, WinXP
jumps in and locks the file whilst it takes a disk-image copy of the
entire file. This is happening because your database has the ".gdb"
extension. This friendly little gift from Microsoft is called
SystemRestore. The problem is documented in the release notes of
both Fb 1.5 and 2.0. Don't use ".gdb". You don't need or want
SystemRestore for a database.

2. AVG is probably then stepping in and virus-checking what WinXP
has just written to disk.

3. IBOConsole is a client application. One of its features is that
is queries the entire metadata of the database and caches it upon
your initial connection. This is an optional feature of IB Objects,
which is the data access driver used for that application. The
application writer enabled that option no doubt because of the quite
reasonable assumption that users of an admin tool would be interested
in having instant local access to the metadata.

4. If you do an ordered query and the server needs to write an
intermediate sort file to your temp disk space, it's likely that AVG
will interfere with that, too. It won't be an issue with a query
returning one record, however.