Recently a colleague and I have been working through the beating heart of our flagship application to try and improve performance and throughput. There was nothing wrong with the performance we were getting but we’d got some time to spare and so figured tweaking the code to get the absolute maximum out of it would be a fun excercise for a Thursday afternoon.
One of the areas we looked at was caching some of the common MySQL queries that are called on every request using the CachedWithin attribute. Having merrily rolled this out we fired up our test site expecting pleasing improvements in page load times and instead found that the site was pretty much completely borked.
We checked our syntax (no errors), checked our timespans (fine) and scratched our heads before finally deciding to write it off as a bad job and look at it in the morning.
When we came back to it first thing, fresh and eager to solve the mystery, my colleague divined that the cause of our problems was the use of the MaxRows on cached queries.
Personally I never use the MaxRows parameter on cfquery preferring to use the native MySQL LIMIT option to restrict my datasets but this was legacy code and had MaxRows scattered throughout.
For the purposes of making sure that we were absolutely correct I worked up a very quick test suite (the code is enclosed below) that cached a query and then changed the MaxRows attribute.
As you can see from the screenshot, a modified MaxRows attribute does not update the cached query and is completely ignored by ColdFusion leading to some very confusing results. Effectively, because the first call to the query is limited to 2 rows, subsequent calls of the same query only return 2 rows even though the MaxRows attribute is set to a higher value!
On the other hand LIMIT does cause the query to be re run (one assumes because it physically changes the string that is passed to the MySQL server)
This behaviour has been verified on both ColdFusion 8 and ColdFusion 9 (haven’t tested Railo yet). As I said before, I don’t use MaxRows but I hope this is of use to those of you who do.
The test suite for this is enclosed below (obv you’ll need to change DSNs etc.) and I’ll leave it up to you guys to determine if this is a bug or a feature.
[cf]
<!— 10 second timespan —>
<cfset myTimeSpan = createTimeSpan(0,0,0,10) />
<!— run the query with no caching and no maxrows —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes">
SELECT *
FROM testData
</cfquery>
<p><strong>run the query with no caching and no maxrows</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
<!— run the query with maxrows set to 2 —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes" cachedwithin="#myTimeSpan#" maxrows=’2′>
SELECT *
FROM testData
</cfquery>
<p><strong>run the query with maxrows set to 2</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
<!— run the query with maxrows set to 4 —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes" cachedwithin="#myTimeSpan#" maxrows=’4′>
SELECT *
FROM testData
</cfquery>
<p><strong>run the query with maxrows set to 4</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
<!— run the query with no maxrows —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes" cachedwithin="#myTimeSpan#">
SELECT *
FROM testData
</cfquery>
<p><strong>run the query with no maxrows</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
<!— run the query with no maxrows but with a limit of 3 —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes" cachedwithin="#myTimeSpan#">
SELECT *
FROM testData
LIMIT 3
</cfquery>
<p><strong>run the query with no maxrows but with a limit of 3</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
<!— run the query with no maxrows but with a limit of 4 —>
<cfquery name="testQuery" datasource="rcwdTest" result="queryRes" cachedwithin="#myTimeSpan#">
SELECT *
FROM testData
LIMIT 4
</cfquery>
<p><strong>run the query with no maxrows but with a limit of 4</strong></p>
<cfdump var="#testQuery#" label="testQuery" expand="Yes" />
<cfdump var="#queryRes#" label="queryRes" expand="Yes" />
[/cf]
That is pretty interesting. It is not unexpected however. Maxrows runs the SAME full query as the statement and then truncates it after it is returned from the database. That looks like it then gets stored in memory. Next time you run it, it sees you already have, grabs that result set from memroy, then (probably) tries to apply the max rows to it (or not?… ) and you get the same two record. If you do it the other way — query 4 then 2, I would expect you would see two rows come out as the maxrow is applied to the record set in memory? Or not, it ignore that attribute once it decides it is cached.
So that odd caching you see is really what you would expect. Is it a bug? probably… in a way. In a way it is not….
Until the query()/cfquery actually uses maxrows as a value it injects into the constructed query, it isn’t going to change.
Your solution is correct. If you want to actually have different limits passed in, you have to do it yourself for whatever database you are using (limit, top, where rownumid =, etc).
I rewrote the cfquery for cf8 into a cfc that will do pretty much exactly what you are doing if you want a copy of it for a starting point.
I stumbled across this post because i am trying to use the new query() cfc in cf9 and the ‘caching’ on it is not really working at all… hum….