Full Text Search — How to install, configure and use it with SQL Server (and Outsystems)
In one of my projects, we had to ingest some big amount of data and search text data from an Outsystems web application. At the time I knew about Elasticsearch but unfortunately the client didn’t have it available, so my only available option was to start using the FullText search from SQL Server.
Note: I’ve only scratched the surface on Full-Text Search from SQL Server. I want to provide here the basics for you to be able to use Full-text search.
What is Full Text Search
Full-Text Search in SQL Server allows users and applications to run full-text queries against character-based data in SQL Server tables. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. Read more
Why I used Full Text Search?
The table where the searches were fetched had around 4 million records (and increasing!) with multiple large text columns. Querying this data was very slow. If we would make the queries with LIKE operators, it would be even worse resulting in timeouts.
After columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. These queries can search for any of the following:
One or more specific words or phrases (simple term)
A word or a phrase where the words begin with specified text (prefix term)
Inflectional forms of a specific word (generation term)
A word or phrase close to another word or phrase (proximity term)
Synonymous forms of a specific word (thesaurus)
Words or phrases using weighted values (weighted term)
Issue with SQL LIKE Operator
In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. A LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data.
Databases indexes are used to enhance performance when looking for something defined in your WHERE clause. However when it comes to filtering some text, e.g., using something like:
WHERE TextColumn LIKE ‘%string%’Then searches are slow, because the way database indexes work is optimised for matches against the whole content of a column and not just a part of it. In specific the LIKE search which includes wildcards cannot make use of any kind of index.
How to start using SQL Full Text search
So, it seems that using Full-Text Search on your queries will improve substantially the speed execution. What do you need to start then?
Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. Read more
Installing Full-Text Search
The Full-Text Search feature of SQL Server 2008 isn’t installed by default since it’s an optional component the Database Engine. During SQL Server 2008 Installation, the database administrator needs to select the Full-Text Search. Read more
Configure a Database for Full-Text Search
- Create a Full-Text Catalog
Connect to the SQL Server 2008 instance and expand Databases node > YOU_DATABASE_NAME > Storage.
Right click Full Text Catalogs and select New Full-Text Catalog from the drop down.
- Create a Full-Text Index
Now that we have the Catalog ready, the next step is to create a Full-Text Index. In this example, we will be creating a Full-Text Index on the table Customers.
Go to the properties of the new Full Text Catalog, choose the table you want to “Full Text Index”. (Note: That table must have a unique index.) Then choose which columns you want to index.
We are now ready to start performing some full text queries in the Customers table, which is now Fully Text indexed.
What now? Let’s make some queries!
I will be using in the following examples, queries with simple terms (One or more specific words or phrase). You can also check the references to see prefix terms, generation terms, proximity terms, thesaurus or weighted terms.
Given our Customers table:
Depending on your goal, it’s possible to extract the ranking score from the match.
Let’s search for the word ‘street’ in the column Address of the table Customers.
- Without ranking
select *
from customers
where contains(customers.Address,'street')
It finds all Customers where the Address has a word with ‘street’ in it.
- With Ranking Score
select customers.Name, customers.Address, key_search.rank
from customers
INNER JOIN CONTAINSTABLE(customers,(Address),’street’) AS key_search on customers.Id = key_search.[Key]
In this example I’am using the CONTAINSTABLE from Full-Text that looks at one column (or more) of one table and finds the word ‘ street’ .
This might be very helpful when having more than one word in the search and you want to sort by Rank score.
Now, if you want to search for the address that contains the word ‘street’ and it’s at the state ‘PA’. Then we need to break the string with AND’s or OR’s and order by ranking:
select customers.Name, customers.Address, key_search.rank as searchrank
FROM CUSTOMERS
INNER JOIN CONTAINSTABLE(customers,(address),'"street" and "PA"') AS key_search on customers.Id = key_search.[Key]
order by searchrank desc
Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). You must use whatever fits you better.
Conclusion
If you want to search by one or more words inside a string, you can now take advantage of the Full-Text Search. The only thing that remains is to talk about the outcome. After refactoring the application queries to use full-text search, the average performance per query improved significantly! In my experience, some queries went from timeout to a couple of seconds results.
Outsystems and SQL Full Text Search
And now, the awaited question: How does this fit in Outsystems?
The not so good part about SQL Full Text Search and Outsystems is that you still need to develop Advanced Queries and cannot do this in Aggregates. So, It’s time to invest a bit into T-SQL and learn (it will help you in the future to better understand data and even make optimal aggregates!)
The good part is that you don’t need to add anything new to Outsystems (besides the queries of course). Since it’s on the database level, it’s ready to be used!
