Link to home
Start Free TrialLog in
Avatar of MohitPandit
MohitPanditFlag 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,
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of 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,
what are your indexes? do you have an index on Col3?
Yes, Col3 is having non-clustered index and Col1 & Col2 (as included i.e. covering index)

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

Best Regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
Recommendation to close this question by accepting the above comments as solution.