Click here to Skip to main content
11,705,473 members (41,256 online)
Click here to Skip to main content

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

, 4 Oct 2012 CPOL 61.8K 20
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)

Share

About the Author

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

You may also be interested in...

Comments and Discussions

 
QuestionMultiple choice online exam Pin
Chetan Patel9-Feb-14 7:25
memberChetan Patel9-Feb-14 7:25 
General[My vote of 2] This is completely misguiding Pin
_groo_26-Sep-12 3:52
member_groo_26-Sep-12 3:52 
GeneralThoughts Pin
PIEBALDconsult20-Aug-12 4:11
memberPIEBALDconsult20-Aug-12 4:11 
GeneralGood tip: have always used bitwise operators where appropria... Pin
mark merrens30-Nov-11 0:49
membermark merrens30-Nov-11 0:49 
GeneralReason for my vote of 5 Thank you Pin
Jamal Alqabandi5-Oct-11 13:17
memberJamal Alqabandi5-Oct-11 13:17 
GeneralReason for my vote of 5 Good one......... Pin
Vivek Johari6-Jan-11 16:31
memberVivek Johari6-Jan-11 16:31 
GeneralReason for my vote of 3 Really nice one Pin
am.net29-Dec-10 22:58
memberam.net29-Dec-10 22:58 
Reason for my vote of 3
Really nice one
GeneralReason for my vote of 4 good one Pin
Pranay Rana26-Dec-10 23:52
memberPranay Rana26-Dec-10 23:52 
GeneralReason for my vote of 4 Good advice, but could use some clar... Pin
Walt Fair, Jr.25-Dec-10 7:46
subeditorWalt Fair, Jr.25-Dec-10 7:46 
GeneralPerformance when Filtering Data Pin
Ravi LVS26-Feb-10 16:54
memberRavi LVS26-Feb-10 16:54 
GeneralRe: Performance when Filtering Data Pin
Chetan Patel26-Feb-10 22:20
memberChetan Patel26-Feb-10 22:20 
GeneralRe: Performance when Filtering Data [modified] Pin
Ravi LVS26-Feb-10 23:41
memberRavi LVS26-Feb-10 23:41 
GeneralRe: Performance when Filtering Data Pin
Chetan Patel1-Mar-10 19:49
memberChetan Patel1-Mar-10 19:49 
GeneralRe: Performance when Filtering Data Pin
Ravi LVS2-Mar-10 21:08
memberRavi LVS2-Mar-10 21:08 
GeneralRe: Performance when Filtering Data Pin
Chetan Patel4-Mar-10 6:50
memberChetan Patel4-Mar-10 6:50 
GeneralRe: Performance when Filtering Data Pin
Ravi LVS4-Mar-10 16:13
memberRavi LVS4-Mar-10 16:13 
GeneralRe: Performance when Filtering Data Pin
Chetan Patel4-Mar-10 22:32
memberChetan Patel4-Mar-10 22:32 
GeneralRe: Performance when Filtering Data Pin
QCPBraca28-Mar-10 23:48
memberQCPBraca28-Mar-10 23:48 
GeneralRe: Performance when Filtering Data Pin
Ravi LVS29-Mar-10 17:03
memberRavi LVS29-Mar-10 17:03 
GeneralRe: Performance when Filtering Data Pin
Chetan Patel16-Jan-12 23:32
memberChetan Patel16-Jan-12 23:32 
GeneralRe: Performance when Filtering Data Pin
Marc Temanson22-Mar-12 4:03
memberMarc Temanson22-Mar-12 4:03 
QuestionOR & XOR bit wise operation? Pin
Member 365361222-Feb-10 22:09
memberMember 365361222-Feb-10 22:09 
AnswerRe: OR & XOR bit wise operation? Pin
Chetan Patel25-Feb-10 0:32
memberChetan Patel25-Feb-10 0:32 
Generalnormalize properly Pin
Rozis21-Feb-10 10:22
memberRozis21-Feb-10 10:22 
GeneralRe: normalize properly Pin
Chetan Patel25-Feb-10 0:36
memberChetan Patel25-Feb-10 0:36 
GeneralRe: normalize properly Pin
_groo_26-Sep-12 4:22
member_groo_26-Sep-12 4:22 
QuestionCan SQL usefully index binary-coded data? Pin
supercat919-Feb-10 5:51
membersupercat919-Feb-10 5:51 
AnswerRe: Can SQL usefully index binary-coded data? Pin
Chetan Patel19-Feb-10 17:54
memberChetan Patel19-Feb-10 17:54 
GeneralRe: Can SQL usefully index binary-coded data? Pin
Rozis21-Feb-10 10:25
memberRozis21-Feb-10 10:25 
GeneralRe: Can SQL usefully index binary-coded data? Pin
Chetan Patel22-Feb-10 21:35
memberChetan 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 | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 5 Oct 2012
Article Copyright 2010 by Chetan Patel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid