Filters on the data

[kucol]

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

[Hugo Kornelis]

Re: Filters on the data

On 27 Oct 2006 14:47:17 -0700, kucol wrote:

>Hi guys,
>
>I wanted to ask you for help as I am struggling with it second evening
>already...

(snip)

Hi Kucol,

I noticed that you have posted the same message to
microsoft.publi c.sqlserver.pro gramming as well. Please do not multipost
in the future - had I not checked the other groups first, I might now
have spent time to duplicate an answer you've already gotten elsewhere.
I prefer to spend my time answering questions that have not been
answered yet.

I'll keep an eye on the discussion in .programming and chime in if I
feel I have anything new to add.

--
Hugo Kornelis, SQL Server MVP