Additional index needed?

Question

Additional index needed?

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

As Item_Num is not on the leading edge of your index, it could benefit from having an additional index added.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Queries that filter on End_dt alone can seek on the primary key index. Queries that filter on End_dt and on ITEM_NUM can seek on the primary key index. Queries that filter on ITEM_NUM alone cannot (it's not a left-based subset of the index key)

If you have queries that filter on ITEM_NUM alone, you'll need an index on ITEM_NUM.

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

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

Ninja's_RGR'us

SSC Guru

Points: 294069

July 28, 2011 at 10:21 am

#1360292

Edit : Just repeated what others have said.

BAGs Right there with Babe Points: 793 More actions · Answer 4

Thank you Gail and SSCrazy for the advice.

As far as the additional request -

THere could be 5m rows

156 dates (3 yrs)

50,000 different items.

The query distribution is estimated at

1/3 combined - SKU/WK

1/3 - SKU only

1/3 - DATE only

steveb. SSC-Forever Points: 46733 More actions · Answer 5

PeterG-377490 (7/28/2011)
Thank you Gail and SSCrazy for the advice.
As far as the additional request -
THere could be 5m rows
156 dates (3 yrs)
50,000 different items.
The query distribution is estimated at
1/3 combined - SKU/WK
1/3 - SKU only
1/3 - DATE only

IF SKU = Item_Num,

then it is a good candidate for an index.

BAGs Right there with Babe Points: 793 More actions · Answer 6

thanks Steve.......didn't mean to call you CRAZY!!!! Picked up the wrong name.

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

With that distribution I'd really go with PK : SKU, endt

Then clustered on the date.

Your PK will be way more selective that way.

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

PeterG-377490 (7/28/2011)
thanks Steve.......didn't mean to call you CRAZY!!!! Picked up the wrong name.

Not the first time it happens! Don't sweat it 😉

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Ninja's_RGR'us (7/28/2011)
With that distribution I'd really go with PK : SKU, endt
Then clustered on the date.

Agreed.

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