Click here to Skip to main content
Click here to Skip to main content

Optimize Speed and Performance of SQL Server with Use of Bitwise Operators

, 4 Oct 2012
Rate this:
Please Sign up or sign in to vote.
Improve speed while saving the storage space for data, basic things to take care of during database design.

Introduction

The performance of the application is the key factor while designing a product, everybody would like their application to be as fast as possible. If your database design is tricky, you can reduce storage and improve speed.

We should have to consider all alternate possible solutions to store data so as to improve performance. This topic covers one of the best tricks to store multi choice answer (like Hobby, Language Known, etc.), which you have to use for advanced search later.

DataType of Fields

This is the main concern which directly affects the size and processing time of your data.

  • Always prefer Int/Number Datatype as your Primary Key Column.
  • Check range of possible values of the column and choose best fit DataType. ex.// Age : It is between 1-125, then use Byte for the same instead of Int(32 bit).
  • Make sure your database is normalized properly.

The above things are very basic and must be adopted during database design.

  • Understand the Bitwise operators.

You can store Hobby, Language Known, etc. multiple possible options using bitwise operator in single integer field.

Practical Example

Table for Hobby
ID      Title               Value
1       Cricket             1
2       Reading             2
3       Music               4
4       Indoor Games        8
5       Painting            16
6       Traveling           32

You may user Hexa decimal values for simplicity ex.//

ID      Title               Value
1       Cricket             0x01
2       Reading             0x02
3       Music               0x04
4       Indoor Games        0x08
5       Painting            0x10
6       Traveling           0x20 

Req->> Customer can have any combination of the above Hobbies.

So now populate CheckboxList with the above Table Data.

DataTextField="Title"
DataValueField="Value"

How to Insert To Database as Single Integer field?

Using loop, get the Sum of all selected values:

int Hobby=0;
for(i=0;i<ddlHobby.Items.Count-1;i++)
{
    if(ddlHobby.Items[i].Selected)
       Hobby=Hobby+Convert.ToInt32(ddlHobby.Items[i].Value);
}
Now Save Hobby to Customer Table
  objCustomer.Hobby=Hobby;
Now How to show selected Hobby in Edit Mode
int Hobby=objCustomer.Hobby;
for(i=0;i<ddlHobby.Items.Count-1;i++)
{
    if((Hobby & Convert.ToInt32(ddlHobby.Items[i].Value)) != 0)
       ddlHobby.Items[i].Selected=true;
}

Now in advanced search, it is very fast to get result. Allow the user to select one or more hobbies in search preferences. Get total (sum) of values of selected hobby.

Now in Query, write as follows:

where (Hobby And @Hobby) <> 0

It is much faster than any other way.

Another Example

If you need to store 30 questions with answers: Yes or No? You think of taking 30 columns for each question.

  • It is possible with the usage of single Int (32bit).
  • You can use every single bit for one question.
  • This is possible using bitwise AND Operator.
Example
(Here I have stated only 4 questions.)
  1. Do you know XML? Yes/No
  2. Do you know AJAX? Yes/No
  3. Do you know SQL Server? Yes/No
  4. Do you know JavaScript? Yes/No

So take 1 bit for each question.

Like 0000 in this four bit, each bit has its value as 8, 4, 2, 1 means power of 2. (Decimal value of each bit position).

While storing the answer:

   if 1st is Yes Add 1 or 0x01 to sum (selected)
   if 2nd is Yes Add 2 or 0x02 to sum (selected)
   if 3rd is Yes Add 4 or 0x04 to sum (selected)   
   if 4th is Yes Add 8 or 0x08 to sum (selected)
// 1st and 3rd are selected then store 1+4=5 to Integer Column Value.

How to Retrieve this Value

Read the number from DB:

Num=5
// Apply bitwise AND with every value to Num.
//ex
   for 1st option   if (num And 1)=1 then 1st selected
   for 2nd option   if (num And 2)=2 then 2nd selected
   for 3rd option   if (num And 4)=4 then 3rd selected
   for 4th option   if (num And 8)=8 then 4th selected

Last week, I used this trick to get Product Input.

The products have different attributes, so we have to create a table with all the fields (UNION).
While defining product, select which are required. You can filter these fields while getting input from user.

If you have any questions or suggestions that will improve this tip, please feel free to post them.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Chetan Patel
Software Developer (Senior)
India India
Development tool doesn't matter.
Chetan Patel
Gujarat-India.

Comments and Discussions

 
QuestionOR & XOR bit wise operation? PinmemberMember 365361222-Feb-10 22:09 
AnswerRe: OR & XOR bit wise operation? PinmemberChetan Patel25-Feb-10 0:32 
Generalnormalize properly PinmemberRozis21-Feb-10 10:22 
GeneralRe: normalize properly PinmemberChetan Patel25-Feb-10 0:36 
GeneralRe: normalize properly Pinmember_groo_26-Sep-12 4:22 
QuestionCan SQL usefully index binary-coded data? Pinmembersupercat919-Feb-10 5:51 
AnswerRe: Can SQL usefully index binary-coded data? PinmemberChetan Patel19-Feb-10 17:54 
GeneralRe: Can SQL usefully index binary-coded data? PinmemberRozis21-Feb-10 10:25 
But because of 'colating sequence' (the ordering of characters in the alphabet are nation dependent) this may be extreme tricky...
GeneralRe: Can SQL usefully index binary-coded data? PinmemberChetan Patel22-Feb-10 21:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 5 Oct 2012
Article Copyright 2010 by Chetan Patel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid