SQL Server 05, which is optimal, LIKE %<term>% or CONTAINS() for searching large column

Question

I've got a function written by another developer which I am trying to modify for a slightly different use. It is used by a SP to check if a certain phrase exists in a text document stored in the DB, and returns 1 if the value is found or 0 if its not. This is the query:

SELECT @mres=1 from documents where id=@DocumentID
 and contains(text, @search_term)

The document contains mostly XML, and the search_term is a GUID formatted as an nvarchar(40).

This seems to run quite slowly to me (taking 5-6 seconds to execute this part of the process), but in the same script file there is also this version of the above, commented out.

SELECT @mres=1 from documents where id=@DocumentID
and textlike '%' + @search_term + '%'

This version runs MUCH quicker, taking 4ms compared to 15ms for the first example.

So, my question is why use the first over the second? I assume this developer (who is no longer working with me) had a good reason, but at the moment I am struggling to find it..

Is it possibly something to do with the full text indexing? (this is a dev DB I am working with, so the production version may have better indexing..) I am not that clued up on FTI really so not quite sure at the moment. Thoughts/ideas?

Answer 1

Update: Aggg - all of my answer is wrong!

Yes, CONTAINS is indeed using full-text search (see http://msdn.microsoft.com/en-us/library/ms187787.aspx) and so should be quick (or at least it should properly scale)

The reason why the second version (using LIKE) could be quicker is if your table doesn't contain many rows - invoking the full text engine for searches will incur a small additional overhead which might mean that using LIKE is marginally quicker for small tables.

On the other hand if the top query is taking 5-6 seconds to execute then I'd say that something is probably wrong somewhere - again try looking at the execution plan.

Answer 2

There will always be case where one option would be faster than the other, and vice-versa. Your best option would be to test the same query on production, and see the difference. For this kind of performance testing, I would recommend using profiler, and running each query multiple times, and taking an average to compare