Well.. what one of our former developers did was create this master query and have quite a few sections of the site reference it. Of course this ran for a couple years and we never noticed it until now when a few of the pages starred to run slow. Turned on debug output and boom.. there is was. We are working on the table to speed it up. I always though the execution time would be close to 0 with cached queries, but I understand now that with a large query this may not be the case.
Thanks Jacob -----Original Message----- From: Mike Chabot [mailto:[email protected]] Sent: Monday, December 19, 2011 5:08 PM To: cf-talk Subject: Re: Cached query with execution time? The 672ms time likely has something to do with the 100,000+ records in the query being loaded into a variable. Otherwise you are correct in that the times for small cached queries should be close to 0. I think that query is too big to put into ColdFusion's cache. It might be faster to execute every detailed query against the database. With the correct database index, such as an index on release date, company and title, you might be able to get that detailed query down to 30ms without the cache, rivaling what you are seeing with the QoQ in CF. If that query is taking more than four seconds it likely means that the database is doing a complete table scan because it doesn't have a good index to use. -Mike Chabot On Mon, Dec 19, 2011 at 6:50 PM, Jacob <[email protected]> wrote: > > It this right? I thought if your cached a query there would be no > execution time for the cached query. If I do not cache it, it takes > about 4 seconds to run. I am trying to speed up a section of our > site. This is a master query and I use query of a query to reference it. > > Master query > > DVDList (Datasource=xxx, Time=672ms, Records=116765, Cached Query) > Select a.stock, a.title, a.company, , a.releasedate, b.starring > From DVD a, DVDstarring b > where a.stock = b.stock and releasedate < '12/19/2011' > order by a.releasedate DESC, a.title > > Detailed query > > ListCompanyDVD (Datasource=, Time=31ms, Records=1519) > SELECT stock, title, company, releasedate, starring > FROM DVDList > WHERE company=? > > Query Parameter Value(s) - > Parameter #1(cf_sql_char) = VIV > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349221 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
