Query of Query Performance

[JPfeff]

I'm wondering if anyone has done some good performance tests with Q of Qs? I mainly have used them for relatively small data sets since cfmx first came out, as I remember some of my first experiences with it handling large amounts of data being pretty underwhelming. It would just really start to drag when it had to handle data sets of more than a few hundred records. Has that improved in more recent releases? I would assume its at least better than that, but how much? I am looking at doing an application where I have a large data set that is used continually throughout the application but with a lot of different filters and I was thinking of using a cached query and then Q of Qs to pull the various records I need but was concerned that it couldn't handle large data sets well (say 5-10K records). Has anyone done this kind of thing and could comment on performance ?

John P

[Daverms]

Hi John,

In QOQ, Since the recordsets persist in memory, the performance really depends upon the configuration of your computer in where CF is running..

In the most recent releases, MX7 had undergone many enhancements, you can find more information here, (Under "ColdFusion MX 7 Enhancements" section).

http://www.adobe.com/support/documentation/en/coldfusion/mx7/releasenotes.html#whatsnew

http://www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/86

Where as in ColdFusion 9, check out the "Built-in ORM" feature, which has many nice features.

http://www.adobe.com/devnet/coldfusion/articles/coldfusion9_orm_05.html

HTH

[JPfeff]

I'm really hoping someone might have a bit more real-world experience to offer. The CF7 link is basically just listing feature improvements, it has nothing at all about performance improvement. I had already run across the blog post before and while an interesting discussion, it really doesn't address the performance issue much, it's basically asking the same thing I am, whether it is better to have one big query that you run QofQs against, or hundreds even thousands of smaller cached queries (which can be problematic to flush, other than using cfobjectcache sledgehammer approach). It doesn't include any empiracal comparisons of the two methods, particularly if using very large recordsets. I do disagree that it is mainly dependent on the amount of server memory/resources. Certainly that can play a part, but my experience using QofQs in the past is that it really wasn't the limiting factor, is was more the capability of the function to rapidly search larger recordsets, it simply could not come close to the speed of a database engine in that respect. And I assume that is still the case, but wondered if it had been improved at all, and to what extent.

We're not going to be on CF9 for sometime so ORM really isn't an option.

John P

[Dan_Bracuk]

It should be easy enough to run your own test.  That way you can compare the actual run time to your own acceptable standard.

If you need help setting up a test, please ask.

[ilssac]

No direct personal experience.

I was at a "Birds of a Feather" Pre-MAX conference a few years ago (the one In Los Vegas), and this question was posted to an Adobe ColdFusion engineer.  His answer was basically, "ColdFusion is not a database management system and it never will be."  Going on to advice that heavy data manipulation with SQL should be left to the database management systems that have spent decades, thousands of man hours and millions of dollars to have many very smart people make them as effeicient as possible moving data around with SQL syntax.  Query-of-Query syntax in CF exists for convieance and making easy tasks possible, but not for heavy lifting.  He then advised that if one where going to deal with a large amount of data in ColdFusion's memory, to convert it to an array or structure (hash array).  ColdFusion program written in a compiled, 3rd generation language was much more effecient working with large arrays then it was applying SQL to large record sets.

HTH

Ian

[JPfeff]

Thanks Ian, that was my "gut feeling" but kind of was hoping otherwise as it would solve some issues.

@Dan - certainly to test it myself would be ideal but in addition to all the extra time needed to code it out and test both ways (even just a bare minimum filters) the current data set is not that large yet, it is just anticipated to grow and I didn't want to bother with having to create my own large recordset just to test the theory, if someone else has already done so. I'm kind of surprised that there's such a lack of real information on it.

John P