Using Bitwise Operators to Boost Performance

Question

Using Bitwise Operators to Boost Performance

Herve Roggero

Ten Centuries

Points: 1230
More actions
September 3, 2002 at 12:00 am

#151734

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hroggero/usingbitmaskoperators.asp
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing

Viewing 5 posts - 1 through 4 (of 4 total)

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

danpep SSC Journeyman Points: 94 More actions · Answer 1

Thanks for raising another interesting topic. I had never considered the performance benefits of doing things this way, but have used the method for other reasons (sometimes you simply don't need a reference data table).

Anyway, using your example schema, I just thought I'd highlight the fact that the "traditional" result set of all errors for a/all users can also be generated using the bitwise method.

Cheers,

Daniel

***************************************

DECLARE @user-id int

SET @userid = 3

-- return all errors for a user using the bitwise method in a join

SELECT U.*, E.*

FROM Users U

JOIN Errors E

ON E.ErrorFlag & U.UserErrorFlag = E.ErrorFlag

WHERE U.Userid = @userid

-- return all errors for a user using a join to the many-to-many intermediate table

SELECT U.*, E.*

FROM Users U

JOIN UserErrors UE

ON UE.UserId = U.UserId

JOIN Errors E

ON E.ErrorId = UE.ErrorId

WHERE U.UserId = @userid

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 2

Daniel, thanks for your input and your feedback.

I agree with you. Actually, I have seen cases where so many locks exist on small and static tables that the application layer was really slowing down.

I guess the only danger of not using referenced tables would be data integrity. I usually enforce integrity with the reference table, but use bitwise operators to get to the data, when it makes sense.

Herve

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Nice article... you really put some thought into the proofs and which graphics to use. Good idea, as well.

--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)

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 4

It's interesting you did not point out the possibility of a "double unknown" of using this method. That is, it's possible for the error field to be either 0 or NULL. Both values would have, essentially, the same meaning. It's possible to make use of this fact, say 0 is success and NULL is the program has reported back yet.

But, I would really love to know more, specifically, how large your dictionary can be? This is complicated by the fact that MS stores your larger values signed. For example, a TINYINT is unsigned and can store 7 concurrent values in addition to NULL and 0. But in order to expand beyond that using SMALLINT, you have to divide the value between positive and negative, -2^15 (-32,768) to 2^15-1 (32,767). This gives you 14 concurrent values on the positive side (with 0) and 15 on the negative (without). If SMALLINT were unsigned, you would have 15 with 0 and NULL. INT gives you 30, and BIGINT gives you 62. Essentially, that means that this is only a viable alternative if you need to assign fewer than 62 matches. Or, perhaps the NUMERIC data type can be used for bigger numbers?