How to tune text queries

Question

How to tune text queries

AJ07

Ten Centuries

Points: 1334
More actions
February 21, 2011 at 12:10 pm

#247062

Hi,
I am trying to search the possibilities for tuning the query in which it goes for index scan.
select * from Contact where FirstName like 'a%'
As per business requirements many times we come across this situation, i am trying to know the possible solution to get the result in faster way as this query has an index scan. I have created a clustered index on this column.
Kindly suggest.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

AJ-636201 (2/21/2011)
Hi,
I am trying to search the possibilities for tuning the query in which it goes for index scan.
select * from Contact where FirstName like 'a%'
As per business requirements many times we come across this situation, i am trying to know the possible solution to get the result in faster way as this query has an index scan. I have created a clustered index on this column.
Kindly suggest.
Thanks

Well, like 'a%' is SARGable, so that's not your issue. You mention you've created a clustered index on this column, is it the 'leading edge' of the index, or the first column in the index?

Otherwise, it's going to depend on selectivity of the items to determine if it seeks or scans. After a certain point, SQL decides it's faster to just go through the entire table then to try to find its way through the B-Tree.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

AJ07 Ten Centuries Points: 1334 More actions · Answer 2

I have created only one clustered index and that is on FirstName column, and no other index has been created not even any non clustered index.

So is there any way where we can improve the query performance or can we rewrite such queries in different manner.

Sorry but i just missed the main point in the query its '%a'

SELECT * FROM Contact WHERE FirstName LIKE '%a'

Regards,

AJ

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

AJ-636201 (2/21/2011)
I have created only one clustered index and that is on FirstName column, and no other index has been created not even any non clustered index.
So is there any way where we can improve the query performance or can we rewrite such queries in different manner.
Regards,
AJ

I'd really need to see the query plan and ddl to tell you for sure, but that's about the best you'd usually get for a simple query like that. I'm surprised it's not seeking unless you're heavily weighted in a specific letter (IE: 20% of your FirstNames start with a).

Do you also get non-seeked results when you use two letters? IE: looking for Charlie you would try 'ch%'? Curious if you've got a plan locked in memory that did parameter sniffing and it's just stuck. Heh, on that note, is this actually written with the hard value in place in your SSMS or are you calling a proc that uses a parameter for the like position? If this has more wrappers and the like on it, it would help to see those as well.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

AJ-636201 (2/21/2011)
I have created only one clustered index and that is on FirstName column, and no other index has been created not even any non clustered index.
So is there any way where we can improve the query performance or can we rewrite such queries in different manner.
Sorry but i just missed the main point in the query its '%a'
SELECT * FROM Contact WHERE FirstName LIKE '%a'
Regards,
AJ

Oh, no, you're out of luck there, sorry. That'll never be able to seek.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

AJ07 Ten Centuries Points: 1334 More actions · Answer 5

Hey Thanks Creig,

So such a query can not be improved, there will always be an index scan.

So i think i got my answer:crying:

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 6

AJ-636201 (2/21/2011)
Hey Thanks Creig,
So such a query can not be improved, there will always be an index scan.
So i think i got my answer:crying:
Thanks

There is (at least) one option left:

You could add a computed persisted column with REVERSE(FirstName) and index this column.

Then you could use

SELECT * FROM Contact WHERE FirstNameReverse LIKE 'a%'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

AJ-636201 (2/21/2011)
Hey Thanks Creig,
So such a query can not be improved, there will always be an index scan.
So i think i got my answer:crying:
Thanks

Well, that's a new spelling... 🙂 Yeah, an index seeks based on the first character working in, so by wildcarding/skipping the first character, the B-Tree can't work.

There's a workaround, but it's not pretty, and it won't work with %a% in either case. What you do is reverse the column as a computed column, and index that.

So, you'd create something like:

ALTER TABLE Add COLUMN ReverseFirstName REVERSE( FirstName) PERSISTED

ADD NONCLUSTERED INDEX idx_reverseFirstName ON table (ReverseFirstName)

Then your proc would do this:

SET @parameter = REVERSE( @Parameter) + '%'

SELECT Firstname From table WHERE ReverseFirstName like @parameter.

It's not pretty but it's one of the few ways you can get this to behave with the necessary SARGability.

EDIT: LOL, Lutz beats me to it by a minute. 😉

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

AJ-636201 (2/21/2011)
I have created only one clustered index and that is on FirstName column, and no other index has been created not even any non clustered index.

That's not usually a good candidate for a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

srikant maurya Hall of Fame Points: 3293 More actions · Answer 9

You can rewrite the query but execution plan are same for both t-sql (index scan)

select * from contact where patindex('%a',first_name)>0

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

srikant maurya (2/21/2011)
You can rewrite the query but execution plan are same for both t-sql (index scan)
select * from contact where patindex('%a',first_name)>0

That approach will lead to scans instead of seeks, even if the search was on 'a%' because you're performing a function on the column.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

if you think that query should do an index seek and bookmark lookup to return your results, then do this:

SET STATISTICS IO ON

GO

select ... from mytable with (index=yourNCindexnamehere) where ...

show the actual execution plan too, and check out the ESTIMATED and ACTUAL rows

now compare that to what sql server does by default (table scan)

try other where clauses such as LIKE 's%'

I think you might be surprised by what you find...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 12

TheSQLGuru (2/23/2011)
if you think that query should do an index seek and bookmark lookup to return your results...

I think you missed the fact that there is only one index, and it is clustered.

I agree with Gail that this is a poor choice for the cluster.

Lutz and Craig have suggested a way to make the expression indexable, but I fear it went over AJ's head.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

AJ07 Ten Centuries Points: 1334 More actions · Answer 13

Hey thanks guys for you valuable inputs. I did the same what Craig & Lutz suggested and now I am getting the Index seek instead of scan. My query performance got improved.

I have just given example that I have created the Clustered index on VARCHAR column for the clarity in my question. In actual scenario I have a NON CLUSTERED index on my VARCHAR column.

SQLKivi this logic couldn't go over my head, my head caught it for forever :-).

Regards,

AJ

Paul White SSC Guru Points: 150468 More actions · Answer 14

AJ-636201 (2/24/2011)
SQLKiwi this logic couldn't go over my head, my head caught it for forever 🙂

Excellent. I love it when I am wrong about that sort of thing. :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi