SQL Server Performance Test for Bit Data Type in a WHERE Clause

By:   |   Comments (2)   |   Related: > Data Types


Problem

Can the use of bit data types in SQL Server improve query efficiency? Is the bit data type efficient? What are the performance implications for a bit, varchar or integer data type?

Solution

In a previous tip, I presented the T-SQL Bitwise Operators. One of the comments was a request to examine if query efficiency can be improved when using bit data types. In this tip we will examine the performance of different data types in the WHERE clause of a query.

For this tip, I created a table using the T-SQL below.

--======================================================
--Create our example table
--======================================================
create table tblPerformanceTest
(
  pKey integer identity(1,1) Primary Key,
  dollarAmount1 decimal(5,2),
  dollarAmount2 decimal(5,2),
  dollarAmount3 decimal(5,2),
  charFlag varchar(1),
  intFlag integer,
  bitFlag bit,
  lastUpdated datetime,
  UpdatedBy varchar(128)
)

Next, the table was populated using the T-SQL below.

declare @i integer=1
begin transaction
while @i<=5000000
begin
  insert into tblPerformanceTest --1
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --2
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --3
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --4
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --5
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --6
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --7
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --8
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --9
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'Y', 1, 'TRUE', getdate(), suser_sname())
  insert into tblPerformanceTest --10
  values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), 
          'N', 0, 'FALSE', getdate(), suser_sname())
  set @i=@i+1
  if (@i%1000=0)
    begin
    commit
    begin transaction
  end
end
commit

For each iteration of the loop, the code below will insert 9 true records and 1 false record. The terminating value for the loop was first set at 1 million and then increased in increments of 1 million up to 5 million. After each execution of the above code, the queries below were executed 10 times with the elapsed time for each query recorded. Each query counted 10 percent of the records in the table. The first query uses the integer column in the WHERE clause. The second query uses the varchar column in the WHERE clause and the third query uses the bit column.

Please note, the code for myRandomNumberFunction() can be found at this tip: A More Versatile SQL Server Random Number Function.

select count(*) as intCount from dbo.tblPerformanceTest where intFlag=0
select count(*) as charCount from dbo.tblPerformanceTest where charFlag='N'
select count(*) as bitCount from dbo.tblPerformanceTest where bitFlag=0

The average elapsed times for each query for each volume of records are displayed below. The integer data type in the WHERE clause provides the best performance, with the bit data type next and the varchar data type last.

Table with the elapsed time

The elapsed times are visualized in the chart below. Notice the linear relationship for each data type.

Logical OR example
Next Steps

The above queries used no indexing on the column used in the WHERE clause, so there is room for improvement.

Check out these other tips and tutorials on T-SQL on MSSQLTips.com.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, January 18, 2016 - 9:35:52 AM - kailash Ameta Back To Top (40438)

 Hi Dallas,

 

Thanks for your wonderful tip. We would like to have more tips on such type of articles.

It would be appreciated if you can also provide the reason why the use of Int data type provided in your example is fastest one instead of Bit.

As logically I can think that the Bit datatype requires the lowest size to store data and integer data type requires 4 byets to store data, hecne at the time of retrieving records from table INT data type should cause more IO operation than Bit data type. Accessing data from 4 byte should cause more overhead than 1 bit. I know the provided reason is novice level's reason. But still I am curious for the reason. Would you please help me to grasp this concept?

Thanks in advance.

Best Regards,

Kailash Ameta.


Friday, January 8, 2016 - 9:24:07 AM - Stephen Back To Top (40373)

In my tests I got a slightly different outcome when I passed the select satement a bit rather than a tiny int. I am working with the same data set as you with 5 million records.

select count(*) as intCount from dbo.tblPerformanceTest where intFlag=0

Average elapsed time 542.4000 ms

 

select count(*) as bitCount from dbo.tblPerformanceTest where bitFlag=0

Average elapsed time 748.6000 ms

Average elapsed time  as a percentage of the INT select 138.0%

 

select count(*) as bitCount from dbo.tblPerformanceTest where bitFlag=cast(0 as bit)

Average elapsed time 567.6000 ms

Average elapsed time  as a percentage of the INT select 104.6% 

 

As a rule its always best to avoid implicit conversions.

 















get free sql tips
agree to terms