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 <ja...@excaliburfilms.com> 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:349219 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
