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 |
|
Cadence75
Starting Member
1 Post |
 Posted - 2009-02-07 : 19:38:07
|
| Hi all, My DB has a huge detail table (customer_sales) and a parent table (customers).CUSTOMERS--------------ID_CUSTOMER int (primary key)CUSTOMER_NAME varchar(60)...CUSTOMER _SALES------------------------ID_CUSTOMER_SALE int (primary key)ID_CUSTOMER intPRICE decimal(9,2)...Total customer sales are important info in customer's profile. This total amount is not directly the sum of customer sales (we have to add taxes, discounts...). So, I can create a view with customer columns + a function that returns total customer sales:VCUSTOMERS---------------SELECT ID_CUSTOMER, CUSTOMER_NAME, dbo.TOTAL_CUSTOMER_SALES(ID_CUSTOMER) AS TOTAL_SALES FROM CUSTOMERSBut this solution has very poor performance on SELECT.Breaking design rules, I can add a TOTAL_SALES column on CUSTOMERS table, and create a trigger on CUSTOMER_SALES (for insert, update & delete) that updates TOTAL_SALES column on CUSTOMERS (trying to mantain integrity on this summary info).My question is: any other design idea to have available this summary info (TOTAL_SALES) for a good performance on SELECT everytime my users want to view a customer profile???In abstract terms, I think this is like "storing cache data", but we have to be sure this cache column (TOTAL_SALES) has correct data (integrity) in every time.Thanks in advance.C. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-07 : 20:35:14
|
| I would create aggregation table(Total Sales) and relate with parent table if this is datawarehouse structure with Dimension and Fact tables(Star Schema). Because sometimes when users need item level information it can achieved as well. |
 |
|
|
tonymorell10
Yak Posting Veteran
90 Posts |
Posted - 2009-02-08 : 12:21:22
|
| If you prefer not to store the total sales in the database and have to maintain the value, is it possible to pull the code out of the function and embed it in the SQL for the view? Also, consider making ID_CUSTOMER the clustered index in CUSTOMER_SALES (if it's not already). That should inprove the performance of the function/total_sales query. |
|
|
|
|
|
|
|
|
Summary column in parent table