Click here to Skip to main content
12,072,540 members (66,835 online)
Rate this:
 
Please Sign up or sign in to vote.
I have two tables, one called parts, and one called customer_parts. The first is our catalogue of parts available, and customer_parts is a matrix of each of our customers' part references. The basic schemas are...

parts:
part_number | description | [other fields]...
--------------------------------------------------
            |             |

customer_parts:
customer_id | part_number | customer_part_number
--------------------------------------------------
            |             |

I have a full text catalogue that contains both tables, and the following fields:
parts.part_number
parts.description
customer_parts.customer_part_number

Below is a cutdown version of a query I run, and it runs extremely fast. So far so good...
@CustomerID and @SearchTerm are inputs into the stored procedure

DECLARE
	@FTS_CONTAINS varchar(200),
	@FTS_FULLTEXT varchar(200)
 
-- Generate full text queries
SELECT @FTS_CONTAINS = '"' + ISNULL(@SearchTerm, '') + '"'
IF IsNull(@SearchTerm, '') = ''
	BEGIN
		SELECT @FTS_FULLTEXT = '""'
	END
ELSE
	BEGIN
		SELECT @FTS_FULLTEXT = @SearchTerm
	END
 
-- Lookup matching parts
SELECT *
FROM parts p
	LEFT JOIN customer_parts i ON p.part_number = i.part_number AND i.customer_id = IsNull(@CustomerID, -1)
WHERE (IsNull(@SearchTerm, '') = ''
	OR CONTAINS(p.part_number, @FTS_CONTAINS)
	OR FREETEXT(p.description, @FTS_FULLTEXT)
	)

However, when I add the following line into the OR conditions, my query suddenly takes forever to execute. Take it back out, and it's instantaneous.

OR CONTAINS(i.customer_part_number, @FTS_CONTAINS)

I'm afraid I'm pretty new to full-text querying, so I'm a bit stuck. Any help would be appreciated.

Thanks
Posted 8-Apr-13 23:03pm
MarqW4.3K
Comments

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 9 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100