Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: More > Data Types

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.



Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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 08, 2016 - 9:24:07 AM - Stephen Back To Top

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.

 


Learn more about SQL Server tools