Hi Svein Erling,

THanks you for the count(distinct ...) suggestion - that was a new one
to me. I tested it. Yes, it performs slightly worse than exists - about
30% slower. That's when I've replaced the CHILD "in" list with a
sequence of "like 'xx%' or like 'xy%' ...", which brings down the number
of actual criteria from 283 to 23 in my test case. Testing with all 283
criteria seems to indicate that the count(distinct)+join is a lot
slower. Hmm...

Anyway, what seems to be the best way allaround is to denormalize the
CHILD data into the MASTER table. That's actually the way I originally
receive the data - it just made sense to me to normalize it for
querying. Guess I shouldn't have.

Changing from exact list of criteria to a "like 'xy%'" list and
denormalizing brings down execution time from almost 3 minutes to 18
seconds. Seems like I'll have to consider a modification to my SQL
generator so it supports that kind of query. *sigh*

Kjell

Svein Erling Tysvær wrote:

> The one thing you could try, is to use a join rather than an exists, I
> have no idea whether it will be faster or slower (well, I'd guess
> marginally slower):
>
> select count(DISTINCT MASTER.PK)
> from MASTER
> join CHILD on MASTER.PK = CHILD.FK
> where MASTER.FIELD in ('1', '2', '7', ...long list)
> and CHILD.FIELD in ('a', 'b', 'r', ...long list)

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