Strange Caching

A thing I noticed while playing with some performance tests is that MX has a strange behaviour when caching queries. Well in fact I guess it is not strange but worth mentioning.

The last couple of days there was a larger discussion on CF-Talk on whether to cache one large query or many smaller ones.
The thing is that large queries, even if cached consume some time when refeched from Coldfusions memory.
In my particular case I pulled a query with 120000 records in order to test some performance switches in Railo. Just for fun, since I didn't want to wait for the SQL-Server to fetch all the records from the database again I wanted them to stay cached for a couple of minutes. Here's the code I used:

My query returns 120,000 records. SQL-Server delivers them in 1.7 seconds. The funny thing is, that when I reexecute the same template again within the cached time Coldfusion still needs over a second to fill the query variable with the cached query.

The results
Execution	CFMX	Railo
First (Ø):	1,721ms	704ms
Further (Ø):	1,157ms	0ms

If I turn on debugging just for one case it displays:

---

SQL Queries

getRecords (Datasource=davidoff, Time=1265ms, Records=121267, Cached Query) in D:\Projects\davidoff\test.cfm @ 08:40:55.055

---

In fact I really do not know why. I haven't looked at the generated .java file but it seems that the cached query is refilled record by record from a cached variable into my local variable called "getRecords".

This way of executing has imho two drawbacks: 1. Coldfusion consumes twice as much memory for caching one query. First for the cached one and second for the copied on (assuming the query get's copied, which it seems)
2. Coldfusion takes mor time to fill the query even if cached since the records have to be copied from the cached query into the local query variable.

Well this is the only explanation I have for this effect. Maybe there is an other reason for this.
Railo caches a query in a different way. When a query get's cached and an other query needs to be filled with the same cached results, Railo points to the same cached query without refilling anything.
You may argue what happens when a cached query is changed by using queryAddRow(), or querySetCell()?
Railo only in this case copies the cached query into a new one and applies the functions. Since the changing of a query is extremely rare, this is much more performant than copying it everytime you access a cached query.