Subject Performance troubles
Author steffengrondahl
I'm working on a java application and are facing some serious
perfomance trouble.
The application (working thru jdbc) makes a simple join like this

select contactno from contacts c
state in (1,2)
(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).
If I make a simple query and let java do the work I can obtain the
desired result in a few seconds:

select contactno, giftvalue from gifts where someotherfield
= 'somvalue' and contactno in (select contactno in contacts where
state in (1,2))

Here java sums up giftvalue for each contactno and dicards contactno
where sum of gifts below 50.0.

I would like firebird to do the work. Any ideas ?

I've been thinking of
- rebuilding indexes
- making new index (contactno is primary key in contacts amd foreign
key in gifts, moreover there is index on contactno and someotherfield
in gifts)
- rewrite the SQL
- giving a hint so I directly 'activate' a given index
but I need some clues whether this will work and how to do it.

Firebird version: 1.0.3
OS: WinXP, Win2000 and Linux
java-version: 1.3, 1.4, 1.5