All geek questions in one place

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

I have a function written by another developer that I am trying to change for a slightly different use. It is used by SP to check if a certain phrase exists in a text document stored in the database and returns 1 if the value is found or 0 if it is not. This is the request:

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

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

It seems to me that this is rather slow for me (it takes 5-6 seconds to complete this part of the process), but in the same script file there is also this version above, commented out.

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

This version is much faster, taking 4 ms compared to 15 ms for the first example.

So my question is why use the first after the second? I assume that this developer (who no longer works with me) had a good reason, but at the moment I'm trying my best to find it.

Perhaps this is due to the full indexing of the text? (This is the debut DB I'm working with, so the production version may have better indexing.) I'm not looking so confidently at FTI, so I'm not quite sure at the moment. Thoughts / ideas?

+4
source share
2 answers

Update: Aggg - My whole answer is incorrect!

Yes, CONTAINS does use full-text search (see http://msdn.microsoft.com/en-us/library/ms187787.aspx ) and therefore should be fast (or at least it should scale correctly)

The reason the second version (using LIKE ) may be faster is because your table does not contain many rows. Calling a full text search engine will result in a little extra overhead, which may mean that using LIKE for small tables is much faster.

On the other hand, if the top query takes 5-6 seconds, then I would say that something is probably somewhere wrong - try looking at the execution plan again.

+1
source

There will always be a case when one option will be faster than another, and vice versa. Your best option is to check the same production request and see the difference. For this kind of performance testing, I would recommend using a profiler and execute each request several times, and compare on average

0
source

Source: https://habr.com/ru/post/1304244/

More articles:


All Articles