SQL Server Performance

A lot of bit columns in a where, a bad thing for performance?

Discussion in 'SQL Server 2005 General DBA Questions' started by matt3.5, Aug 12, 2008.

  1. matt3.5 New Member

    Hello,
    I have a design issue with a table. To cut a long story short, users of a site need to be able to store their preferences in a table. Part of this user profile system includes a bunch of tick boxes to indicate various preferences. This leaves a table with 11 bit columns. Now I know from a size perspective this isn't a big deal, but I'm concerned about performance when it comes to querying this as the query will have to check all of these bit columns for a yes/1 value....so my WHERE clause is going to end up like:..............WHERE PrefA = 1 OR PrefB = 1 OR PrefC = 1 etc.. (for all 11 preferences).
    I'm thinking that, even though a bit column is very small, this can't be good for performance because, well, the where clause is checking 11 columns. So could this be solved by using a covering index over the columns or should I avoid this situation altogether? We are talking a lot of records here as user profiles can be anonymous. My only other option though would be to go down a JOIN route with a many-many JOIN table.
    On the other hand, I could be worrying about nothing here because SQL Server is lightning fast at WHERE clauses against a lot of bit columns.......but that just dosn't sound right..
    Hope someone can shed some light on this!
    Matt
  2. Adriaan New Member

    There is always the option of using bitwise logic, where each option has its own value of a single bit (1, 2, 4, 8, 16, etc) which allows you to put a lot of options into one column. You can filter using the bitwise operators (see BOL).
    Might be good for querying, might be less suitable for the user interface of your client application.
  3. FrankKalis Moderator

    [quote user="matt3.5"]
    I have a design issue with a table. To cut a long story short, users of a site need to be able to store their preferences in a table. Part of this user profile system includes a bunch of tick boxes to indicate various preferences. This leaves a table with 11 bit columns. Now I know from a size perspective this isn't a big deal, but I'm concerned about performance when it comes to querying this as the query will have to check all of these bit columns for a yes/1 value....so my WHERE clause is going to end up like:..............WHERE PrefA = 1 OR PrefB = 1 OR PrefC = 1 etc.. (for all 11 preferences).
    I'm thinking that, even though a bit column is very small, this can't be good for performance because, well, the where clause is checking 11 columns. So could this be solved by using a covering index over the columns or should I avoid this situation altogether? We are talking a lot of records here as user profiles can be anonymous. My only other option though would be to go down a JOIN route with a many-many JOIN table.
    On the other hand, I could be worrying about nothing here because SQL Server is lightning fast at WHERE clauses against a lot of bit columns.......but that just dosn't sound right..
    Hope someone can shed some light on this!
    [/quote]
    Sorry, I might be missing something, but why do you need to query on the preferences instead of just returning them to the client and let the client handle the rest? Can you give a more concrete example?
  4. ScottPletcher New Member

    I would think a bit check would be the fastest type of check that could be done. Don't see why 11 -- or 15 or 20 -- would be that much overhead.

Share This Page