TechNote (Archived)

ColdFusion MX 6.1, MX 7.0: CFQUERY, CFSTOREDPROC maxrows behavior

Issue

---

Until recently, ColdFusion MX cfquery andcfstoredproc tags did not honor themaxrows parameter and pass the value on to the database. This means for some migrated applications, there may be a performance penalty or memory footprint issue on large resultsets that the developer thought would be limited by maxrows.

ColdFusion MX still behaves correctly in limiting what's in the final resultset but the actual query retrieves ALL rows before it discards all but maxrows rows from the resultset.

Solution

---

ColdFusion MX 7.0.1 CHF2 corrects this problem and sends the maxrows value through statement.setMaxRows() to the database.

Workaround for ColdFusion MX 6.1 for CFQUERY/CFSTOREDPROC maxrows

The fix was considered too extensive for an MX 6.1 updater hot fix, so workarounds must be used in MX 6.1.

Here are some examples showing how to limit rows in the actual SQL, using SQL Server and Oracle.

SQL Server

Use database-specific row limit hints.

1. Use ROWLIMIT

    <CFQUERY maxrows=50 cachedwithin=...><!--- maxrows left in place because later versions of       ColdFusion MX are fixed --->    set ROWLIMIT 50    select id, content from content_table ...</CFQUERY> 

   
   OR
   
2. Use the SQL Server-specific "TOP N" syntax.

    <CFQUERY maxrows=50 cachedwithin=...>    select TOP 50 id, content from content_table ...</CFQUERY> 

Oracle

Use rownum syntax.

 <CFQUERY maxrows=50 cachedwithin=...>    select id, content from content_table where rownum < 51</CFQUERY> 

The examples above show simple, single table queries. Each database platform and version may use a different directive to limit rows. Refer to your database vendor documentation.

Search Support

---