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,
LVL 6
MohitPanditAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:
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
0
MohitPanditAuthor 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,
0
Determine the Perfect Price for Your IT Services

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

Get Your eBook
HainKurtSr. System AnalystCommented:
what are your indexes? do you have an index on Col3?
0
MohitPanditAuthor Commented:
Yes, Col3 is having non-clustered index and Col1 & Col2 (as included i.e. covering index)

Best Regards
0
HainKurtSr. System AnalystCommented:
if you only need col1, col2 and you can create a new index on col3, col1, col2, it will be super fast :)
0
MohitPanditAuthor Commented:
I did try with CHARINDEX. But still, it is taking same time.

Best Regards
0
HainKurtSr. System AnalystCommented:
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...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
HainKurtSr. System AnalystCommented:
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...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.