June 24, 2019 at 7:14 pm
I am using full text indexing for partial match and Today it started to be very slow when I use the following queries
It takes more than 1 min to return the results.
SELECT *
FROM
[Syn].[CustomerNamesSyn] AS Customer WITH(NOLOCK)
WHERE
CONTAINS(Customer.CustomerName, ‘ ”any input string *” ’)
The full text index is set on CustomerName column
I was using same query for more than a year and has not experienced such slowness before.
What could have went wrong?
June 24, 2019 at 9:00 pm
To start, you would probably want to check the full text log. That would list the last crawl and any errors that may have occurred. You would also want to do a quick check of sys.fulltext_indexes to see if the crawl completed, start and end times.
Sue
June 24, 2019 at 9:01 pm
Did you look at the execution plan to ensure that SQL was using the full text index?
a nice read on CONTAINS vs CONTAINSTABLE is here:
http://www.qdpma.com/CBO/FullTextQueryPlans.html
If it IS being used, my next thought is that you are not doing maintenance on the full text index and it is getting fragmented and causing performance issues.
My third thought is that your query is actually part of a stored procedure and that '"any input string *"' is an input to that stored procedure and you are being hit with parameter sniffing.
Microsoft also has some tips on how to improve query performance with full text queries:
A small thing that makes me a bit nervous with your query is that you have "WITH(NOLOCK)" in there. There are risks with using NOLOCK and in general, it is advised to avoid it where possible.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply