Are Bools/Bits Faster Than Integers

Question

As the title suggests I'm wondering whether bools or bits are faster than using integers (1 and 0)? Specifically I thinking about their speed in CASE statements, even more specifically in T-SQL (although it would be interesting to hear about experiences from other languages).

For some context, I have a pretty big SELECT statement over a large table which has to transform a lot of data based on integer being either 1 or 0, and was considering converting the integers to bits for a performance boost.

EDIT:

Thank you all for your responses. It kind of seemed like you were all giving me pretty much the same advice worded slightly differently or with different levels of detail.

Answer 1

One could go and make wild speculations here, or even educated guesses, but the truth is that it probably doesn't matter, and if it does, it's going to depend on a lot of other factors. The only way to find out is to profile, and when you do, first be sure to rule out other factors (system load, fragmentation, etc.).

My bet would be that the difference is going to be negligible, and that there's much more to win elsewhere - set up sensible indexes, profile joins vs. subqueries, use caching where possible, experiment with many small queries vs. few large queries, avoid unnecessary trips to the database, buffer accumulated results, etc.

The thing is, an integer vs. bool optimization will never give you more than a linear speed increase, and the increase won't be large - if you get 5%, consider yourself lucky. Those 5% will get smashed if your query does an outer join requiring full scans on both joined tables when one table is really large and you add one row to the other.

Answer 2

Don't worry about it.

If you're particularly interested in T-SQL, that implies you're using a database. Most likely a database on disk.

Disk access is multiple orders of magnitude slower than any CPU operation or memory access. Touching the disk just once, to read a single block of data, will likely take longer than the total time spent to make all the comparisons you're doing.

If you have performance problems, profile the code to find out where the actual problem is rather than immediately focusing on things which most likely take up under 1% of your total run time (and, therefore, can improve performance by less than 1% even if completely removed). Most likely, you'll get the best return on your optimization investment by improving your algorithms rather than by changing data types.

Answer 3

Just to give you more than the two and a half words I've written as a comment, let's get down to the processor level and try to find out what the difference is there regarding this question.

First, a processor always loads a full register size of data whenever anything needs to be processed. So the least data loaded, wheter you use it for a binary or integer comparison, would be 32 or 64 bits. Let's assume 32 for the moment.

Then, the difference between the two is this:

mov ax,[address]
cmp ax,1
jne label

or

mov ax,[address]
and ax,10h
je label

Forgive me if my assembler is not right on the spot, it has been about 13 years. But the answer still stands. The difference between these two should at the current processor generation be practically zero.

Then, there's the engine of the RDBMS. It might translate your CASE statement into something like the assembler above, but chances are that it transforms it based on it's own rules.

Then, there's the query optimizer. Whatever it does can be determined, usually by something like EXPLAIN (in MySQL), there's a similar function in MS-SQL-Server that explains the execution plan.

Then, for testing the speed, you need an environment that can ignore the interference from different processes, caching mechanisms and the likes.

So that all makes testing such things difficult, and the results speculative at best. Usually the time spent figuring out such little differences can be used to much greater use by optimizing indexes or data structures, server parameters or SELECT statements.

Cheers.

Answer 4

The other answers have touched on how the processor may handle an int vs. how it would handle a bit. A few other things to consider:

If you have multiple such fields in the table, storing them as bit should give you an advantage in row size (from MSDN):

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.)

So, in the best case scenario, for the "price" of an int (4 bytes), you could store 32 bit fields. I don't want to generalise too much, but disk access is often a bottleneck; reducing the amount of data you are physically storing will lead to faster queries.

People also tend to focus on the performance of bit fields in indexes. I've encountered quite a few cases where people changed bit fields to char(1) in order to allow the field to be indexed (mostly because you couldn't index a bit field prior to SQL 2005). The truth is, regardless of how it's stored, if your field only has two possible values, you're going to have trouble convincing SQL Server that it's worthwhile using as an index, unless it's used in combination with several other fields.

Answer 5

If your current implementation fails to meet performance requirements, use a profiler to identify where time is wasted.

If you identify bits vs. integers to be such a performance issue, then fix it.

Answer 6

This answer is not specific to T-SQL, or SQL, but is a more general answer to the title of this question, "Are Bools/Bits Faster Than Integers", this is certainly true for some microcontrollers which actually have a "bit" data type in hardware, such as the 8051 architecture and many PICs processors from Microchip.

In the 8051, the bit addressable memory in RAM runs from byte addresses 0x20 through 0x2f (128 bits total), and any SFR (special function register) whose address ends in 0 or 8 (another potential 128 bits, but not all are implemented). To support these, the 8051 has several bit-addressable instructions such as set bit, clear bit, complement bit, jump if bit set or clear and several more. The RAM bits are very useful for use as flags, and the SFR bits allow registers to be set, toggled, and cleared individually.

In most PICs (but not the PIC32), all internal RAM and I/O ports are bit addressable, and supported by instructions such as set bit, clear bit, and skip if a bit is set or reset. Since the working register WREG and status register are mapped to internal addresses also, one can do bit tests on those also.

On CPUs which do not have bit-addressable instructions, as other posters have noted, it would be necessary to load an entire word from memory and do a bit-wise operation on it, which might take as many as three instructions: load, and/or/xor, and store or branch.

C compilers for the 8051 and PICs typically have a "bit" data type in addition to char, short, int and long to allow C code to take advantage of these operations.

Answer 7

Using bits make sense instead of integers when you utilize bit map indexes (not available in SQL Server, but can be found in Oracle) and when you use bit map filters (available in SQL Server). From your question, this is not what you are looking for, but I think it may be of interest to you because it shows a practical application using binary over other data types in database design.

The following link gives some details:

Link

Answer 8

The simple answer: it depends.

The more complex answer: it depends on how your language, compiler, and runtime deal with bools or bits, and the word length of your CPU registers (64, 32, even 16 or 8 still have some use in simpler computing devices).

There are a couple of major ways to set up a boolean:

• Enumerated data type: public enum Bool {false, true};. This is probably the least space-efficient, as depending on the language/compiler an enum is usually turned into a bunch of byte constants, with compiler-checking to ensure that an invalid value is never used. However, most computers deal with byte values very efficiently; there's very little binary math required with them.
• Bitmasks: When used, it's almost always behind the scenes, though .NET has "Flag Enums" that allow you to specifically set multiple bits on one composite value. The idea for bools is the same; your compiler will take each bool that has the same scope and pack them into bytes. The second boolean to be declared would be the second bit from the left or right, and can be tested with some simple binary math: a&0x02 >> 1 will return a 1 or 0 representing that particular boolean out of the byte.

Everything I know says that each boolean gets a byte in all but the most memory-critical applications, similar to the first option, because the extra computation for bit-packing slows the application considerably to save a paltry 8 bytes of storage space, and only when the booleans are not actively needed.