| Subject | Re: FireBird .FDB file size won't go down even after deleting a large number of |
|---|---|
| Author | Adam |
| Post date | 2005-11-29T22:42:38Z |
--- In firebird-support@yahoogroups.com, "Hari Kolasani"
<hpkolasani@y...> wrote:
several times a week.
Another similar question:
http://groups.yahoo.com/group/firebird-support/message/69300
with record versions, because you mention a couple of thousand rows.
Our "blank" database we use for new installs has > 15000 records in
one table alone, and there are about 150 tables with various degrees
of preset data. The file size is only about 5MB, so unless you are
storing huge records, 600MB is not usual for such little data.
MGA databases keep old "versions" of records until a time when there
is no need for them. Of course if you leave transactions running for a
long time, then Firebird has to keep track of the old version, so it
can't be garbage collected, so the space it occupied can't be reused
by other records, so the FDB size grows.
The way you tune performance is to provide indices that can help the
queries you are running. The optimiser is pretty good at using any
available index to run a query faster, except for a few known quirks.
Query speed should be independent of database size. If your queries
are using a natural plan, that means it must inspect EVERY record in
the table to see if it meets your conditions. Obviously, the more you
insert, the longer it will take.
Firebird is largely self tuning though. If you are having performance
troubles with a query, post the query and table definitions to the
list. I have seen some multi-minute / hour queries optimised to
sub-second responses.
The basic rules:
* Do not create a duplicate index. This confuses the optimiser and it
doesn't use either.
* Primary keys, foreign keys and unique constraints are all
implemented using an index. Creating an index on these fields is as
bad as breaking the first rule, with the same results.
* Avoid table scans on tables which grow as data increases by adding
an index that will limit the work to be done.
* Avoid defining an index which has "poor selectivity" (ie a lot of
duplicate values). You can always throw the primary key on the end of
the index definition to gain good selectivity.
Adam
<hpkolasani@y...> wrote:
>See Alexandre's response. Or read the FAQ, as this question is asked
> I have been using a FireBird database in my app and the database
> grew to several thousand rows and the .FDB file size went up to 600
> MB over a period of a few weeks.
>
> First of all I noticed that the application runs very slow (SQL
> queries) when the file size is big.
>
> So, I deleted all the rows in the database using an SQL DELETE
> statement via ODBC, the file size still remains at 600 MB even when
> when the database is empty.
>
> 1. Is there anyway to bring the .FDB file size down?
several times a week.
Another similar question:
http://groups.yahoo.com/group/firebird-support/message/69300
> 2. Is there anyway to tune the performance when the FDB file size isFirstly, 600 MB is far from "large". I think your problem is to do
> large?
>
with record versions, because you mention a couple of thousand rows.
Our "blank" database we use for new installs has > 15000 records in
one table alone, and there are about 150 tables with various degrees
of preset data. The file size is only about 5MB, so unless you are
storing huge records, 600MB is not usual for such little data.
MGA databases keep old "versions" of records until a time when there
is no need for them. Of course if you leave transactions running for a
long time, then Firebird has to keep track of the old version, so it
can't be garbage collected, so the space it occupied can't be reused
by other records, so the FDB size grows.
The way you tune performance is to provide indices that can help the
queries you are running. The optimiser is pretty good at using any
available index to run a query faster, except for a few known quirks.
Query speed should be independent of database size. If your queries
are using a natural plan, that means it must inspect EVERY record in
the table to see if it meets your conditions. Obviously, the more you
insert, the longer it will take.
Firebird is largely self tuning though. If you are having performance
troubles with a query, post the query and table definitions to the
list. I have seen some multi-minute / hour queries optimised to
sub-second responses.
The basic rules:
* Do not create a duplicate index. This confuses the optimiser and it
doesn't use either.
* Primary keys, foreign keys and unique constraints are all
implemented using an index. Creating an index on these fields is as
bad as breaking the first rule, with the same results.
* Avoid table scans on tables which grow as data increases by adding
an index that will limit the work to be done.
* Avoid defining an index which has "poor selectivity" (ie a lot of
duplicate values). You can always throw the primary key on the end of
the index definition to gain good selectivity.
Adam