Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    18

    Unanswered: Main and detail table amount calculations

    Hi all,

    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.

    OR

    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. )

    Suggestions

    (My attitude is going towards the trigger way but 100 percent sure)

  2. #2
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Quote Originally Posted by drewex
    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.

    eg. Fields:
    TicketID, DetailID, MoneyTypeID, Value
    ------------------------------------
    1,1,1,10.00
    1,2,1,30.00
    1,2,2,1.00
    1,2,3,10.50
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  3. #3
    Join Date
    Jul 2006
    Posts
    18
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by drewex
    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.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2006
    Posts
    18
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •