Using index for bitwise comparison

Question

Post reply

Using index for bitwise comparison

Igor Marchenko

Hall of Fame

Points: 3281
More actions
September 28, 2006 at 6:08 pm

#174186

Hello!

I remember reading a while ago that index on INT column could be used for bitwise operations efficiently. For example,
Index on dwRights_Public could be useful:

select dwPlayerIx from FTE_TABLE_SIT where (dwRights_Public&4) > 0

I created an index (which is highly selective) but Query Optimizer is alwasy using clustered index scan. Is my assumtpion incorrect and index can not be utilized for this type of queries?

Thanks,
Igor

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

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

Sergiy SSC Guru Points: 110192 More actions · Answer 1

Move your rights to look-up table and do the search on it.

It will be scan anyway, but it will be scan on small table.

Than join this lookup to FTE_TABLE_SIT on indexed column.

select dwPlayerIx

from FTE_TABLE_SIT F

inner join RightsTable R ON R.dwRights_Public = F.dwRights_Public

where (R.dwRights_Public&4) > 0

You must have indexes on dwRights_Public columns in both tables to make it work.

_____________
Code for TallyGenerator

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

Any fuinction applied to a column will prevent index seeks, even if it's something simple like column+0

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

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 3

I realize that (R.dwRights_Public&4) makes agrument NON-SARGABLE. I found another solution that might work well for my sccenario: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5970

Thanks,

Igor

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Which SARGABLE solution have you found over there?

_____________
Code for TallyGenerator

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 5

Igor Marchenko

Hall of Fame

Points: 3281

October 2, 2006 at 8:48 pm

#663761

Create and index on computed column (dwRights&4).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

I dunno wth he found but I kinda like the looks of the calculated column... could be sargeble that way...

--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)
Intro to Tally Tables and Functions

Sergiy SSC Guru Points: 110192 More actions · Answer 7

Did you read anything about index on BIT column?

It does not work by definition.

It's strong recommendation in any book - don't create index on bit column.

You don't need to believe me or anyone else, but you just do the test. Create suggested computed column and lookup table with FK on indexed column.

And run 2 queries. One against computed column, another - using JOIN to lookup table.

It's better one time to see...

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Thanks Serqiy. Nope, I haven't actually lost my mind (yet). I think just about everyone except the guy who wrote that knows that creating indexes on bit columns is a bit counter-productive (that's a pun, son ).

Nope... it seemed to me that some calculated columns operating on the wide-bit-wise column make life a little easier and was trying to figure out if there was a way to make things indexable using those non-bit-defined calculated columns. But even when you combine some of the more heavily used combinations, you don't come up with anything unique enough to really be worthwhile as an index... You know me... I always have to try these things out

--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)
Intro to Tally Tables and Functions

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 9

Jeff, Sergiy,

Thanks for your feedback. Computed column dwRights_Public&4 has, indeed, two distinct values (0 and 4), even though field type is defined as INT. As I mentioned before, most of the records will have value equal 0. Query using '...where (R.dwRights_Public&4) > 0' condition is, in fact, making use of index on computed column. I populated table in development and did run some tests. You can take my word on it

By the way, I did read something about index on BIT fields

Igor

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Sounds like you did your homework... glad it worked out.

--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)
Intro to Tally Tables and Functions