Better TSQL for searching - looking for suggestions
-
TSQL is my biggest weakness here. I really don't enjoy writing it but you have to know it to get things done. I'm looking for some help here, on making a better search system, and I'm not sure which direction to go. I guess at first, I should get the TSQL right, or better at least. I have a FTS catalog with partNumber, Title, Description, VendorName for the productInfo table. I wrote this 2 years ago, but it's pretty weak, and doesn't work well. For instance, if I search part number "06-CR10", it produces nothing, but if I search "CR-10", it produces a result. I have trouble with 2 words or phrases like "tile spacers" using CONTAIN, but it works fine using FREETEXT. my question is: Should I be detecting multiple words, and use different TSQL based on word counts? Or just better TSQL? I'm just looking for suggestions.
Dim mySelectQuery As String = "SELECT " & _
"ProductID, Category, SubCategory, ShortDescription, LongDescription, Thumbnail, PartNumber, Price " & _
"FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR FREETEXT(PartNumber, @Query) " & _
"ORDER BY RANK() OVER (Order BY PartNumber, ShortDescription, LongDescription) DESC" -
TSQL is my biggest weakness here. I really don't enjoy writing it but you have to know it to get things done. I'm looking for some help here, on making a better search system, and I'm not sure which direction to go. I guess at first, I should get the TSQL right, or better at least. I have a FTS catalog with partNumber, Title, Description, VendorName for the productInfo table. I wrote this 2 years ago, but it's pretty weak, and doesn't work well. For instance, if I search part number "06-CR10", it produces nothing, but if I search "CR-10", it produces a result. I have trouble with 2 words or phrases like "tile spacers" using CONTAIN, but it works fine using FREETEXT. my question is: Should I be detecting multiple words, and use different TSQL based on word counts? Or just better TSQL? I'm just looking for suggestions.
Dim mySelectQuery As String = "SELECT " & _
"ProductID, Category, SubCategory, ShortDescription, LongDescription, Thumbnail, PartNumber, Price " & _
"FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR FREETEXT(PartNumber, @Query) " & _
"ORDER BY RANK() OVER (Order BY PartNumber, ShortDescription, LongDescription) DESC"Short answer: use both CONTAIN and FREETEXT. Less short answer: The idea is to call a PROCEDURE which complements the use of the higher energy union of the two methods. Until discovering INFORMATION_SCHEMA ("Views") I was adhereing, for many years, to using LIKE without even considering FULLTEXT search-enabled tables, if you can believe that. Besides the overhead requirement of LIKE and the stored procedure call (yeah, using "String" would be ok), I had only the sys.x catalog from which to draw the associations into a database relationship. Best answer: hey, whatever works for you.
-
Short answer: use both CONTAIN and FREETEXT. Less short answer: The idea is to call a PROCEDURE which complements the use of the higher energy union of the two methods. Until discovering INFORMATION_SCHEMA ("Views") I was adhereing, for many years, to using LIKE without even considering FULLTEXT search-enabled tables, if you can believe that. Besides the overhead requirement of LIKE and the stored procedure call (yeah, using "String" would be ok), I had only the sys.x catalog from which to draw the associations into a database relationship. Best answer: hey, whatever works for you.
Sounded complex on the fist reading. But very informative. I'll do some more reading on some of the words you used, to learn more about it. But in the meantime, I experimented with FREETEXT, CONTAINS and LIKE an sort of solved my partNumber issue by using LIKE for the partNumber, so now the part number works.
Dim mySelectQuery As String = "SELECT COUNT(ProductID) FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR PartNumber LIKE @Query2"I will try searching each column with specific ways to obtain the result I'm looking for and keep refining it for now.
-
Sounded complex on the fist reading. But very informative. I'll do some more reading on some of the words you used, to learn more about it. But in the meantime, I experimented with FREETEXT, CONTAINS and LIKE an sort of solved my partNumber issue by using LIKE for the partNumber, so now the part number works.
Dim mySelectQuery As String = "SELECT COUNT(ProductID) FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR PartNumber LIKE @Query2"I will try searching each column with specific ways to obtain the result I'm looking for and keep refining it for now.
Also, use the BOL (Books on Line) for ssmse. Specifically look up INFORMATION_SCHEMA. Chock full of pre-associations the View approach to sys.objects is. Ironically, it was the "SPECIFIC_CATALOG" of "View" (really I still don't quite comprehend what that word means ...) that happened concurrently with my first attempt to use FULLTEXT indexes, and quick succeed, that made me toss my entire search system at the time. But yeah, use all three; an even better idea.
-
Also, use the BOL (Books on Line) for ssmse. Specifically look up INFORMATION_SCHEMA. Chock full of pre-associations the View approach to sys.objects is. Ironically, it was the "SPECIFIC_CATALOG" of "View" (really I still don't quite comprehend what that word means ...) that happened concurrently with my first attempt to use FULLTEXT indexes, and quick succeed, that made me toss my entire search system at the time. But yeah, use all three; an even better idea.