8

If my table has a huge number of columns (over 80) should I split it into several tables with a 1-to-1 relationship or just keep it as it is? Why? My main concern is performance.

PS - my table is already in 3rd normal form.

PS2 - I am using MS Sql Server 2008.

PS3 - I do not need to access all table data at once, but rather have 3 different categories of data within that table, which I access separately. It is something like: member preferences, member account, member profile.

niaher
  • 9,460
  • 7
  • 67
  • 86
  • Duplicate... sort of http://stackoverflow.com/questions/486991/what-are-your-thoughts-on-breaking-up-a-large-sql-table-into-several-based-on-a – baudtack Jun 18 '09 at 00:20
  • do you need all 80 columns in the usual case? Are there logical groups for those attributes that correspond to your use cases? – Ben Hughes Jun 18 '09 at 00:20
  • Yes there are 3 logical groups: prefereces, account details, profile details. – niaher Jun 18 '09 at 00:26
  • @docgnome It looks like the question you are linking to is asking about splitting up a table based on rows (data)... this question is about splitting a table based on columns. – Paul Rowland Jun 18 '09 at 00:26

5 Answers5

16

80 columns really isn't that many...

I wouldn't worry about it from a performance standpoint. Having a single table (if you're typically using all of the data in your standard operations) will probably outperform multiple tables with 1-1 relationships, especially if you're indexing appropriately.

I would worry about this (potentially) from a maintenance standpoint, though. The more columns of data in a single table, the less understandable the role of that table in your grand scheme becomes. Also, if you're typically only using a small subset of the data, and all 80 columns are not always required, splitting into 2+ tables might help performance.

Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
7

Re the performance question - it depends. The larger a row is, the less rows can be read from disk in one read. If you have a lot of rows, and you want to be able to read the core information from the table very quickly, then it may be worth splitting it into two tables - one with small rows with only the core info that can be read quickly, and an extra table containing all the info you rarely use that you can lookup when needed.

Tim Haines
  • 1,496
  • 3
  • 14
  • 16
  • I haven't thought about it from this perspective and I totally agree with your argument. However in my case I will always only retrieve a single row specified by primary key. – niaher Jun 18 '09 at 00:35
  • 1
    If you're only ever retrieving a single row at once, then you probably won't have performance issues with it. You may want to split it for non performance reasons though. i.e. if often many of the fields are unused, you might be able to save a lot of disk space by moving them out of the table into another, and only creating rows when you have info for those columns. – Tim Haines Jun 18 '09 at 00:43
3

Taking another tack, from a maintenance & testing point of view, if as you say you have 3 distinct groups of data in the one table albeit all with the same unique id (e.g. member_id) it might make sense to split it out into separate tables.

If you need to add fields to say your profile details section of the members info table, do you really want to run the risk of having to re-test the preferences & account details elements of your app as well to ensure no knock on impacts.

Also for audit trail purposes if you want to track the last user ID/Timestamp to change a members data. If the admin app allows Preferences/Account Details/Profile Details to be updated separately then it makes sense to have them in separate tables to more easily track updates.

Not quite a SQL/Performance answer but maybe something to look at from a DB & App design pov

MadMurf
  • 2,247
  • 3
  • 23
  • 30
1

Depends what those columns are. If you've got hard coded duplicated fields like Colour1, Colour2, Colour3, then these are candidates for child tables. My general rule of thumb is if there's more than one field of the same type (Colour), then you might as well code for N of them, not a fixed number.

Rob.

Rob Nicholson
  • 1,943
  • 5
  • 22
  • 37
1

1-1 may be easier, if you have say Member_Info; Member_Pref; Member_Profile. Having too many columns can make it run if you want lots of varchar(255) as you may go over the rowsize limit, and it just makes it too confusing.

Just make sure you have the correct forgein key constraints and suchwhat, so there's always 1 row in each table with the same member_id

glasnt
  • 2,865
  • 5
  • 35
  • 55