January 8, 2009 at 11:39 am
I have a table with 60 columns (most of the columns are int)
what is better for performance?
Leave the table like that or divide it for 2 or 3 tables?
January 8, 2009 at 12:29 pm
Time to bust out the old tried and true It Depends...
It depends on how you are using the data in your table...
The normalization gurus will say that if all of those columns rely on the PK then that's the best way to leave them... Obviously, if you have repeating groups or various other non-normal bits in your data you'd want to normalize those and break them out into their own tables.
If it's already well normalized and you are returning the entire row or almost the entire row each time you query that table, then breaking it up probably won't get you anywhere. If you are only returning portions, perhaps better indexing will help in that regard, using covering indexes or included columns etc.
It all really just depends on what data you're storing and how it all relates.
-Luke.
January 8, 2009 at 12:41 pm
Also, is this a transactional system or a BI system? 60 columns for some of the BI systems I've seen is pretty standard.
"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
January 8, 2009 at 2:20 pm
60 ints is a tiny table - don't make work - only consider vertical partitioning if you have real serious reasons to do so - it's bit like over normalisation and you'll find yourself adding joins all over the place. horizontal partitioning is a better way to go.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2009 at 3:00 pm
I have to go with "it depends", too. What is the table used for? How normalized is it? (If not sure what that means, ask. A lot of the online definitions aren't very clear because they assume you know a lot of other things.) How volatile is the table (how many inserts/updates/deletes per day)?
- 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
January 8, 2009 at 6:58 pm
roy vagner (1/8/2009)
I have a table with 60 columns (most of the columns are int)what is better for performance?
Leave the table like that or divide it for 2 or 3 tables?
Splitting it to 2 or 3 tables would actually become a performance problem for queries that needed something from each of those tables.
But, I agree with the others, 60 columns of int's isn't a real wide table unless you have some monster varchar columns or the table is actually VERY denormalized on some of the columns. The real key will likely be that which is picked as the PK and that which is picked as the clustered index if there's a clustered index at all. It's not just Selects you have to worry about, it's inserts, updates, and {gasp} deletes AND table/index maintenance that you must also be concerned with.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply