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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2007-01-10 : 12:39:39
|
| Hi,I have a problem and I'm not sure the best solution. We have an application which calculates premiums for a product. The total premium consists of a number of different records (which could be in a few different tables) that are summed together. On a per item basis we just sum the appropriate numbers. Our client would like some reports where we calculate the total premium for a number of records (monthly reports etc). I see two main options:1) write a view that sums these values together. - pros: since it is a calculated field, there is no duplicate data - cons: we are talking about millions of records. I'm concerned about performance where we need to sum values in up to 4 different tables.2) at the time the product is ordered (process is complete), write the total premium to a field. Once the process is complete, the application is read-only and can not be edited - pros: very simple to query this new field - cons: storing a calculated field in the database, goes against some basic database rulesIdeally option 1 is the best but with the number of records involved, it might be problematic. I'm just curious how others might have dealt with this issue.Thanks,NicNic |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-10 : 13:51:29
|
| Disk cost is much higher than calculation cost, so I would expect option 1 would not only be acceptable but would actually perform better than option 2 - the extra disk reads that SQL Server would need to read that extra column would almost certainly be more costly than calculating the value (unless the calculation becomes extremely complex). |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-10 : 13:55:52
|
| I should have added - my previous statements are dependent on you not having to perform extra reads to get the values used in the calculation. If you are having to join extra tables to get the values then option 1 may well be more costly, but if you are already using all the tables in the query, then performing the calculation in the query will not be expensive. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2007-01-10 : 14:58:39
|
| Well, there is a significant performance hit making this calculation via a view. Even though the monthly report would only contain a few thousand recs, the view itself needs to contain an agregate function (There may be 20 or 30 records that need to be summed for each item. Mulitply that by 1 million and it really drags). I've played around with indexed views but run into a few issues.Another not as clean option is removing the calc from the view and just doing everything via a stored procedure. This helps, but performance still drags.Unless someone has any other advice, I'm beginning to lean towards option 2.Nic |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-10 : 17:51:44
|
| OK, so as I said in my second message - if the calculation is hitting lots of extra data (I didn't take "summed" to mean an aggregate in your original post because you said "On a per item basis we just sum the appropriate numbers") then certainly the view could perform badly and calculating and storing the aggregate value in a column will perform much better. Of course as you said before that value is not only to be treated as read only but must be updated if any of the underlying data changes. Depending on the nature of the data that may be trivial or a huge task - but making sure that all modifications are performed through a stored proc does give you the necessary control. |
|
|
|
|
|
|
|
|
structural design: view vs calc field