| Author |
Topic |
|
sql_buddy
Starting Member
41 Posts |
 Posted - 2009-07-18 : 01:51:06
|
| we are performing bit "&" operation on a table and retreiving data according to that,but that is just degrading our performance and getting timeout expired problem ,is there any way to fasten the bit operation in sql 2005 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-18 : 01:55:23
|
| can you be more specific plaese? can you post code snippet that's causing performance problem? also have you had a look at execution plan to see what are bottlenecks? |
 |
|
|
sql_buddy
Starting Member
41 Posts |
Posted - 2009-07-18 : 02:15:04
|
| we are having a table in which a facility col is int type on which we do bit "&" operation and decide which type of facility a person could avail .as each facility is give with a specific int with value of power of 2.so on doing bit operation of who are the users who area availing following fascility we have to do bit operations and thus getting time out expired |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-18 : 02:18:16
|
| are you sure this is place where performance is affected? how were you analysing performance? |
|
|
|
sql_buddy
Starting Member
41 Posts |
Posted - 2009-07-18 : 02:20:48
|
| query is taking more time to execute |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-18 : 03:06:35
|
Have you tried normalizing your tables?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
sql_buddy
Starting Member
41 Posts |
Posted - 2009-07-18 : 03:35:01
|
| ya table is normalized ,i feel that only bit operation query is taking alarmingly more time compared to select query in case of other fileds,that is why i conclude bit operation is taking more time. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-18 : 03:50:27
|
A bit column is not normalized, because having a bit column means you are trying to cram multiple type of information into one column.Create a junction table with many to one relation to original table.If Original table is "Customers" the junction table can look something likeCustomerID | FacilityIDFor this to work, you will have to create a Facility table like thisFacilityID | FacilityName
N 56°04'39.26"E 12°55'05.63" |
|
|
|
sql_buddy
Starting Member
41 Posts |
Posted - 2009-07-18 : 04:52:29
|
| column is not bit ,column type is int but bit "&" operation is used for retreiving multiple facility id's assosciated with the user.and table is normalized. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-18 : 04:55:10
|
Yes, I know you wrote an INT column, but you are treating the column as bit doing bit-mask operations on it.If you don't want advice, just say so.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
sql_buddy
Starting Member
41 Posts |
Posted - 2009-07-18 : 05:13:34
|
| so we have to create another table having facilityid and userid,and so need for bit operation will end but sir, database is running so change in schema is last to be thought of.so my top priority will be to see if bit operation could fasten bit operation. |
|
|
|
|
performance issue in bit operations