July 10, 2008 at 11:47 am
Hi All
I have a unique situation
We have a table wilt around 4 million+ records and growing 100k + per month
And we are planning to add around 26 new bit columns
How space and performance will affect in these scenario.
To have 26 new flag fields
Or One Int field and then UI to convert INT in to flag
July 10, 2008 at 12:10 pm
How will it affect performance? Not sure.
An int in 4 bytes, so 32 bits are available. You could bitmask, but I think the storage overhead for bits is minimal, they are combined into bytes.
either way, you're talking a few bytes per row. It shouldn't affect space much.
In terms of performance, depends on how you query. If you build the proper bitmask and then index on the int, it shouldn't have much impact, but it makes querying complicated. If you query by bit fields, are you indexing every one? Could be complicated to write these queries in a different way.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 10, 2008 at 12:15 pm
Hi Thanks
For replay
As far as DB is concern it will store and return values
If we store it as an INT then our app will do all calculation value of 1 and 0
Thanks
July 10, 2008 at 12:35 pm
26 bits will take 4 bytes of storage per row for the data. 26 ints will take 108 bytes of storage per row. That also means more IO, more RAM, more network traffic, etc. Or is that not what you mean?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2008 at 12:40 pm
No Just one INT column and will mask it to 26 flages
July 10, 2008 at 1:39 pm
It is quite the indexing question actually.
If you have 26 bit fields you could index each field individually (which is somewhat useless) or index groups of columns in which you will commonly be querying. This would give you the flexibility to index by say Bit1, Bit2, Bit5 and by Bit2, Bit4, Bit1 if your queries tend to include multiple checks at once.
If you have a single integer bitmask, you will only have one indexing order for your bits and an index will include all of the bits. This will be great if you constantly query for an entire bitmask (MyField=26) and you are not doing some really crazy OR conditions with a bitmask conversion.
I would think the single integer column would typically be the more useful way to go.
July 10, 2008 at 2:01 pm
Hope this doesn't sound stupid, but I would simply test it and compare both versions.
Make sure you rebuild the table or clustered index after adding / removing the columns.
Best Regards,
Chris Büttner
July 10, 2008 at 3:32 pm
A single column, either Int or Char, used as a bitmask, would probably be better than 26 bit fields. If you need to add another condition, you just add another digit, as opposed to adding another column. Less human-readable, but probably easier to maintain and operate on. Might be better performance, that would require testing to find out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply