Subject Re: [firebird-support] Performance troubles
Author unordained
Have you tried the following?

select c.contactno
from contacts c
inner join gifts on gifts.contactno = c.contactno and someotherfield = 'somvalue'
where state in (1,2)
group by c.contactno
having sum(gifts.giftvalue) >= 100;

I did a quick test, on a similar set of tables: i have about 300k rows in the table equivalent to
your 'gifts', and it takes about 1.x seconds to perform the query. I'm not sure how indexed I have
it, honestly, but it's running off my old laptop harddrive ... (Firebird 1.5.1)


---------- Original Message -----------
From: "steffengrondahl" <steffengrondahl@...>
> select contactno from contacts c
> where
> state in (1,2)
> and
> (select sum(giftvalue) from gifts g where g.contactno = c.contactno
> and someotherfield = 'somvalue') >= 100
> This takes several hours (contacts haves about 55,000 record and
> gifts about 1,000,000).
------- End of Original Message -------