Using Bitwise Operators to Boost Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Herve Roggero » Using Bitwise Operators to Boost Performance

Using Bitwise Operators to Boost Performance

Rate Topic

Display Mode

Topic Options

Author

Message

Herve Roggero

Posted Tuesday, September 03, 2002 12:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 06, 2009 11:47 PM
Points: 48, Visits: 52

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

Post #6552

danpep

Posted Thursday, September 05, 2002 2:28 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 1:50 AM
Points: 10, Visits: 31

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 @UserID 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

Post #40864

Herve Roggero

Posted Friday, September 06, 2002 7:07 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 06, 2009 11:47 PM
Points: 48, Visits: 52

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

Post #40865

Jeff Moden

Posted Thursday, December 01, 2005 8:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 32,906, Visits: 26,795

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #241382

Oblio Leitch

Posted Friday, July 13, 2007 9:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 05, 2011 10:15 AM
Points: 175, Visits: 74

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?

Post #381525

« Prev Topic | Next Topic »

Permissions