Thanks Adam.

I'll try adding some indexes and see how it goes.

I have around 300,000 rows with four blob columsn that
store XML documents. I think the reasoning behind the
size makes sense now.





--- Adam <s3057043@...> wrote:

> --- In firebird-support@yahoogroups.com, "Hari
> Kolasani"
> <hpkolasani@y...> wrote:
> >
> > 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?
>
> See Alexandre's response. Or read the FAQ, as this
> question is asked
> 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 is
> > large?
> >
>
> Firstly, 600 MB is far from "large". I think your
> problem is to do
> 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
>
>
>
>

__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/