In SQL Server database I have a View with a lot of INNER JOINs statements. The last join uses LIKE predicate and that's why it's working too slowly. The query looks like :
SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON X.Name LIKE '%' + W.Name + '%' AND
LIKE '%' + W.Name2 + '%' AND
LIKE '%' + W.Name3 + '%'
I want to use CONTAINS instead of LIKE as :
SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON CONTAINS(X.Name, W.Name) AND
CONTAINS(X.Name, W.Name2) AND
CONTAINS(X.Name, W.Name3)
I know that CONTAINS is working faster than LIKE and also that can't use CONTAINS in JOIN statements. Is there any workaround in this case or suggestion? Thanks in advance.
4条答案
按热度按时间plicqrtu1#
It's not that
CONTAINS
can't be used in joins.You just can't use columns as a second parameter of
CONTAINS
- see MSDN - CONTAINS (Transact-SQL)However, you can use a variable as a search condition, so you can use a cursor and then get all data you need. Here is some very rough example:
0s0u357o2#
CONCAT
works perfect, I have tested it with PostgreSQLPlease refer to similar answer here
rqenqsqc3#
You can create a join using a LIKE.. something like this:
ie - select everything from TABLE_ONE where the string_column is contained in the TABLE_TWO name
dluptydi4#
In short there isn't a way to do this using CONTAINS, it simply is not allowed in a JOIN like this. see: TSQL - A join using full-text CONTAINS
So although there is performance hit, IMO like is the easiest solution here.