SQL Server Performance

Performance on querying an indexed field with bitwise operation

Discussion in 'Performance Tuning for DBAs' started by lookaround, Sep 9, 2008.

  1. lookaround New Member

    Hi,
    I have to decide the way to perform a where on a field of a table using bitwise operation.
    I'll try to explain: I have a table with an indexed int field where I store a code whose value is in a set of power-of-2 codes, i.e.
    Messages Table
    IDMessage bigint
    MessageType int
    The allowed values of Message Type are pre-defined:
    1 - Private Message
    2 - Public Message
    4 - Broadcast Message
    8 - Open Message
    and so on...

    Suppose I have to select the rows of type Private OR Broadcast = 1 | 4 = 5
    I could do
    SELECT * FROM Messages WHERE (5 & MessageType) = MessageType
    or
    SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4
    My doubt is: Is the first query efficient? Or does it implies a table scan to perform a bitwise operation for each row in the table?
    I have absolutely no idea on the effect of such a type of comparison on indexed field...
    Thank you very much in advance for any elucidation!
  2. preethi Member

    Using OR condition is definitly better as you can use if you have an index on it. Additionally, it is easy to read, debug and maintain.
  3. lookaround New Member

    Thank you for the advice!
  4. moh_hassan20 New Member

    [quote user="lookaround"]
    Suppose I have to select the rows of type Private OR Broadcast = 1 | 4 = 5
    I could do
    SELECT * FROM Messages WHERE (5 & MessageType) = MessageType
    or
    SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4
    [/quote]

    SELECT * FROM Messages WHERE (5 & MessageType) = MessageType , return a mesaage that is both: Private Message and Broadcast Message
    which is not:
    SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4 , don't return
    the value stored in MessageType field is 5, and by bit operation to mask bits needed
    as you do bit operation on the field (i.e you do compuation on the field in where condition), i think optimizer will do index scan (not index seek)
    generate execution plan to be sure.
  5. lookaround New Member

    Thanks for your suggestion.
    The messageType stored value can be only one; can't be a combination of the flags (i.e. each Row is of a unique messagetype)
    I'll store in MessageType Field only one of the allowed values defined by the flag (i.e. one of 31 allowed values).
    When selecting I'll pass, tipically in a stored procedure paramater, a int value which can be a combination of Flags.
    So If I want to select the messages of type Private and Broadcast I'll pass the value 5 in the sp parameter and then perform the bitwise comparison.
    Due to the behaviour explained, the index should have only maximum 31 entries (one for each of the defined value of the flag). Is this correct?
    In your opinion, basing on the fact that index entries are in a limited number, do you think the index scan will be efficient?
    Thanks again in advance!
  6. moh_hassan20 New Member

    [quote user="lookaround"]Due to the behaviour explained, the index should have only maximum 31 entries (one for each of the defined value of the flag). Is this correct?[/quote]
    you are correct bez, your where condition is:
    where
    messagetype = @myaparam (the parameters you pass in sp) , you select one out 31 indexed values , so index is seek , and performance is good.

    But, if your where condition is:
    where mesagetype & value1 = value1 ,
    you do computation in the field , and optimizer do index scan (less efficient)
    and avoid this, as you can
  7. lookaround New Member

    Now is all clear...
    Many thanks againg!

Share This Page