Avatar of MohitPandit
MohitPandit
Flag for India asked on

SQL Server 2014: Index Seek is taking more than 20 seconds (using LIKE '%Value%')

Hello,

I've a table with half million rows. I want to fetch records based on below query:

SELECT Col1, Col2
FROM Table
WHERE Col3 LIKE '%Value%'

Open in new window


Using aforesaid query, Index scan was using by execution plan & taking around 25 seconds Then I modified query with below
SELECT Col1, Col2
FROM Table
WHERE (Col3 IS NOT NULL And Col3 LIKE '%Value%')

Open in new window

After that it was using Index Seek. But still take 20 seconds (5 seconds saved, but need in less than 1 second). FYI, it returns around 100 thousand records after 20 seconds.

Do you have any idea to boost it (except database/table partitioning)?


Best Regards,
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
HainKurt

try

(Col3 IS NOT NULL And CHARINDEX ('Value', Col3)>0)

may perform better...

see http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex
MohitPandit

ASKER
Thanks for your valuable comment.

@Vitor - no other filters
@Huseyin - Okay, I'll try over production. BTW, roughly check, CHARINDEX statement is also using Index Seek.

Best Regards,
HainKurt

what are your indexes? do you have an index on Col3?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
MohitPandit

ASKER
Yes, Col3 is having non-clustered index and Col1 & Col2 (as included i.e. covering index)

Best Regards
HainKurt

if you only need col1, col2 and you can create a new index on col3, col1, col2, it will be super fast :)
MohitPandit

ASKER
I did try with CHARINDEX. But still, it is taking same time.

Best Regards
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
HainKurt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
HainKurt

ok, try this

create a function index, say

rtrim(col)

or one of those

ltrim(col)
ltrim(rtrim(col))

then use this query

SELECT Col1, Col2
FROM Table
WHERE rtrim(Col3) LIKE '%Value%'

Open in new window


and lets see if it helps...
Vitor Montalvão

Recommendation to close this question by accepting the above comments as solution.