Den 2011-08-23 12:16 skrev Vander Clock Stephane såhär:

> > I have a database that's about 56 Gbyte and has 150 million or more
> > records (each) in two of the tables.
> >
> > I find query performance to be very agreable, provided an index can be
>
> yes, but when you need order by, even with index it's not funny at all :(
> did we need to use the index on the query filter or on the order by filter ?
> this off course depend how many record the query filter will return ...
> with few reccord returned better to use the index on the query filter,
> with lot of
> reccord returned better to use the index on the order by ... but this
> the optimization
> engine can not know by advance ....

Not sure what you mean here, but have you tired adding both an ascending
as well as a descending index on all columns that are involved in
(possibly descending) sorts?

As far as I know, FB will use any relevant combination of single-column
indices for equality where criteria.

Are you sure you've got the temp space for sorting setup in a good way?
Perhaps put it on a RAM disk or fast SSD?

Is your page cache large enough? I'd recommend superclassic to be able
to utilize a very large page cache, e.g. 20 Gbyte, that's shared for all
connections. Classic has separate page cache per connection so cache
can't be too large, and superserver has problems with multiple cpu:s/cores.

> > So, provided all your queries will be able to use the index on
> > Contact_ID, I think you will be fine. And if not, then there are
> > probably better ways to handle the situation than your option 2, e.g.
> > moving records to an archive table or "throwing hardware at it".
>
> I already split the big table in 56 "archived like" table to reduce
> the size. for now the perf are ok, but what in one year with the growing
> of data :( i know that this system will soon meet the limit of firebird
> OR the server himself

How many records do you anticipate to have in total, all archive tables
included, within the lifetime of this system? What's the growth rate?
What would be a relevant archive cycle? Do your users do selects
regularly from archives or are they mostly interested in the "current"
stuff?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64