SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimize a LIKE '%text%' query


Optimize a LIKE '%text%' query

Author
Message
ralu_k_17
ralu_k_17
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 494
Hello!

Is there a way of optimizing(/workaround) the performance of a LIKE '%text%' query, on a non-unique column (so, no full text index possible)? Both wildcards are required.

Thank you for any input!
Y.B.
Y.B.
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9404 Visits: 2666
Well the leading wildcard in that LIKE clause will guarantee it will have to do full scans. Something LIKE 'text%' is fine though. I guess it comes down to your requirements.


SELECT quote FROM brain WHERE original = 1
0 rows returned
ralu_k_17
ralu_k_17
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 494
Unfortunately, both wildcards are required. I will update my initial post with this info, as well.
Jonathan AC Roberts
Jonathan AC Roberts
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 4390
Alternatives are:
select * from myTable x
where x.Description like '%text%'
select * from myTable x
where PATINDEX('%text%',x.Description)>0
select * from myTable x
where CHARINDEX('text',x.Description)>0


I doubt there is much difference between them though you would think that CHARINDEX has less logic in so it should be faster, but I don't think that's the case.
ralu_k_17
ralu_k_17
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 494
Thank you for your reply!
Interestingly enough, if I run the second option (PATINDEX) I see a 3sec improvement, which is huge, because the initial run was 4-5sec and this is about 1-2sec.
The problem is, if I put just this query (PATINDEX) , with absolutely no other statements, into a sp and send the %text% as a parameter to the sp, it goes back to 5sec
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)SSC Guru (396K reputation)

Group: General Forum Members
Points: 396797 Visits: 43207
Capture and compare the actual execution plans between the various options.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jason A. Long
Jason A. Long
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20033 Visits: 7982
ralu_k_17 - Friday, November 9, 2018 6:52 AM
Hello!

Is there a way of optimizing(/workaround) the performance of a LIKE '%text%' query, on a non-unique column (so, no full text index possible)? Both wildcards are required.

Thank you for any input!

It depends... There is a method that works but it requires that you maintain an "N-grams" table of your values.

Check out the following two articles...
http://www.sqlservercentral.com/articles/Tally+Table/142316/
https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85854 Visits: 9660
Create a nonclustered index using the clustering column(s) as key(s) and INCLUDE the search column. That's about the best you can do with this type of requirement.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110258 Visits: 14759
I don't know your database, but it could be argued that if users must routinely perform LIKE '%keyword%' queries on text columns just to locate the rows they are looking for, then the data model is not appropriately normalized. For example, if users are querying WHERE CustomerName LIKE '%Smith%', then there should definitely instead have FirstName and LastName columns. Or, if they are querying WHERE ProductName LIKE '%laptop%' then that suggests a ProductCategoryID column and ProductCategory table should be introduced. So, the best solution depends on the context of why they are needing to perform this text searching.

As someone suggested earlier, one approach is to parse the contents of the text name/description column into a keyword lookup table similar to the example below. Another benefit to implementing a search keyword lookup table is that the Keywords table can be extended to contain other attributes like a count of how times the keyword was searched and additional columns for categorization and semantic type searching. For example, all of the laptop, TV, phone, etc. related keywords could be assigned to a category called 'electronics'.


CREATE TABLE Products (
PRIMARY KEY CLUSTERED ( ProductID )
, ProductID INT NOT NULL
, ProductName VARCHAR(500) NOT NULL );

CREATE TABLE ProductKeywords (
PRIMARY KEY CLUSTERED ( KeywordID, ProductID )
, KeywordID INT NOT NULL
, ProductID INT NOT NULL );

CREATE TABLE Keywords (
PRIMARY KEY CLUSTERED ( Keyword )
, KeywordName VARCHAR(200) NOT NULL
, KeywordID INT NOT NULL
, CategoryID INT NOT NULL
);

-- Query Products by keyword:
SELECT P.ProductID, P.ProductName
FROM Keywords AS K
JOIN ProductKeywords AS PK ON PK.KeywordID = K.KeywordID
JOIN Products AS P ON P.ProductID = PK.ProductID
WHERE K.KeywordName = 'laptop';

-- Query Products by category:
SELECT P.ProductID, P.ProductName
FROM Keywords AS K
JOIN ProductKeywords AS PK ON PK.KeywordID = K.KeywordID
JOIN Products AS P ON P.ProductID = PK.ProductID
WHERE K.CategoryID = 12;



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)

Group: General Forum Members
Points: 885082 Visits: 47972
ralu_k_17 - Friday, November 9, 2018 6:52 AM
Hello!

Is there a way of optimizing(/workaround) the performance of a LIKE '%text%' query, on a non-unique column (so, no full text index possible)? Both wildcards are required.

Thank you for any input!


1. Does the table have a PK?
2. How wide is the text data and can you provide an example so we can see what you're actually having to work with?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search