SQL Server Performance

Newsletter Tip: Triggers for Summary Data

Discussion in 'Please Tell Us What You Think' started by vbkenya, Nov 10, 2003.

  1. vbkenya New Member

    The last sql-server-performance.com newsletter came with this tip:


    quote:If your application needs to retrieve summary data often, but you
    don't want to have the overhead of calculating it on the fly
    every time it is needed, consider using a trigger that updates
    summary values after each transaction into a summary table.

    While the trigger has some overhead, overall, it may be less that
    having to calculate the data every time the summary data is
    needed. You may have to experiment to see which methods is
    fastest for your environment. [6.5, 7.0, 2000]


    IMHO, Indexed Views would also seem ideal for this kind of situation. This will work the same way as indicated above and you don't have to define any triggers. It is also possible to reap further benefits from the indexed view if the index key is used frequently in retrieving your summary data (instead of futher indexing your trigger-generated summary table).

    Any thoughts on this?

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  2. bradmcgehee New Member

    This is a good idea. I wonder which method offers the better performance? Without testing, I wouldn't want to guess.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page