Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Question Storing computed data

    I'm new to database use and design and have a design dilemma.

    I am building a database of events. The database will be added to by a batch process once a day but otherwise it will be read only and will be used to extract various bits and pieces of information about the events, e.g. "all type A events followed within a half hour by a type B event from the same source, with no earlier type B events from this source". I haven't done so yet, but I imagine one can construct complicated queries/joins to find this sort of thing dynamically, but I am wondering if it might not be better to create some auxilliary tables of e.g. a table of event sources with info about first, last event from that source, stuff like that, and actually store it in the database. Is this considered in bad taste? Are query/join optimizers good enough that this sort of in-database caches are not normally used? Of course these aux tables will need to be rebuilt when new data is added, but I'm thinking that's not too bad since that will only happen once day. What do y'all think?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    This is a perfectly valid approach. What you call "auxiliary tables" are implemented as "materialized views" or "materialized query tables" in different DBMSes; they persists results of complex queries and refresh them when the source data change.

    The query optimizers are usually good enough to figure out when to use materialized query tables that benefit performance, if those tables exist.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Auxiliary tables, and even materialized views, are valid options. But make sure you actually have a performance problem querying the raw data before adding needless administrative complexity to your architecture.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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