What is the perfect Index , in this special case

Question

What is the perfect Index , in this special case

Abhilash T

Mr or Mrs. 500

Points: 516
More actions
June 3, 2012 at 12:53 am

#257243

Hi All,
I have a table with lots columns(all columns not included here).
CREATE TABLE DBO.DAYSPECIALS(
DAYSPECAILSKEY BIGINT ,---PK FIEL
........ --SOME INFO COUMNS
ISAPPLIEDFORSPECIFICDAYS BIT,
ISSUNDAY BIT,
ISMONDAY BIT,
ISTUESDAY BIT,
ISWEDNESDAY BIT,
ISTHURSDAY BIT,
ISFRIDAY BIT,
ISSATURDAY BIT)
And there is one search by a Stored Procedures
@dt=datepart (dw,GETDATE())
........
ISAPPLIEDFORSPECIFICDAYS =1 AND (
(ISSUNDAY=1 AND @dt) OR
(ISMONDAY=1 AND @dt) OR
(ISTUESDAY=1 AND @dt) OR
(ISWEDNESDAY=1 AND @dt) OR
(ISTHURSDAY=1 AND @dt) OR
(ISFRIDAY=1 AND @dt) OR
(ISSATURDAY=1 AND @dt) )
what will be the perfect index for this search?
Thanks
🙂

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

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

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

Please post the entire procedure, the portion that you posted is full of syntax errors and I'm not sure exactly what you're doing.

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

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 2

Declare @Weekdays Mytable(DAYSPECAILSKEY bigint)

Declare @dt int =datepart (dw,GETDATE())

insert into @Weekdays

Select DAYSPECAILSKEY from

DBO.DAYSPECIALS where ISACTIVE=1 and

(

ISAPPLIEDFORSPECIFICDAYS =1 AND (

(ISSUNDAY=@DT) OR

(ISMONDAY= @dt) OR

(ISTUESDAY= @dt) OR

(ISWEDNESDAY= @dt) OR

(ISTHURSDAY= @dt) OR

(ISFRIDAY= @dt) OR

(ISSATURDAY= @dt) )

)

......

This is the search part of the snippet.

My real doubt is , we can put index if ISAPPLIEDFORSPECIFICDAYS=1 and (ISSUNDAY= @dt) as

ISAPPLIEDFORSPECIFICDAYS,ISSUNDAY.

I don't know how will put when the condition comes with OR condition

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

That's very different from what you initially posted.

For that collection of ORs, see http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

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

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 4

Abhilash T

Mr or Mrs. 500

Points: 516

June 3, 2012 at 4:14 am

#1496271

Thanks a lot.

Its helpfull

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Abhilash T (6/3/2012)
Hi All,
I have a table with lots columns(all columns not included here).
ISSUNDAY BIT,
ISMONDAY BIT,
ISTUESDAY BIT,
ISWEDNESDAY BIT,
ISTHURSDAY BIT,
ISFRIDAY BIT,
ISSATURDAY BIT)

Why not just have one column for the weekdays and assign a bit mask value for each day like so

Sunday 1

Monday 2

Tuesday 4

Wednesday 8

Thursday 16

Friday 32

Saturday 64

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 6

yes Right,

Bu I couldn't change it. Bcz its live and lot of developments done with this.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Abhilash T (6/3/2012)
yes Right,
Bu I couldn't change it. Bcz its live and lot of developments done with this.

I'm not sure that it's even worth trying to put an index on this because all of the columns only have 2 values.

Although you good folks have had a "lot of developments done with this", I'd suggest a taking a different approach. The problem is that none of us know what or why you're trying to do this and, I can't speak for the others, but I can't even begin to make an alternate suggestion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Abhilash T (6/3/2012)
yes Right,
Bu I couldn't change it. Bcz its live and lot of developments done with this.

hmm, seems a bit daft to me to be honest. What if someone updates more than one column and sets the bit flag, have you allowed for this? 😉

You might want to consider it

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Jeff Moden (6/3/2012)
Abhilash T (6/3/2012)
yes Right,
Bu I couldn't change it. Bcz its live and lot of developments done with this.
I'm not sure that it's even worth trying to put an index on this because all of the columns only have 2 values.

A covering index can be very valuable, a non-covering maybe not. Either way, the index will be useless if just on the bit column as there's more than just the ORs in the where clause, so when assessing the indexes over all columns, there won't be just 2 or 3 values.

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

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

Perry Whittle (6/3/2012)
What if someone updates more than one column and sets the bit flag, have you allowed for this? 😉

That might be perfectly valid. If it is a table listing what days special offers are valid on then Monday, Tuesday and Thursday may be perfectly acceptable. We don't know.

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

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 11

Thanks for the hint "COVERING INDEX". This I tried, got improvements. I have added the some selected columns too in other tables also. Much faster now.

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

You did read the blog post I hope. It has more than hints for indexing this type of query.

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 13

Abhilash T (6/3/2012)
Hi All,
I have a table with lots columns(all columns not included here).
CREATE TABLE DBO.DAYSPECIALS(
DAYSPECAILSKEY BIGINT ,---PK FIEL
........ --SOME INFO COUMNS
ISAPPLIEDFORSPECIFICDAYS BIT,
ISSUNDAY BIT,
ISMONDAY BIT,
ISTUESDAY BIT,
ISWEDNESDAY BIT,
ISTHURSDAY BIT,
ISFRIDAY BIT,
ISSATURDAY BIT)
And there is one search by a Stored Procedures
@dt=datepart (dw,GETDATE())
........
ISAPPLIEDFORSPECIFICDAYS =1 AND (
(ISSUNDAY=1 AND @dt) OR
(ISMONDAY=1 AND @dt) OR
(ISTUESDAY=1 AND @dt) OR
(ISWEDNESDAY=1 AND @dt) OR
(ISTHURSDAY=1 AND @dt) OR
(ISFRIDAY=1 AND @dt) OR
(ISSATURDAY=1 AND @dt) )
what will be the perfect index for this search?
Thanks
🙂

As mentioned earlier, ideally there would be a bitmap column like ISWEEKDAY, and then the perfect index would be (ISAPPLIEDFORSPECIFICDAYS,ISWEEKDAY).

Experiment with an index ON (ISAPPLIEDFORSPECIFICDAYS) that INCLUDES (ISSUNDAY,ISMONDAY,ISTUESDAY,ISWEDNESDAY,ISTHURSDAY,ISFRIDAY,ISSATURDAY).

You may even want to implement that ISWEEKDAY bitmap as a persisted computed column, just so you can combine it with ISAPPLIEDFORSPECIFICDAYS in an index.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

GilaMonster (6/3/2012)
That might be perfectly valid.

Personally I can't see any validity.

GilaMonster (6/3/2012)
If it is a table listing what days special offers are valid on then Monday, Tuesday and Thursday may be perfectly acceptable. We don't know.

Then the bit values simply accumulate.

Sunday and Monday have a value of 3

Sunday Tuesday and Friday have a value of 37

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉