Normally, I always use maxrows attribute of CFQuery when I want to display limited records from table. Sometimes I want to display limited records from table with built-in Microsoft SQL command like TOP in Oracle. Yesterday I found that there is ROWNUM command in Oracle which can display limited records like maxrows in CF and Top in SQL. That's why I feel happy to use this command in my query. On the other hand, I want to test the performance between maxrows and rowcount.

So I've tested as follow

With maxrows

view plain print about
1<cfquery name="qryPerson" datasource="myDSN" maxrows="100">
2    select NAME, DOB from nh_person
3</cfquery>

Query execution time for above statement is 31ms and debugging output is as follow

With rownum

view plain print about
1<cfquery name="qryPerson" datasource="myDSN">
2    select NAME, DOB from nh_person
3    WHERE ROWNUM <= 100
4</cfquery>

Query execution time for above statement is 16ms and debugging output is as follow

So what I want to know is Coldfusion use which Oracle function if we put recordcount into maxrows of CFQuery tag.

This entry was posted on December 21, 2010 at 7:55 PM and has received 1488 views. There are currently 2 comments. Print this entry.