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

In article <19980305164801.LAA09011@ladder03.news.aol.com>, SaTriGuy <satriguy@aol.com> writes > >>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. > Agreed. Fetching data from the database and updating it mulitple times means a lot of overhead communicating with the database engine. >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. > that would also help. Then do a select key,sum(...), sum(..),day_column from INCOMING group by day_column and insert/update as before.. > > > >Madison Pruet -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care