For The Gurus: On-The-Fly aggregation vs. end-of-day aggregation

Heres a question for the DB Gurus (you may need to read it closely) An application has 10 threads, each of them reading about 1 gigabyte records off the network in a constant stream. Each record is about 100 bytes in length and has a composite key (multiple columns make the unique key). This data needs to be stored in the database as is. Additionally, this data needs to be summarized for daily, monthly, quarterly and yearly totals. The BIG QUESTION is - is On-the-fly aggregation better than End-of-day aggregation (both terms are explained below). Points to consider are the extra locks, reads, updates (in on-the-fly) vs. reading over 10Gb of records at end of day, and summarizing them, Issues include what are the DBases of today better tuned towards. What is the feeling about resource contention (About 20% of the records summary updations across all 10 threads will want to update the same records). End-of-Day Aggregation -------------------- Read each record from the network, and write it to INCOMING_TABLE At the end of the day, ^^^^^^^^^^^^^^^^^^^^^ FOR all records For This Day in the INCOMING_TABLE (there may be older days records in this table as well) FETCH record Generate Aggregation Summary record for the KEY in memory End for For all summary records Generated Find a summary record in the daily summary table for this KEY If found, (database will lock record automatically) FETCH summary record from the daily summary table, UPDATE the summary record (database will unlock record automatically) else if not found INSERT new summary record in daily summary table. end if (the updation of the daily summary table will trigger the updation of monthly/quarterly/yearly summaries) End for On-the-fly Aggregation ----------------------- For each record read from the network Write the incoming record to INCOMING_TABLE. Find a summary record in the daily summary for the incoming record's KEY, If found, (database will lock record automatically) FETCH summary record from the daily summary table, UPDATE the summary record (database will unlock record automatically) else if not found INSERT new summary record in daily summary table. end if (the updation of the daily summary table will trigger the updation of monthly/quarterly/yearly summaries) End for +++++++++++++++++++++ Thanks for your response, Ashwin/Pankaj