Hi,

I've been trying to optimize a query that joins two tables and has one
additional condition on each of them:

insert into TARGET (...)
select ...
from A
inner join F
on A.FK = F.PK
where F.Status in ('1', '2')
and A.Status in ('1', '2')

The query actually also left-joins two additional tables but I don't
think that's relevant (correct me if I'm wrong).

Both A and F contain about a million records and a rather wide. F.PK is
F's primary key. A.FK is a foreign key that references F.PK.

Executing this query took about 13 hours without an index I should have
had on one of the two left-joined tables. Adding that index probably
brought the execution time down a bit, but according to my tests with
"first 1000" it would still have taken several hours to execute (more
than 10).

After a lot of experimenting I found that if I drop the F.Status
condition (which I realized I could do due to "business logic"), the
query executes a *lot* faster! I also bumped up memory usage on the
server, and I was now down to 30 minutes.

Can anyone explain why the F.Status condition ruins performance and how
I should reformulate the query and/or what indices I should add to make
it execute faster, in case I actually do need the F.Status condition?

I did try a lot of combinations of single-column as well as compound
indices om both A and F. Didn't try *all* possible combos though.

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64