SQL Server Performance Forum – Threads Archive
number of columns vs performance
I am extending an exisiting table by creating a secondary table that can be linked to with a unique id.For the extended table I would like it to be ‘future proof’ and have all the columns I may ever need – 5 datetime, 30 strings and 20 ints.
My question is, is there a noteable performance price for having tables with lots of columns? Would a different approach be better?
I could allow nulls on most of the extended table so inserts would only worry about populated data items. Likewise, queries would usually only target a few columns but could potentially need to return all columns.
You might consider using a normalized design, where you the "columns" are actually rows, so you don’t need to worry about extending the table design. A child table for varying lists of dates would look something like this: ChildId (PK)
ParentId (FK)
DateName –"column name" for the date
DateValue … with a unique index on (ChildId, DateName) to avoid duplicate entries.
quote:Originally posted by simon9813
I am extending an exisiting table by creating a secondary table that can be linked to with a unique id.
For the extended table I would like it to be ‘future proof’ and have all the columns I may ever need – 5 datetime, 30 strings and 20 ints….
For the extended table I would like it to be ‘future proof’ and have all the columns I may ever need – 5 datetime, 30 strings and 20 ints….
Sounds like an extremely bad idea to me.
Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
quote:Originally posted by Adriaan
You might consider using a normalized design, where you the "columns" are actually rows, so you don’t need to worry about extending the table design.
A child table for varying lists of dates would look something like this:
ChildId (PK)
ParentId (FK)
DateName –"column name" for the date
DateValue … with a unique index on (ChildId, DateName) to avoid duplicate entries.
ParentId (FK)
DateName –"column name" for the date
DateValue … with a unique index on (ChildId, DateName) to avoid duplicate entries.
That’s an interesting idea – so I would have 1 ‘normal’ row of data and it would link to another row in the same table to act as extended data?
How would I query the table to get a row and all its child rows?
quote:Originally posted by Roji. P. Thomas
Sounds like an extremely bad idea to me.
That’s a shame because it would be the easiest to code. Do you have a suggestion for extending the table in some other way?
quote:Originally posted by simon9813
That’s an interesting idea – so I would have 1 ‘normal’ row of data and it would link to another row in the same table to act as extended data?
How would I query the table to get a row and all its child rows?
No, you would have a main table like you have now, and a child table with one row for each date that you need to add.
quote:Originally posted by Adriaan
You might consider using a normalized design, where you the "columns" are actually rows, so you don’t need to worry about extending the table design.
A child table for varying lists of dates would look something like this:
ChildId (PK)
ParentId (FK)
DateName –"column name" for the date
DateValue … with a unique index on (ChildId, DateName) to avoid duplicate entries.
ParentId (FK)
DateName –"column name" for the date
DateValue … with a unique index on (ChildId, DateName) to avoid duplicate entries.
That’s an interesting idea – so I would have 1 ‘normal’ row of data and it would link to another row in the same table to act as extended data?
How would I query the table to get a row and all its child rows?
quote:
Ahh, I see. How would it perform versus a fixed extended table if: (a) there were just a couple of extended data entries
(b) there were 30-40 extended data entries
Originally posted by Adriaan
No, you would have a main table like you have now, and a child table with one row for each date that you need to add.
Ahh, I see. How would it perform versus a fixed extended table if: (a) there were just a couple of extended data entries
(b) there were 30-40 extended data entries
Depends on whether you want to present the dates from the child table as separate columns in a resultset – if you do, that will take extra time to process. If not, difficult to say. 30 to 40 dates – that looks like perhaps this is used to flag changing statuses – why not have one current status date on the main table, plus a history table for past statuses?
quote:Originally posted by Adriaan
Depends on whether you want to present the dates from the child table as separate columns in a resultset – if you do, that will take extra time to process. If not, difficult to say.
30 to 40 dates – that looks like perhaps this is used to flag changing statuses – why not have one current status date on the main table, plus a history table for past statuses?
Thanks for the advice – I think I’ll knock up a few tables and see how they perform against each other.
]]>