Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

querycache execution time

Description

Upon testing query caching I found that the results that are returned back from the cached query show the execution time of the original query not the execution time of this current operation. It appears to me that the execution time should accurately reflect how long the current option took.

Environment

None

Attachments

1
  • 10 Oct 2017, 10:52 am

Activity

Show:

Pothys - MitrahSoft 10 October 2017 at 10:53
Edited

I've analyzed this ticket & confirmed that issue. Cached query returns execution time same as execution time of original query.

Below code to reproduce the issue

<cfquery>CREATE TABLE Testing (ID INT NOT NULL PRIMARY KEY)</cfquery> ---> <cfloop from="1" to="1000" index="i"> <cfquery>INSERT INTO Testing (ID) VALUES (#i#)</cfquery> </cfloop> <cfquery name="qry1" >SELECT * FROM Testing where id > 100</cfquery> <cfdump var="#qry1#" label="original" /> <cfquery name="qry2" >SELECT * FROM Testing</cfquery> <cfset cachePut( 'querycache', qry1, createTimeSpan( 0, 0, 30, 0 ) )> <cfset cachedQuery = cacheGet('querycache')> <cfset res = cacheCount()> <cfdump var="#res#" /> <cfdump var="#cachedQuery#" label="CachedQuery" />

Result:

shown in result.png

Zac Spitzer 7 October 2017 at 02:44

btw, with debugging enabled, does Cache Type under sql get ever get populated for you?

there's also this problem with the reporting of queries execution times
https://luceeserver.atlassian.net/browse/LDEV-1521#icft=LDEV-1521 Query Execution Time isn't reported for cached components

Zac Spitzer 7 October 2017 at 02:33

oh yeah, i completely understand how useful caching is....

just been playing around with this, i had to use getTickCount('nano') to really see any timing information,
coz accessing a cached query is already so fast. Cached: true indicates this.

What you are asking is to add a little more overhead to accessing every cached query by adding two timing calls
and then updating the result set, to show cache access time = (0ms/1ms) just for the rare case you're dumping
out the query when Cached: true is already telling you basically the same information?

Isn't seeing that a cached query was slow, thus improving performance/reducing load more useful?

Former user 6 October 2017 at 14:52

Query Caching has become the most important thing we do. DB Servers are running about 100-150 queries per second each so it is important to have caching to offload the requests.

Zac Spitzer 6 October 2017 at 14:01
Edited

the cfdump metadata for a query could do with some formating! does it even show if the query was cached?

I don't really used the query cache stuff myself, i usually cache a parsed object built from the query, i.e keyed by a
struct when i cache stuff.

Unresolved

Details

Assignee

Reporter

Priority

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 6 October 2017 at 11:13
Updated 21 May 2024 at 23:22

Flag notifications