March 24, 2010 at 7:13 pm
I inherited at work a very wide table (~ 100 fields). The pain is that most of the fields are like :
Answer1 - Answer30 varchar(255)
Comment1 - Comment20 varchar(512)
and so on...
Of course that in more than 80% of the cases the value stored in this fields is just NULL...
Now the question:
This year we're going to upgrade our DB server to 2008 R2 and with this occasion I want to change its design (this is the most accessed table in the DB and it stores 3-4 rows/8 KB page .... imagine the speed).
Thinking from the perspective of data retrieval performance (and considering the new "sparse" option in 2008 fields) what would be the best way of redesigning it:
- leave it as it is
- leave the basic fields (~20) in this table and move all the "Answers" and "Comments" into another table but with the exact same structure (and an ID for linking of course)
- leave the basic fields in the table, create an "Answers" and a "Comments" table, both of them having an ID to link them to the main table and a schema like this :
AnswerID shortint ; Text varchar(255) respectively
CommentID shortint ; Text varchar(512) for the 2nd table;
I personally believe the last solution is the best one (sometimes I am thinking about the second one only because I wouldn't have to change too much all the code...) but I am sure some people have an expert perspective (and maybe a better solution than mine)
March 25, 2010 at 6:46 am
It sounds like a classic design error on normalization. Yes, given the opportunity, I'd suggest redesigning the structure and migrating the data. It won't be that hard and you should be to see both storage enhancements and performance enhancements when you're done.
As to the design, not knowing all the details, which means my suggestion isn't worth that much, I'd look at moving the answers & comments out to two seperate tables. You might want to look at more than just the two tables assuming you need to associate answers to questions or comments to answers, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2010 at 7:21 am
I'm in agreement; I would be tempted to take the poor thing out of its missery and redesign it on the 3NF.
Having said that #1... how about the application? better to get a LOE from application side before putting your hands on it.
Having said that #2... is this table used solely for reporting purposes? if the answer is "Yes" - with a capital "Y" - you may want to benchmark performance in between the current flat design and a 3NF design.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply