用于搜索大型列的最佳SQL Server 05，LIKE％<term>％或CONTAINS（） [英] SQL Server 05, which is optimal, LIKE %<term>% or CONTAINS() for searching large column
问题描述
我有一个由另一个开发人员编写的函数，我试图修改其中的一个稍微不同的用法。 SP用它来检查DB中存储的文本文档中是否存在某个短语，如果找到该值，则返回1;否则返回0。这是查询：
SELECT @ mres = 1其中id = @ DocumentID
且包含（text， @search_term）
该文档主要包含XML，而search_term是格式化为nvarchar（40 ）。
这对我来说似乎运行得非常慢（需要5-6秒来执行这部分过程），但是在同一个脚本文件中也有这个
SELECT @ mres = 1 from id = @ DocumentID
and textlike '％'+ @search_term +'％'
此版本运行速度更快，第一个例子。
所以，我的问题是为什么第一个使用第一个？我认为这个开发者（谁不再和我一起工作）有一个很好的理由，但目前我正在努力寻找它。
使用全文索引？ （这是一个我正在使用的开发数据库，所以生产版本可能有更好的索引..）我并不是那么关注FTI，所以目前还不太确定。想法/想法？
更新： Aggg - 我的答案都是错的！
是的， CONTAINS 确实使用全文搜索（请参阅 http://msdn.microsoft.com/en-us/library/ms187787.aspx ），所以应该很快（或至少它应该适当缩放）
第二个版本（使用 LIKE ）可能更快的原因是if你的表不包含很多行 - 调用全文引擎进行搜索会产生一个额外的额外开销，这可能意味着对于小型表使用 LIKE 稍微快一些。另一方面，如果顶部查询需要5到6秒钟才能执行，那么我会说某处可能有错误 - 再次尝试查看执行计划。
/ p>
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?
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.
这篇关于用于搜索大型列的最佳SQL Server 05，LIKE％<term>％或CONTAINS（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
