November 24, 2008 at 10:08 am
Hello,
I have a Categories, Subcategories, and Company-Categories (CC) association table in my SQL database. I need to use a free-form text search on the entire joined category name which uses both tables, i.e. 'subcategory category'. Creating a view that combines the terms and uses LIKE '%keyword phrase%' is obviously quite slow since it scans and not seeks. Full-text indexing seems perfect for this situation. The problem is is that I need to create an indexed view first, and then implement the FTI. Unfortunately, I cannot create an indexed view since no matter how I write the query, I have to use UNION, OUTER JOIN, derived table, or a view referencing a view since the CC table does allow nulls as a subcategory, and I need to use the CC table to obtain a count of how many companies are in the matching categories. Views using those terms cannot be indexed.
I found one workaround is to create a third table that has the 'subcat cat' combinations in an indexed field. This 3rd table is updated through triggers on the Categories and Subcategories tables. I'd rather not go this route as now I'm eating up more disk space and, in effect, duplicating data that's already there.
Any ideas?
TABLE: Subcategories (SubcatID int, CatID int, Subcategory varchar(50))
TABLE: CompanyCategories (CCID int, CatID int, SubcatID int, CompanyID int)
VIEW using OUTER JOIN and VIEW referencing a VIEW:
SELECT CASE WHEN A.SubcatID IS NULL THEN
B.Category ELSE A.Subcategory + ' ' + A.Category END AS CombCatURL,
COALESCE (A.ID, B.ID) AS CatID, COALESCE (A.Category, B.Category) AS Category,
COALESCE (A.RCategory, B.RCategory) AS RCategory, A.SubcatID, A.Subcategory,
A.RSubcategory
FROM dbo.vwRCatSubList_A AS A FULL OUTER JOIN
dbo.vwRCatSubList_B AS B ON A.ID = B.ID AND A.SubcatID = B.SubcatID
vwRCatSubList_A:
SELECT C.ID, C.Category, C.RCategory, SC.SubCatID, SC.SubCategory, SC.RSubCategory
FROM dbo.Categories AS C INNER JOIN
dbo.Subcategories AS SC ON C.ID = SC.CatID
vwRCatSubList_B:
SELECT ID, Category, RCategory, NULL AS SubcatID
FROM dbo.Categories AS C
View using UNION:
SELECT FullCatName = RSubcategory + ' ' + RCategory, ID,
Category, RCategory, SubcatID, SubCategory, RSubCategory
FROM dbo.Categories AS C JOIN
dbo.Subcategories SC ON C.ID = SC.CatID
UNION ALL
SELECT RCategory, ID, Category, RCategory, NULL, NULL, NULL
FROM dbo.Categories AS C
November 26, 2008 at 12:46 pm
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply