Subtle little query caching performance issue

This post is more than 2 years old.

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Mike Brunt posted on 8/18/2009 at 7:56 AM

Interesting Ray as I am looking at all things caching at the moment, thanks for the post.

Comment 2 by Adam Cameron posted on 8/18/2009 at 11:00 AM

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

Comment 3 by Al Davidson posted on 8/18/2009 at 2:49 PM

CFQUERYPARAM is your friend!

Comment 4 by Kevin Roche posted on 8/18/2009 at 3:26 PM

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.

Comment 5 by Raymond Camden posted on 8/18/2009 at 3:36 PM

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.

Comment 6 by todd sharp posted on 8/18/2009 at 4:23 PM

I'd consider switching the where clause to use the DB's dateDiff() function, but your solution seems to work just as well.

Comment 7 by Justin Dunham posted on 8/18/2009 at 5:14 PM

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.

Comment 8 by Dan G. Switzer, II posted on 8/18/2009 at 6:26 PM

As Justin indicated, I recommend using the database native functions--which will help with it's stored index plans (especially in MSSQL.)

Comment 9 by James Moberg posted on 8/18/2009 at 8:18 PM

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.

Comment 10 by Daniel D. posted on 8/18/2009 at 9:19 PM

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.