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 |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2011-11-30 : 11:47:33
|
| This is more of a curiosity in regards to performance and time to query, but I need to setup a statistics table for our sales team. Basically, for every employee in the database, I need to track stats such as:-Total number of sales-Total profit sold-Total commission-Number of customer complaints-Number of return customers- etc, etcIn total, they have 38 different statistics they want to track. So as I see it, there's two ways I could go about creating these tables:Option #1) Create 3 tables: CREATE TABLE Statistics ( StatisticID int, Name varchar ) CREATE TABLE Employees ( EmployeeID int, FirstName varchar, etc, etc ) CREATE TABLE EmployeeStatistics ( EmployeeStatisticID int, EmployeeID int, StatisticID int, StatisticValue varchar )Option #2) Create a larger single table: CREATE TABLE EmployeeStatistics ( EmployeeStaticID int, EmployeeID int, TotalNumberOfSales int, TotalProfitSold int, NumberOfCustomerComplaints int, etc, etc )----------------------------Normally, I would use option 1 and simply join the tables. However, I was just curious which of these 2 options would perform better. Lets say theoretically, my company had 5 million employees (haha). Which of these 2 options is really the better approach? Which would perform better in a query and take up less resources in the database? Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-30 : 11:55:18
|
| Depends on your hardware and the queries you want to perform and indexes - you can gain a lot from option 1 by taking care over the queries.if you want all the statistics in a single query then probably option 2 - but this means a structure change ofr a new statistic.I would have said go for option 1 but things tend to be more optimised for table scanning now - only way to know is to test.Can you do option 1 but maintain option 2 for querying?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Performance question: Which is faster to query?