On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> 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.
I'm going to assume you've used some sort of profiling (eg Query Analyzer if MS-SQL) to ensure you've got indexes appropriately -- of course hardware/software/config/etc govern speed, but it would be good to make sure you're not using a table scan or other expensive operations in the queries that need the count. You may be able to use some combined indexes to speed things up even more if date is frequently used with other specific fields. You can cache to an application/session/whatever scope variable yourself and set a timer on it. You can use <cfquery cachedwithin>. You'd have to decide how often you need to do the caching depending on how "fresh" the value needs to be. There's a couple more options, depending on your database. You could partition the table into a table where date > 19000101 and one where it's not, then instead of a where clause you can simply count(*), which may be faster depending on the query. In MS-SQL you could create an view WHERE data > 19000101 and index it to virtualize the table and then do your SELECTs against that view (I'm not suggesting just using a normal view -- I'm suggesting what I think MS-SQL calls a "materialized" view). In MySQL you could partion the table into two or more tables and use a MERGE table to combine them when needed. -- John Paul Ashenfelter CTO/Transitionpoint (blog) http://www.ashenfelter.com (email) [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235620 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
