svanderclock wrote:

> because as the user can freely write the params of the query, it's very hard to know how much rows will result
>
> for exemple these fields are mandatory :
>
> ID_place = xxx and
> Status = 1 and
> kind = 65 and
> subcategory = 12
>
> in the case yes can be < 10000 row, but just changing kind = 65 by kind = 64 will output 2 000 000 row !
>
> we have an huge amout of data (the system must be ready for 80 000 000 rows!) and what is the most cost intensive is the order by after the filter ! for this order by we don't have any other choice than use all the index combination as possible. because even if a set return 10 000 row, the order by will kill the performance.
>
> but in the other way we can not have a system that cost 1 seconds to add a row ! did we cross the limit of firebird ? or did we do something wrong in our index (too much fields / index for exemple) ?
>
> stephane
>
>

My first attempt would be to create an index for each individual field.
Firebird could combine 2 or more indices to narrow down the result set.
So if you have 10 fields, you will need only 10 indices and not every
possible combination.

The sort will be in memory (as far as you have enough memory of course),
a Quick Sort on 10 000 records should be instantaneous.

see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br