Re: For The Gurus: On-The-Fly aggregation vs. end-of-day aggregation
In article <34FE2C81.64EB@cisco.com>, Ashwin Kumar <askumar@cisco.com> wrote: >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). First off, let me say that each DB does things slightly differently but you should not use locks to load data warehouse data on any of them. Red Brick has a direct loader (rb_tmu) as does Oracle (sqlldr direct=y) and most of the others. Use this feature it's important to avoid locks and redo logs. As far as aggregation goes the choice will probably depend on what you are doing and when. If you load during prime query time then you will need more resources then if you load and aggregate during off hours. If you do not have this option partition the DB so that there will not be contention between the loads and querries. Also if you are using UN*X then you can avoid using lots of disk space by spliting the file and using named pipes. Do a man (UN*X on line manual) on 'tee' and 'mkfifo' Hope this helps Mark Rosenbaum Otey-Rosenbaum & Frazier, Inc. mjr@netcom.com Consultants in High Performance and (303) 727-7956 Scalable Computing and Applications POB 1397 ftp://ftp.netcom.com/pub/mj/mjr/ Boulder CO 80306