![]() Building a terms table – and have people check boxes to pick from common terms, or have your app examine their search string to redirect over to the terms table rather than use full text search. ![]() In other situations, I’ve had to point folks toward: I wanna stress, though, that that isn’t a solution that works in the majority of shops. In this particular example, a like with a leading % sign – normally a DBA’s enemy – is actually a performant solution. SQL Server will choose to perform the full text search across all of the rows regardless of your other filters, then drag a ton of data back into the execution plan and force the rest of the operators to deal with it. However, the more complex your query plan becomes, like the more filtering that’s being done on other tables, AND the more common your search keywords are, the angrier you’ll become with full text performance. It brings powerful capabilities to SQL Server built right into the box. If you have a really simple query and you’re filtering for very unusual keywords, then full text search is pretty dang good. Full text search doesn’t integrate well in query plans. That finishes in less than a second, with 2,426 logical reads. To illustrate it, let’s say I’m using the Stack Overflow database, and I want to optimize this query: By the time your data grows to real production sizes, full text can get uncomfortably painful. This isn’t a big deal for small text data sets, like under 10GB, but as your data grows linearly, your query time goes up linearly too. ![]() When your query uses CONTAINS, SQL Server has a nasty habit of doing a full text search across all of the rows in the table rather than using the rest of your WHERE clause to reduce the result set first. That works at small scale, but as your data grows, you run into a query plan performance problem. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings, thinking it’s going to be faster than LIKE ‘%mysearch%’. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. SQL Server’s full text search is amazing. ![]()
0 Comments
Leave a Reply. |