Not so long ago, I was introduced to the MaxRows attribute of the ColdFusion CFQuery tag (thanks Joe Rinehart). It's one of those things that you can't believe you didn't know about, but sure enough, there it is. If anyone else doesn't know what it does, it limits the number of records that can be returned by a given query. So, for instance, the following query:
<!--- Limit the record set using MaxRows. ---> <cfquery name="qMovie" datasource="..." maxrows="30"> SELECT * FROM movie ORDER BY name ASC </cfquery>
... will only return the TOP 30 rows to the ColdFusion memory space since it's MaxRows attribute is set to "30." This is similar to the way the TOP directive works in SQL server:
<!--- Limit the record set using TOP. ---> <cfquery name="qMovie" datasource="..."> SELECT TOP 30 * FROM movie ORDER BY name ASC </cfquery>
Both the above queries accomplish the same thing by limiting the max number of returned records to 30. However, one is a ColdFusion directive (MaxRows) and one is a SQL directive (TOP). When compared though, MaxRows does pretty well. When I "select *" from a database that has 50,000+ records, using the TOP directive executed in about 30-40 ms. MaxRows returned the same record set in around 125-140 ms. This is about 3-4 times slower... but when you compare that to the query that ran without either the TOP or the MaxRows directive (on average 400-450 ms), it's a pretty darn good compromise when the SQL TOP directive is not available.
I don't know why MaxRows executes faster than the query that has neither TOP nor MaxRows. I assume that ColdFusion is basically cutting off the database stream once the "MaxRows" number of records have been returned (but this is just theory). I wonder how this affects the SQL Server. I wonder if, from the SQL standpoint, it is still returning all those rows? Does the SQL server still have to amass the massive record set?
Efficiency aside, MaxRows is sometimes the only option available, such as when performing a ColdFusion query of queries (which is where Joe jumped in and pointed out my ignorances).
Want to use code from this post? Check out the license.