SQL Server 05, which is optimal, LIKE %<term>% or CONTAINS() for searching large column
- by Spud1
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?
