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
@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:
http://msdn.microsoft.com/en-us/library/ms142571.aspx 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!