| Author |
Topic |
|
dennisgaudenzi
Starting Member
26 Posts |
 Posted - 2011-07-14 : 08:23:42
|
| Hi there. I am building a new application and I am trying to decide if I should add a few more columns to an existing database table or add another table. We have about 15 tables now, all normalized, with the main table (crux of the system) having about 30 columns as it is. If I added 10 more columns to this table, is that better for performance than adding another table and inner joining it in? When I build the select stored proc, I would then jut be joining and still selecting the same amount of fields. I am not a db admin by trade, so I wanted to get some feedback before proceeding. I know you can "over normalize" a table so it just got me thinking. Running SQL 2008. Thx for the help in advance!!! Dennis |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-14 : 09:14:48
|
quote:
When I build the select stored proc, I would then just be joining and still selecting the same amount of fields
Then add more columns to the table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
dennisgaudenzi
Starting Member
26 Posts |
Posted - 2011-07-15 : 09:59:32
|
| Thanks for the reply. So, there really is not a general rule that you should not have more than "x" columns in a table? Just checking to see where performance starts to degrade and heavily hit web applications using SQL. thanks again. Dennis |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-15 : 12:59:03
|
| If there is a 1 to 1 relationship between the data there is no reason to add another table, it would only bring more overhead as you would have to do the join as well. But storage can be an issue if column usage is going "out of bounds"...in that case you can create new tables (especially if only a small portion of the records need all columns) or utilize "sparse columns" (http://msdn.microsoft.com/en-us/library/cc280604.aspx). It all depends. But in general I woudn't worry too much about 40 columns in a table even though it is a little on the wide side.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dennisgaudenzi
Starting Member
26 Posts |
Posted - 2011-07-16 : 00:10:03
|
| Thx again for the feedback. Most of the added columns would be varchar 50 or smalldate time fields. I will take all this in to consideration!One other question. If I am going to add another table that might have 1-4 records per associated record to another table by a common int field, is a join better to the other table or add those records (with duplicate other data) to the existing table? More records or additional join, which I more "expensive"?Thx! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-16 : 00:24:58
|
| JOIN |
|
|
|
dennisgaudenzi
Starting Member
26 Posts |
Posted - 2011-07-16 : 09:48:04
|
| Thx for everything everyone! Great site. Dennis |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-16 : 11:11:41
|
| >> I am building a new application and I am trying to decide if I should add a few more columns to an existing database table or add another table.<<This request makes no sense. You could not possibly be more vague. A table models either a relationship or a set of entities. We have no idea what your table is even at the highest level of abstraction. You might as well ask what color database has more garlic. >> We have about 15 tables now, all normalized, with the main table (crux of the system) having about 30 columns as it is. <<Sure wish we knew what the DDL looks like. Or even a valid name; “main table” is a term that does not exist in RDBMS. >> If I added 10 more columns to this table, is that better for performance than adding another table and inner joining it in? <<Are these columns attributes of the entity/relationship which the table models? Or are you adding the recipe for fried chicken to the Personnel table? Are these new columns a separate entity/relationship that needs its own table? Please get some help or read a book on basic data modeling. All the answers you got are absurd, since we have no information; Russell's was the worst. Join what to what and by what search condition? First get the model right, then do performance tuning. If you don't need the right answer then always give them "42" -- it worked for Douglas Adams.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
dennisgaudenzi
Starting Member
26 Posts |
Posted - 2011-07-16 : 11:43:53
|
| Hey Celko, while your points are all correct, I sure do appreciate (and I am sure everyone else who replied does too) the condescending feedback! A simple "Hey, I need more info b/c of x, y, and z" would be a bit better of an approach IMO. Just saying. I did not write more simply because i was on my phone and thought that there may have been enough for a basic, high-level answer. Sorry. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-16 : 14:42:26
|
| Joe, once again, not only are you arrogant, but wrong.He already said, in his second question that it is a one to many relationship.In a relational design, one would typically add a table and join to it as opposed to adding columns and duplicating data.Even you know that. |
|
|
|
dennisgaudenzi
Starting Member
26 Posts |
Posted - 2011-07-17 : 10:09:34
|
| Thanks Russell. One thing I will admit after reading back on my posts is that they were very simple that I just wanted reconfirmation on, but knew the answers that were most likely. I have been developing relatively extensive web apps for years and got a basic understanding of verifying from previous jobs under various DBAs. My job is not developing anymore, but do it where i need to for my own company to save big $$$$. Probably should have posted my initial question a bit better from my computer where it is easier to type and could have explained better. This is a great site and will do that for any post questions I have in the future. Thx again everyone. Dennis |
|
|
|
|
More columns or add table?