Using Bitwise Operators to Boost Performance

  • Herve Roggero

    Ten Centuries

    Points: 1230

    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

  • danpep

    SSC Journeyman

    Points: 94

    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

  • Herve Roggero

    Ten Centuries

    Points: 1230

    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: 997205

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Oblio Leitch

    SSCrazy

    Points: 2137

    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?

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

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