Eliminating Index Scans

Question

Eliminating Index Scans

Ben Sullins-437405

SSCertifiable

Points: 5397
More actions
May 23, 2007 at 11:40 am

#117324

Greetings everone,
I am needing to get my query to perform faster and I based on the execution plan I have an Index Scan that is taking up 98% of the execution time. Any idea's on how to change this to an Index Seek in the query below?
/***
select distinct OrderNumber, Comment
from table1
where left(isnull(ltrim(rtrim(Comment)),''),3) = 'CI:'
***/
Thanks,
Ben
Cheers,
Ben Sullins
bensullins.com
Beer is my primary key...

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I don't see how you can eliminate the index scan (assuming you are indexing on the Comment field) unless you create a computed column based on the first three characters of the Comment field and then build an index on the computed column.

I think this is possible in SQL 2000, haven't have a need to.

Also, you could try; WHERE Comment like 'CI:%'

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 2

Thanks Lynn,

I started out using 'like' but had the same result.

Ugh...I really hate when business process folks decide how data should be stored in the application w/o consulting any of DB folks that have to actually work w/ it.

/vent

-Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Nothing forbids you to change the indexing, and maybe not even how the data is stored. Never forget that you are the data keeper of the company!

Sergiy SSC Guru Points: 110208 More actions · Answer 4

It's probably the time for you to refresh memory about normalisation rules, especially the 1st one.

But if you'll remove leading spaces from "Comment" when you save it you could use this:

select distinct OrderNumber, Comment

from table1

where Comment LIKE 'CI:%'

This will use index seek.

_____________
Code for TallyGenerator

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 5

Sergiy,

There are no leading spaces in the field I just put that in out of habbit. The query you mention is what I used originally and I am getting an index scan versus an index seek.

Sorry

-Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Sergiy SSC Guru Points: 110208 More actions · Answer 6

If there are no leading spaces then there is no need for LTRIM. RTRIM is useless by definition. As well as ISNULL.

You doin't need LEFT because LIKE will take 1st 3 symbols anyway.

Which index it scans?

Do you actually have an index on column Comments?

How many lines you've got in the table?

Because I just ran same query against one of my tables and it did index seek.

_____________
Code for TallyGenerator

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 7

no index on this field (why would you put on comments field?)

4.2M records in table

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Since you are using the field in a where clause, it makes sense to index the field.

John Rowan SSC Guru Points: 56440 More actions · Answer 9

If you do not have an index on the Comments column, SQL Server usaully goes to the clustered index or primary key and scans that. An index scan of this nature is basically the same as a table scan as SQL Server must transverse through the entire index to find the result set. You must put an index on Comments if you expect this query to get any faster.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 10

ah....makes sense, will give it a shot

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...