Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spider3
Starting Member
11 Posts |
 Posted - 2007-06-15 : 09:55:17
|
| Hi to everyone!I've just started to learn MS SQL Server 2005 and I hope you can give me some advices about what is the best design for the max performances between the two cases:Case 1:Single table, but not very scalableTable name: GS salary_id (primary key, int)salary_description (varchar)salary_cumulative_1 (int)salary_cumulative_2 (int)..salary_cumulative_9 (int)Case 2:Two tables, this removes the limit of having only 9 salary_cumulative field, but will be performance worst? Keep in mind that this table(s) will be queried a lot the whole times when the user use the application.(1) Table name: GSsalary_id (primary key, int)salary_description (varchar)(2) Table name: Cumulativesalary_id (primary key, int), foreign key salary_cumulative (varchar)Thank you a lot for your time!ps: Can you also please tell me if using long (very long) names for the tables name will decrease the performances too?Greetings,Mirto |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-15 : 10:20:19
|
| >>ps: Can you also please tell me if using long (very long) names for the tables name will decrease the performances too?The query plan uses object IDs not names - might take a bit (very tiny bit) longer to compile.Your 9 lmit seems to be because you have named them _1 - _9 - why not _01 - _99Saying that why are these held on that table at all and what are they meant to be?how aboutsalary_id, Descriptionsalary_id, Cumulative_number, Cumulative_Value==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-15 : 10:21:18
|
case 2 seems better - it's normalized, plus it supports an arbitrary number of "cumulative" entries. However salary_id can't be a primary key in the Cumulative table since it's a fk to GS - there will be multiple same values of salary_id in Cumulative.using long names will not hurt perf at all. something more descriptive than GS is in order I think. Perhaps "Salary" would be a better name for this table.
elsasoft.org |
|
|
|
spider3
Starting Member
11 Posts |
Posted - 2007-06-15 : 10:53:56
|
| Thank you a lot for your quick reply! *smile*nr: i've set only 9 fields because not always they are all filled, sometimes there is only one cumulative, sometimes none and sometimes we need more. 9 is the maximum we ever needed, but since we are creating the application in another language (from VO to C#) we'd like to optimize everything and keep in mind the future scalability.jezmine: thank you for the info about the fk and primary key, didn realized it! ;)In my opinion using two tables is a lot better and structurally correct, but I just wanted to be sure about loss of performances querying two tables instead of only one. From what you are saying, it seems that should not cause me relevant loss of performances.thanks! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-15 : 10:59:10
|
| Your cse 2 is the same as mine but with out the Cumulative_number to make the PK.(I'm no good at names).I would go with that and if necessary you could create the flattened table from the normalised data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Table design: best performances?