Unanswered: Main and detail table amount calculations
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
(My attitude is going towards the trigger way but 100 percent sure)
I can leave as it is so only when they view the ticket it does the calculation.
Yes, you really only want to calculate results from values that are stored in a database and return those results to the client app/report/whatever. Storing calculated values is usually a bad idea. If I were you, I'd go with a third table that details each money field. So, you have the main table, the table with ticket details, then have another table with money details for the ticket details.
you got the question wrong i really dont have too much of changing the data structure is not an big option since the system is already built. I dont have that much options of adding another detailed money table. I need is to decide if i should use triggers and the main table for the calculations or do the calculations on every view Thanks.
Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
How often do they view it? Oh right right - I wasn't to ask
If they are accessing this via a sproc then the easy method is log every execution (in fact do this for every sproc) to a table and then query your table.
If not then you could run a profiler trace (http://www.developer.com/db/article.php/3482216) save the results to a table or to a trace file (trace file is more efficient and you can dump the results in a table once the trace is over) and query the table. You would be querying the TextData column for the SQL:BatchCompleted event.
sadly on the server there is a wierd problem. Trace is not working. and lets say for how often they look up a ticket is, insanely often. probably the data is viewed alot more often then it is updated. I hope that make sense to help awnser the main part of the question. Thanks