Click here to Skip to main content
Rate this: bad
good
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
MarqW4K
Comments

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

  Print Answers RSS
0 OriginalGriff 7,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 9 Apr 2013
Copyright © CodeProject, 1999-2014
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