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:
Using aforesaid query, Index scan was using by execution plan & taking around 25 seconds Then I modified query with below
Do you have any idea to boost it (except database/table partitioning)?
Best Regards,
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%'
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%')
Do you have any idea to boost it (except database/table partitioning)?
Best Regards,
Microsoft SQL Server
Last Comment
Vitor Montalvão
ASKER CERTIFIED SOLUTION
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.
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,
@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?
ASKER
Yes, Col3 is having non-clustered index and Col1 & Col2 (as included i.e. covering index)
Best Regards
Best Regards
if you only need col1, col2 and you can create a new index on col3, col1, col2, it will be super fast :)
ASKER
I did try with CHARINDEX. But still, it is taking same time.
Best Regards
Best Regards
SOLUTION
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.
SOLUTION
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.
ok, try this
create a function index, say
rtrim(col)
or one of those
ltrim(col)
ltrim(rtrim(col))
then use this query
and lets see if it helps...
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%'
and lets see if it helps...
Recommendation to close this question by accepting the above comments as solution.
(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