Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

81 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
3383 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  
 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.06 seconds. Powered By: Snitz Forums 2000