我如何优化/重构TSQL"LIKE"?条款? [英] How can I optimize/refactor a TSQL "LIKE" clause?

问题描述

我有一个包含117000左右记录的表.我需要执行搜索，以检查给定字符串模式的3个单独字段.

I have a table with 117000 or so records. I need to perform a search that checks 3 separate fields for a given string pattern.

我的where子句如下:

My where clause is as follows:

field1 LIKE '%' + @DESC + '%'
OR field2 LIKE '%' + @DESC + '%'
OR field3 LIKE '%' + @DESC + '%'

这似乎要花费大约24秒，而不管输入内容是什么.

This seems to take about 24 seconds regardless of input...

是否有更好的方法可以做到这一点?少于10(或5！)秒会更可取.

Is there a better way to do this? Less than 10 (or 5!) seconds would be much more preferable.

感谢您的帮助.

推荐答案

使用全文本搜索和

Use Full Text Search and CONTAINS. LIKE cannot be optimized when searching in the middle of the field, ie. when the LIKE expression starts with an '%', so it will always do a full table scan.

这篇关于我如何优化/重构TSQL"LIKE"?条款?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！