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
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
- 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.
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.
How to Insert To Database as Single Integer field?
Using loop, get the Sum of all selected values:
Now Save Hobby to Customer Table
Now How to show selected Hobby in Edit Mode
if((Hobby & Convert.ToInt32(ddlHobby.Items[i].Value)) != 0)
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.
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
- You can use every single bit for one question.
- This is possible using bitwise
(Here I have stated only 4 questions.)
- Do you know XML? Yes/No
- Do you know AJAX? Yes/No
- Do you know SQL Server? 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:
// Apply bitwise AND with every value to Num.
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 (
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.
Development tool doesn't matter.