databasecoldfusioncfquery

Should I use "maxrows" in my query? ColdFusion / SQL

Is it necessary to use maxrows="1" when searching for a row with a numeric primary key?

<cfquery maxrows="1">  
   select *
   from table
   where RowID = #NumericVariable#
</cfquery>

Does ColdFusion have some kind of SQL binding or anything else that makes it better / faster / superior if maxrows is included or excluded?

Solution

  • maxrows limits the number of results in the query variable after its returned from the database.

    If you are only ever returning a single result then this is not going to have any impact at all on the performance of your query.

    ColdFusion does allow you to pass bind parameters using the CFQUERYPARAM tag.

    For example:

    <cfquery name="q">
    SELECT property1, property2, property3 
    FROM yourTable 
    WHERE RowID = <cfqueryparam value="#NumericVariable#" cfsqltype="CF_SQL_INTEGER" />
</cfquery>

    You can hope to improve the speed on your database by providing a bind parameter and specifying the properties to return. This may allow for better query caching and performance improvements depending on the database engine you are using.

    I'd add its generally more secure to use CFQUERYPARAM than to leave variables unqualified and potentially open to SQL injection attacks.