Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are there any Sql Server Full-Text Search (FTS) performance improvements since version 2008 R2?

we're using SQL Server 2008 R2 Full-Text Search over a table with 2.6 million records. The search performance often is poor, it follows the commonly reported pattern: cold system/first run ~10+ sec, subsequent runs ~1-2 sec. This is inline with results reported in the following article dated of Feb, 2013:

So You Think You Can Search – Comparing Microsoft SQL Server FTS and Apache Lucene

The article shows the following speed comparison results using Wikipedia dump data:

Indexing speed, size and single query execution time using:

                        Lucene      MS SQL FTS
Indexing Speed          3 MB/sec    1 MB/sec
Index Size              10-25%      25-30%
Simple query            < 20 ms     < 20 ms
Query With Custom Score < 4 sec     > 20 sec
Parallel Query Executions (10 threads, average execution time per query in ms):

                                     MS SQL FTS  Lucene (File System)   Lucene (RAM)
Cold System:         Simple Query    56          643                    21
                     Boost Query     19669*      859                    27
Second executions:   Simple Query    14          8                      < 5
                     Boost Query     465         17                     9

*average time, the very first query could be executed up to 2 min(!)

My questions are:

  1. Since there were several major SQL Server releases since the article was published on Feb 8, 2013, can someone report any FTS performance improvements over same data (preferably of 1+ million records) when they migrated to more recent SQL Server versions (2012, 2014 and 2016)?

  2. Do more recent SQL Server versions support FTS catalogs/indexes placed in RAM just as solr/lucene do?

UPDATE: in our scenario we seldom insert new data into FT catalog linked table, but run read only searches very often. So, I don't think SQL constantly rebuilding FTS index is the issue.

like image 442
andrews Avatar asked Sep 23 '16 13:09

andrews


People also ask

How do I make SQL Server search faster?

If you search the beginning of each word though with your search parameter then FTS can give you the speed you need. Example: @search with value Win should match address "123 Window lane" but not "123 LikWin".

How can I tell if full text search is enabled in SQL Server 2019?

How can I tell if Full-Text Search is enabled on my SQL Server instance? A: You can determine if Full-Text Search is installed by querying the FULLTEXTSERVICEPROPERTY like you can see in the following query. If the query returns 1 then Full-Text Search is enabled.

What is Full Text Search vs LIKE?

Like uses wildcards only, and isn't all that powerful. Full text allows much more complex searching, including And, Or, Not, even similar sounding results (SOUNDEX) and many more items.

How does full text search work in SQL Server?

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on the rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.


1 Answers

Fulltext Search Improvements in SQL Server 2012:

We looked at the entire code base from how queries block while waiting an ongoing index update to release a shared schema lock, from how much memory is allocated during index fragment population, to how we could reorganize the query code base as a streaming Table Value Function to optimize for TOP N search queries, how we could maintain key distribution histograms to execute search on parallel threads, all the way to how we could take better advantage of the processor compute instructions (scoring ranks for example)… End result is that we are able to significantly boost performance (10X in many cases when it comes to concurrent index updates with large query workloads) and scale without having to change any storage structures or existing API surface. All our customers going from SQL 2008 / R2 to Denali will benefit with this improvement.

like image 139
TheGameiswar Avatar answered Nov 15 '22 09:11

TheGameiswar