I'm doing a bit of performance tuning (via CFML) for a client and I ran into a little subtle bug that I thought I'd share with others. One of the methods returned all rows that were within a day based range. By that I mean it allowed you to get entries that were created less than 1 day ago, 2 days ago, 5 days ago, and so forth. The query runs a bit slow but was using query caching.
I noticed something interesting though. The debugging was the query always said that the cache was not enabled. Looking over the query I didn't see anything wrong - at least not at first. Can you see why the query never cached?
<cfquery datasource="#datasource#" cachedWithin="#createTimeSpan(0,0,5,0)#" name="q">
SELECT
Post.postId,
Post.feed
FROM
Post
WHERE
dateTimeAggregated > #dateAdd("h", ((arguments.daysBack * 24) * -1), dateConvert("local2utc", now()))#
ORDER BY
clicks DESC, Post.dateTimeAggregated desc
LIMIT
#arguments.offset#, #arguments.limit#
</cfquery>
I've trimmed the query a bit - but do you see the issue yet? The WHERE clause is the culprit. Remember, the idea was - return records that were made N days ago or sooner. You can do this in SQL completely, but the code did it in ColdFusion with the dateAdd function. Notice how it creates the date value - it subtracts N*24 (N being the number of days) from the current time. So if I run this query at 12:01:01 and then again at 12:01:02, the value of now() has changed. Since the WHERE clause changed, the cached query from before wasn't used. I'm not sure this is the best fix, but I simply tried a hard coded time value:
dateTimeAggregated > #dateAdd("h", ((arguments.daysBack * 24) * -1), "#dateFormat(now(), 'mm/dd/yyyy')#")#
It seemed to work fine. I need to double check that it returns the values right - but the point is - when using dynamic queries and caching, be sure to note exactly how the variables can change. While the "daysBack" variable only has a few options, now() is - obviously - pretty varied.
Archived Comments
Interesting Ray as I am looking at all things caching at the moment, thanks for the post.
Hi Ray
Not that it helps your situation on the CF side of things, but it'd help on the DB if you parameterised that date offset value rather than hard-coding it in the SQL.
--
Adam
CFQUERYPARAM is your friend!
I came across this some time ago in version 5, and the solution I used was to create a rounded value for now() rounding to the nearest 15 minutes in that case solved the problem.
Adam - I'm embarrassed to say - I was so focused on the issue I completely missed the lack of QPs. This site I'm working on has a lot of those and I'm not tasked for fixing them, but certainly in cases like this I might as well go ahead and do it while I'm there.
I'd consider switching the where clause to use the DB's dateDiff() function, but your solution seems to work just as well.
It looks like you are using mysql based on your limit statement. I usually do where dates like....
WHERE datefield > DATE_SUB(CURDATE(),INTERVAL 1 DAY)
In the above example CF still calculates that date range every time the page is viewed, but if your query does that conversion, AND it's cached you are in good shape. In most cases you wouldn't see much of a real world difference between the two, but if the page gets hit a ton it would be worth it to cut that calculation.
As Justin indicated, I recommend using the database native functions--which will help with it's stored index plans (especially in MSSQL.)
I've set up a "request.now" variable that defaults to now(), but can also be specifically set for certain users for testing purposes. It allows us to travel back-and-forward through time to test various date and time specific rules. For example, if shipping deadlines change on Thurday at 2pm, I don't have to wait until Thursday at 2pm to test or modify any inline code anywhere. I just change the global date/time variable to Thursday at 1:59pm and 2:01pm and verify the rules are properly configured.
I have used dates like that to programaticly invalidate the cache.
Set cached after on a query to something like 1/1/1900. Then keep a application or session variable of when things last changed. Then add a where statement to the query: where <cfqueryparam value="#application.postlastchanged#"> = <cfqueryparam value="#application.postlastchanged#">
Which evaluates to a 1=1 type value so does not affect the query but invalidates that last cached query as the query string has changed and give you a new query result. Very helpful for seldome updated values that you want to show up as soon as they are updated on the site.