eligoldner avatar image
eligoldner asked

What is the best way to search for multiple keywords across multiple tables?

Hello, We have a search feature for our document management system. The search box allows for multiple keywords, and needs to search for the keywords across multiple columns (document name, name of the person who created the document, who the document pertains to etc.). I've tried to use the following approaches, though none of them seem to work well: 1. CONTAINS - this won't necessarily work for me, as these columns aren't on a single table. I've tried to have all of the data from both tables "live" in a single view; however, due to the fact that the query to get this data is by nature drawn via an OUTER JOIN, indexing on the view isn't possible, hence no CONTAINS. Additionally, I wasn't able to figure out a way for this to work with multiple keywords (separated by spaces). I did try to use a REPLACE feature (as per suggestion from another question asked on a forum) which would turn "Hello World" into " '"*Hello*" OR "*World*"' ", though this returned a result set with no seemingly relevant results. 2. FREETEXT - Although this seemed to look better than CONTAINS, it wouldn't find like words. For example, the search term "He" wouldn't return "Hello". This would not work for me. 3. Use of a variable table - I have a script (to be placed in a stored procedure once complete) which will split a string with the space character being the delimiter, and insert each word into the variable table. I then have a query which does an INNER JOIN on this temp table WHERE the data in the requisite columns (document name, created by etc.) = the column in the variable table. I have an OR in this join to connect to any of the columns which can contain this keyword. The results are correct, however the search across approx 290k rows takes more than 2 minutes. I cannot have the performance be so slow Any Ideas would be appreciated. Thanks so much, Eli
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

eaglescout avatar image
eaglescout answered
@Eligoldner, I am afraid that there isn’t an easy approach to this. Most likely, you’ll have to create complex stored procedures and functions to run Dynamic queries. I would suggest that you begin by reading this article: Yes, this approach may be time consuming, but will save time in the long run, if you plan on doing this continuously. Next, I would suggest that you look into free third party software (SQL Workbench) to do what you want. In short, I am afraid that there just isn’t enough simple steps to get this done. Hope other SQL pros have ideas Good Luck!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@eaglescout That article is what fueled the CONTAINS/FREETEXT approaches. Based on the fact that I cannot have the entire data set in one view, I abandoned the former approach, and cannot use the latter approach due to it not searching for comparison ("He" will not bring back "Hello"). Complex sprocs don't scare me, though I wasn't able to figure out a way to get the data to get back faster... Thanks for taking the time to answer! Eli
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.