We help IT Professionals succeed at work.

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

MohitPandit
MohitPandit asked
on
71 Views
Last Modified: 2017-05-07
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,
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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,
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what are your indexes? do you have an index on Col3?

Author

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

Best Regards
HainKurtSr. System Analyst
CERTIFIED EXPERT

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

Author

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

Best Regards
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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