1

Is there any other way I can write the query for avoiding using "LIKE" operator, as LIKE usage effects Performance while searching large amount of data.

My Table: 

Products
--------
Id int
ProductName NVARCHAR(255)

Qry: Seraching for products which contains word "EBS" 

select * from Products where name like 'EBS%'

A full-text index is placed on the Products table. For few products im not able to retrieve data from PRODUCTS Table thru full-text index.

select *
from Products 
WHERE contains(ProductName,@Contains)
Order by    MT.Name

-- No Results for the above qry select * from Products where name like 'EBS%' ( Poor performance) so there is anyother way to implement this.

Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
SESHI
  • 33
  • 2
  • 7

3 Answers3

0

Full Text works on full words (tokens), or prefix tokens at best, but not on arbitrary partial matches. For 'contains arbitrary string' there is no workaround, only a slow scan using LIKE. Usually the solution is to go to the requirement stakeholders and push back on unjustified demands.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

You can build a regular index on the name field. SQL Server will use an index when the pattern starts with a constant string.

So, this will use the index:

select *
from Products
where name like 'EBS%';

This will not:

select *
from Products
where name like '%EBS%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

on SQL Server this could fit:

select * from Products where LEFT(name,3) = 'EBS'
Gerdinho
  • 3
  • 1