Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Bitwise Operators to Boost Performance Expand / Collapse
Author
Message
Posted Tuesday, September 3, 2002 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 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
Posted Thursday, September 5, 2002 2:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 18, 2013 3:26 AM
Points: 10, Visits: 37
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
Posted Friday, September 6, 2002 7:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 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
Posted Thursday, December 1, 2005 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #241382
Posted Friday, July 13, 2007 9:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 5, 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 »

Add to briefcase

Permissions Expand / Collapse