> Hi... I have reporting system that is causing big problems. > This is a e-commerce website that runs heavy database > transactions every day. We have a web-based (coldfusion) > system that displays daily/monthly/yearly sales reports. It > runs heavy queries on a single page that do hundreds of > complex queries running multiple Select Count(), Select Sum() queries. > Some reports take as long as 10 minutes to load just plain text. > > I want to build a new reporting system that stores the > cumulative results of the historical reports in a table, and > then whenever a new transaction comes in, I want to simply > increment the previously stored results by one. In that sense > I am always keeping a running count of the report results. I > want to build a cfc/custom tag that keeps a track of new > transactions at all times. > > That is just an overview of what I want to do. I want to > invite ideas and experiences about such or similar problems > that any of you may have come across and shed some light on > some specifics of how you dealt with the issue.
Running reports against transactional (OLTP) databases often causes this problem, as transactional databases are designed and optimized for adding and changing records, not for reading large numbers of records, aggregating values, etc. You might want to consider using an OLAP database for reporting. OLAP databases are optimized for reporting, instead of processing transactions. To oversimplify a bit, OLAP databases are denormalized, and therefore much faster for reporting. Typically, for this you'd periodically migrate data from your OLTP database to OLAP. Here's an article about using SQL Server's OLAP functionality with CF: http://cfdj.sys-con.com/read/46790.htm Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299791 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
