"hundreds of complex queries ..." I'm not exactly sure if a business function has to go that route... And I agree with Warrent that Stored Procedure, be it MS SQL Server or Oracle or IBM DB2, would help with performance substantially (for one thing, sql exeuction plan has been precompiled...), along the way, UDF, optimized indexes, optimized queries would all helps... of course, db design is very key... looks like you have got Historical Data and Transaction Data all in/with one db. And you may have tons of historical data, no wonder it's so slow...
Another thing, using ajax for data output to web would help too (I'm starting to "play" with this one myself...) >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. > >Please help. > >Thanks, >Ken ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:299806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
