CF - QoQ vs Query

Question

I've got a notion that calling a query of queries is faster than a query from database, because the slowdown is in the communication between cf and the db. Is this true.

Does that mean that a QoQ within a loop is acceptable, whereas a query within a loop is not.

Answer 1

Full DBMS are highly optimised for processing [appropriately-written] queries, and on a modern network the overhead is not going to be huge.

QoQ are performed using an embedded database, which may or not be well optimised, depending on the type of query being performed.

So, if the database is on a different machine, across a slow network, the QoQ might be less slow in some situations. If you're hitting the database at all, you ideally want to get everything processed appropriately there, in one request, and avoid both round-trips and re-processing in a loop.

Of course, a big benefit of QoQ is that you can use it to process data that doesn't come from a database - such as the results of cfdirectory or a CSV file that has been converted to a query.

ColdFusion performs QoQ by manually parsing the SQL and then looping through the recordset. This makes it efficient for simple operations, such as a two-table join with matching keys, but less efficient for complex combinations (where joining uses multiple columns and/or is not a straight a=b comparison). (Brief info here.)

Railo uses H2. H2 claims to be fast, and their website offers some speed comparisons that suggest it is faster than Derby and MySQL - but of course it would be best to look for independent third-party tests, not to mention that these tests are not guarantees of QoQ performance (which I suspect wont have indexes, for example).

In general: don't make any hard decision without first doing performance testing to determine that you actually need to improve performance, and to be able to objectively determine which method is actually faster.

Answer 2

It depends on the capabilities of your Coldfusion machine as compared to your database server, and the problem that you're trying to solve.

QofQ will generally be very fast for small datasets because it all happens in your server memory. If you try to use QofQ on a large dataset though, your server will start to have problems due to the overhead involved with keeping and processing that data in memory.

Database queries will generally outperform QofQ on large datasets because that's what they are designed for. Databases are good at processing large amounts of data very fast. Use them for that.

If you're considering retrieving a large result set from the database and parsing it with QofQ, that is probably the wrong way to do it. Make the database reduce the results for you instead. If you're asking the database server for this information frequently, then cache it on your server.

Keep in mind that this is all subjective and will depend a lot on your particular problem, load, database and server capabilities.