Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: Bits of Indexes RFC

    I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.

    My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.

    Am I wrong in my conclusion?
    That which does not kill me postpones the inevitable.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
    GO
    
    CREATE INDEX myTable99_IX1 ON myTable99(Col1)
    CREATE INDEX myTable99_IX2 ON myTable99(Col2)
    CREATE INDEX myTable99_IX3 ON myTable99(Col3)
    GO
    
    --[CTRL]+k
    
    SELECT * FROM myTable99 WHERE Col1 = 0
    SELECT * FROM myTable99 WHERE Col2 = 0
    SELECT * FROM myTable99 WHERE Col3 = 'x'
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!

    Thanks Brett... Keep the info coming..

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I get Index Scans....I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..

    Hell the last 2 indexes are the same (if not larger) in....

    But why would a 3 selects use each new index as I add them?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
    GO
    
    CREATE INDEX myTable99_IX1 ON myTable99(Col1)
    CREATE INDEX myTable99_IX2 ON myTable99(Col2)
    CREATE INDEX myTable99_IX3 ON myTable99(Col3)
    GO
    --[CTRL]+k
    
    SELECT * FROM myTable99 WHERE Col1 = 0
    SELECT * FROM myTable99 WHERE Col2 = 0
    SELECT * FROM myTable99 WHERE Col3 = 'x'
    
    CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
    GO
    
    SELECT * FROM myTable99 WHERE Col1 = 0
    SELECT * FROM myTable99 WHERE Col2 = 0
    SELECT * FROM myTable99 WHERE Col3 = 'x'
    
    CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
    GO
    
    SELECT * FROM myTable99 WHERE Col1 = 0
    SELECT * FROM myTable99 WHERE Col2 = 0
    SELECT * FROM myTable99 WHERE Col3 = 'x'
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...

    So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

    In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)
    That which does not kill me postpones the inevitable.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Seppuku
    So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.
    It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..

    In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)
    In a table..no...in an index, it's imperative....at least that's what I've always believed...

    For example...an index with Col3, Col2, Col1

    And a predeicate of Col1 = something

    Is a table scan....ever read up on index intersection?

    Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5....got a very nice seek using IX1 thru IX3

    Wonder why the optimizer chose them...

    I'm (as usual, so it's not disconcerting) perplexed....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •