Re: 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) > Well - don't know if I qualify for this response, but here goes. > > >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, > My thoughts are --- It depends on the needs of the application. If end of day processing is OK, then that's probably what I would do. By doing 'on the fly' processing, you will be locking the "summary" tables by all input threads. This will cause a single point of contention and effectivally single thread the input. However, that will create a LOT of work for the end of day processing. Maybe a comprimise is needed. Suppose instead of keeping all of the summaries in one record, you have a seperate summary record for each input thread. Then during END OF DAY processing, you simply process those records. That'd be pretty fast and would also prevent all of the input threads locking on the same row. Now for my "Quickest of the Quick" solutions. Suppose you fragment the input table and uniquely associate an input thread with a specific fragment? That would result in no contention at all. Madison Pruet