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 |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2004-10-25 : 10:47:06
|
Hello All,Hope you are all well and dandy! I was wondering when is it acceptable to use derived columns or tables? I know that it is a performance issue, and therefore a play off between storage space and processing time. Is this something that can only really be tested once you have created the database, and begin to simulate the different methods for comparison, or are there general conventions that if you have to perform a certain number of joins, or perform a certain number of calculations, using derived table/columns is a better bet?My concern is with regards to invoicing and mainly reporting. A seperate invoice is generated for each of the following different services for a client:Billing Run PostageStationeryArchive ServiceProgrammingFor each service, a number of calculations are derived:For a billing run:o Loading Document Records: Value obtained by joining 6 tableso Sorting Records: Value obtained by joining 6 tableso A4 pages composed: Value obtained by joining 6 tables o A4 laser printed pages: Value obtained by joining 6 tables and then performing 2 subtractions (further joins) and a division o Documents records processed and despatched: Value obtained by joining 6 tables and then performing 2 subtractions (further joins) and a division o Call analysis processed - etco Electronic bill processing - etco Inserts - etcThe other services are only a couple of calculations, but still involve a fair few joins, and calculations.Forinvoicing purposes, the totals for all aspects of each service have to be calculated and will eventually be transferred to an Accountancy system (using XML for example) to actually generate the invoice. This is done a few times a day. At the end of the month, a report is generated that details the financial totals charged to each client for all invoices. Mangement may require other reports, for example, the totals for each service, and so on, in the future.So to summarise, is it acceptable for me to tally the sub totals for each service together into a permanent table. Therefore, the calulations will have already been done as each service is complete, making inserting the values into the accountancy software quicker (as the figures are contained in the DB and not generated on the fly) and reporting would be simply adding together the totals on a monthly basis, and on different fields (per job, per client, per service) depending on the report requirement.I think that a lot of processing would be required to just obtain the figures for the different services (particularly the Billing Run). Considering we do a number of these runs a day, the number of calculations required for all runs would require a lot of processing. However, if this is just once a month, then does this matter?All views would be appreciated!!!Thanks Hearty head pats |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-25 : 11:44:56
|
| I think that approach is perfectly acceptable. It just depends on your business requirements. For example, if management wanted real time access to the numbers, then your approach wouldn't be optimal. That might be a bad example, but you get the point.-ec |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-10-25 : 12:08:26
|
It is very subjective, but it is always nice to get the views of others if only to clarify that I am on the right track !ThanksHearty head pats |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-10-25 : 16:22:59
|
| What you are thinking about is a very common approach to reporting in data warehousing, and if you are not dependent on real-time data then storing aggregated data in intermediate tables is definetly a good choice. There are of course some maintenance issues (what if an invoice for some reason needs to be changed, etc) that have to be handled but I'm sure you have thought of that allready. I have quite a few of these tables in my database and have only good things to say...as long as you are careful that all intermediate data is updated then I'm all for it.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Using Derived tables or columns