SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance question: Which is faster to query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

78 Posts

Posted - 11/30/2011 :  11:47:33  Show Profile  Reply with Quote
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, etc

In 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
Flowing Fount of Yak Knowledge

United Kingdom
3371 Posts

Posted - 11/30/2011 :  11:55:18  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000