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

MohitPandit
MohitPandit used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
IT Engineer
Distinguished Expert 2017
Commented:
When you use LIKE '%something%' it will performindex scan instead of seek. If you get rid of the first '%' then it will perform an index seek but then it might not be what you really want, right?
My recommendation is to try to add any other column for filtering, like a date or something similar that will reduce the number of scanned records.
HainKurtSr. System Analyst

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
MohitPandit

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,
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
HainKurtSr. System Analyst

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

Author

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

Best Regards
HainKurtSr. System Analyst

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

Author

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

Best Regards
HainKurtSr. System Analyst
Commented:
as said before, try to add some more filter on indexed columns... like

active=1
status=1
createdate between getdate() and getdate()-60

etc...

can you please give the real query... maybe we can advice something else...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
I did try with CHARINDEX. But still, it is taking same time.
That was what I explained in my first comment. Only way to have a better performance is to use indexes and avoid the use of functions on WHERE clause.
HainKurtSr. System Analyst

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
Distinguished Expert 2017

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial