|
|
Posted by kucol on 10/27/06 21:47
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
Navigation:
[Reply to this message]
|