CONVERT IMPLICIT on a datetime causing index scan instead of seek

Question

Post reply

CONVERT IMPLICIT on a datetime causing index scan instead of seek

Viewing 10 posts - 16 through 24 (of 24 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

matt stockham (4/24/2008)
Your index is on mydate, col1, col2, col3 - in that order?

No, I did not include mydate ... but yes the index is in the same order as the select.

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

Oh, then it's not a covering index. Try adding the date column. It will be then.

"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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

Because the index doesn't cover the entire query, it is faster to scan the clustered index to get the information. Add mydate as the first column and it will be able to seek on that index (if you add it in another position then it will scan your index - probably still faster than a clustered index scan).

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Ok ... that's crazy ... because I've been modifying stuff quite a bit and I don't think the example I posted was the best bet ...

Anyways, one of the pieces of one of these procedures, was doing an index scan still, even though the index did in fact include the date, in the order in which it was selected. The where clause was on the date in this case, but as soon as I put the date in the first spot on the index, it is now seeking and about 700% faster ...

I don't get this? Why does the where clause need to be the first entry in the index, even though it's selected out in a different order later in the select?

Thanks guys!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Same concept here ... scanning instead of seeking, no where clause, but on a join:

SELECT

NameType

,LastName

,FirstName

,MiddleName

,EndName

,NameKey

FROM tbl1 t1

INNER JOIN tbl2 t2

ON t1.NameKey = t2.NameKey

I've tried my index two ways:

CREATE NONCLUSTERED INDEX [IX_1] ON [tbl1]

(

NameKey ASC

,NameType ASC

,LastName ASC

,FirstName ASC

,MiddleName ASC

,EndName ASC

)

and

CREATE NONCLUSTERED INDEX [IX_2] ON [tbl1]

(

NameType ASC

,LastName ASC

,FirstName ASC

,MiddleName ASC

,EndName ASC

,NameKey ASC

)

Yet both result in a scan ... From your last posts, I've learned that my where clause needs to be in position one, but what about when I'm joining? I tried the same concept by using the joined column in first position, but that did not help.

Thanks again!

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 6

Using the phone book analogy, you are searching for people with a first name of 'Bob' but the book is listed by last name then first name - you have to go through every entry in the book to find them (a scan). If the book was sorted by first name, last name then you would be able to go direct to the 'Bob' section (a seek).

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 7

As for your other question - having namekey in the first position is correct, but then other factors come into play. The optimiser knows that the index is a covering index so it *can* be used, but it then takes into account the number of reads that are (estimated to be) needed. It may have decided to do a nested loop join with table 1 being the outer table - in this case it has to read all the records anyway so it does a scan. Looking at the execution plan will give you this information.

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

Adam Bean (4/24/2008)
Same concept here ... scanning instead of seeking, no where clause, but on a join:
SELECT
NameType
,LastName
,FirstName
,MiddleName
,EndName
,NameKey
FROM tbl1 t1
INNER JOIN tbl2 t2
ON t1.NameKey = t2.NameKey
I've tried my index two ways:
CREATE NONCLUSTERED INDEX [IX_1] ON [tbl1]
(
NameKey ASC
,NameType ASC
,LastName ASC
,FirstName ASC
,MiddleName ASC
,EndName ASC
)
and
CREATE NONCLUSTERED INDEX [IX_2] ON [tbl1]
(
NameType ASC
,LastName ASC
,FirstName ASC
,MiddleName ASC
,EndName ASC
,NameKey ASC
)
Yet both result in a scan ... From your last posts, I've learned that my where clause needs to be in position one, but what about when I'm joining? I tried the same concept by using the joined column in first position, but that did not help.
Thanks again!

And what do the indexes look like on tbl2 in this example above? The data being returned in the select, which tables are they from (with multi-table queries you should alias the columns even if they are unique to the tables)?

😎

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

Lynn Pettis (4/25/2008)

Adam Bean (4/24/2008)
And what do the indexes look like on tbl2 in this example above? The data being returned in the select, which tables are they from (with multi-table queries you should alias the columns even if they are unique to the tables)?
😎

They are aliased in my procs ... this was just a quick sample of my procedure ... wasn't thinking.

Anyways, for this particular piece, on tbl2, NameKey is indexed.

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

I think I'm losing track of the thread. Where are you with this? Have you tried changing the indexes in any of the ways that have been proposed? Have any of them made a difference?

"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