| Subject | Re: [firebird-support] Performance improvements for an embedded database after hibernation |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-11-19T23:57:27Z |
At 10:15 AM 20/11/2009, you wrote:
select * from atable where yes_no = 'Y' would be pretty horrible. Try either removing that index, or making it more selective by adding the primary key to it (at the right), or introducing some hint that will suppress use of that index, such as
WHERE
(INDEXED_FIELD = :AVALUE AND 0=0)
If your search index is multi-key then run SET STATISTICS on that index. If you find that helps, make it something you do so often as is needed.
If, as you say, the rows are pretty wide, e.g., wider than 4Kb, you might like to backup the database and restore it with an 8Kb page size. Take the size of the database cache into account, though, since doubling the page size will double the size of the cache, which won't be helpful if the laptop is short of memory.
None of these things will help speed up the wake-up time from hibernation, of course, since Firebird's memory structures are as much victim to the system's strategies as any others, including how it deals with the FS cache (which a well-configured Firebird server usually relies on for good performance). And laptops often have very slow disks...which matters for recovery time from hibernation if the machine went into hib with any significant degree of RAM utilisation.
./heLen
>Sean,Look at the selectivity of the index on the filtered field. It smells like a case where an index has such poor selectivity that it actually makes the indexed read less efficient than the natural one, e.g.
>
>The database is on the local hard drive.
>The query returns 4,000 rows each of which is pretty wide, so there is a lot
>of I/O involved.
>The query itself is pretty simple and just selects from a
>single table while filtering on an indexed field.
select * from atable where yes_no = 'Y' would be pretty horrible. Try either removing that index, or making it more selective by adding the primary key to it (at the right), or introducing some hint that will suppress use of that index, such as
WHERE
(INDEXED_FIELD = :AVALUE AND 0=0)
If your search index is multi-key then run SET STATISTICS on that index. If you find that helps, make it something you do so often as is needed.
>What setting should I consider changing in firebird.conf in order to make aIf these are sorted queries (ORDER BY, GROUP BY, DISTINCT) and plenty of RAM is available then you could consider tinkering about with the Sort* parameters. Use typical data for testing it, though, and test it on the actual machine.
>query like this faster?
If, as you say, the rows are pretty wide, e.g., wider than 4Kb, you might like to backup the database and restore it with an 8Kb page size. Take the size of the database cache into account, though, since doubling the page size will double the size of the cache, which won't be helpful if the laptop is short of memory.
None of these things will help speed up the wake-up time from hibernation, of course, since Firebird's memory structures are as much victim to the system's strategies as any others, including how it deals with the FS cache (which a well-configured Firebird server usually relies on for good performance). And laptops often have very slow disks...which matters for recovery time from hibernation if the machine went into hib with any significant degree of RAM utilisation.
./heLen