> -----Original Message----- > From: Gert Franz [mailto:[EMAIL PROTECTED] > Sent: Friday, April 20, 2007 2:19 AM > To: CF-Talk > Subject: Re: [OT] Generic Database Design Opinions? > > Hi Jim, > > it is an every day problem. Like Deanna said. Materialize your query > table into a new (maybe even in an other database) table. The thing is, > that this table would be generated eg. once every night. Then on this > new table generate all kinds of indexes. Since it is not subject to a > lot of changes (only once a night). > So your new table is not represented by a view or 'on the fly' query > but > by a physical table with primary key, clustered and non clustered > indexes.
Right now I don't think that would fly - I'm not sure what the acceptance level for stale data actually is, but I know that it's much less than a day. I know that Live would be preferred, but my impression (I'm new to the team) is that not more than 10 minutes or so is the requirement. For a little more background: The various business unit archives are actually generated by a mainframe vendor application. We have no control over it in that respect - it creates the archives as it does. But the vendor app itself doesn't support searching over multiple archives, thus our consolidation table. That table, now, is populated (live) via triggers on the individual archive tables (my understanding is that updates are regular, but not over-whelming: perhaps several hundred new documents per day... although that could be per archive). To initial goal is to improve the time-to-market for new business unit archives and reducing (or eliminating) modifications needed to the consolidated table is a big part of that. So we're looking at options. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275925 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
