> 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.

SuperClassic has a separate page cache per connection as well.



--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!