469,299 Members | 2,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

Filters on the data

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

Oct 27 '06 #1
0 1490

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bryan Harrington | last post: by
6 posts views Thread by TJO | last post: by
1 post views Thread by Dieter Vanderelst | last post: by
10 posts views Thread by ballpointpenthief | last post: by
1 post views Thread by kucol | last post: by
2 posts views Thread by brino | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.