Yeah I thought about that also, but since all my queries, regardless of the ever changing where clause only return a count, never a full record set, I would think that the memory usage would be small or am I wrong? This machine sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I know I can't fit the whole record set in ram.
Jeremy -----Original Message----- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 9:44 AM To: CF-Talk Subject: Re: Large count query > caching The only problem with this is the query can change due to the dynamic building of the where clause. Could do a cached query that selects everything that could be in the where clause then do QoQ on that for dynamic where clause needs. I'd just be a little fearful of how much would need to be selected for that cached query, seems like it could get rather big if accessing 25 million records. On 3/17/06, Mingo Hagen <[EMAIL PROTECTED]> wrote: > > Hi Jeremy, > > Yes, you can very easily cache queries using the cachedwithin="" > attribute of <cfquery> > > Like so: > <cfquery datasource="mydata" name="myquery" > cachedwithin="#createTimeSpan( 1, 0, 0, 0 )#"> > SELECT COUNT( * ) FROM myTable WHERE myDate > '1900-01-01' > </cfquery> > > This will cache your query for one day. You could also put the result of > the query into an application variable or something like that, but this > way the query will only be cached if the query text is the same. > > M. > > > Jeremy Bunton wrote: > > I have a query counting records in a 25 million plus database. Even with > > indexing and what not it takes about 25 seconds to run this query. > (count * > > from table where date > 19000101) Is there a way I could cache the query > so > > that if it is ran in that same way it very quickly returns the full > count. > > I.E. this is a dynamically build query so sometimes it will have a long > > where clause that I may be different than the straight just count > everything > > thing like above. It seems to me that if that query has been ran one > time > > in the same way that query should not have to recount the next time, and > > should just output the count. > > > > Jeremy > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235622 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
