Posted by kucol on 10/27/06 21:02

Hi guys,

I wanted to ask you for help as I am struggling with it second evening
already...
I have got tables DEVICES and PARTS.
One device can consist of multiple parts.

But...

I have also another table - FILTERS (id int, type int, is_not int,
phrase varchar(40))
where:id - just id,
type - filter type - can be 1 - for devices and 2 for parts,
is_not - says if the phrase has to be in a description (0) or must not
be there (1)
phrase - word to found in the description

My trouble is when I want to apply three filters at once:
1. Find devices with description containing PHRASE
2. Find parts with description containing PHRASE
3. Find devices with description NOT containing PHRASE


Query selecting parts and devices is like:

SELECT device.id, part.id
FROM DEVICE JOIN PARTS
WHERE ...

What I did is:

SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
QUERY
) a
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')
) b
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2 AND
part_desc LIKE '%' + phrase + '%')
) c
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1)
OR NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')


It works, but very slow. In DEVICES tables is 2 milion rows and in
PARTS is 3 millions.

I turned SET STATISTICS IO ON, and they show that FILTERS are being
asked veeery often.

It must be more efficient way to acheve this but I must be blind.

Thanks fo any advices,
Kucol

[Back to original message]