Must you use maxrows="1" when looking for a row having a number primary key?

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

Does ColdFusion possess some type of SQL binding or other things that causes it to be better / faster / superior if maxrows is incorporated or excluded?


When the table is structured correctly (i.e. the area you are restricting on may be the primary key), then restricting by the first is redundant. Obviously, a principal key distinctively identifies one row.

I'd test drive it out:

Run your above code because it is for 10 different values of RowID, and compare it with running the code having a maxrows condition too. If you notice the execution occasions will always be better for that latter, you've your definitive answer!

How that actually works, is the fact that cfquery still attempts to query all of the table row's that suit your where clauses, however it only returns x results.

So not quite ideal for performance.

Another alternative a minimum of in SQL Server, may be the SET ROWCOUNT # command, which really limits the amount of records it returns. However this could affect all cfqueries for those who have check persistant in cfadmin.

Or perhaps in mysql there's the limit command.

If only there is a much better solution, but there is not.

maxrows limits the amount of leads to the query variable after its came back in the database.

If you're only ever coming back just one result then once again to possess any impact whatsoever around the performance of the query.

ColdFusion does permit you to pass bind parameters while using CFQUERYPARAM tag.

For instance:

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

You are able to aspire to enhance the speed in your database by supplying a bind parameter and indicating the qualities to come back. This might permit better query caching and gratifaction enhancements with respect to the database engine you're using.

I'd add its generally safer to make use of CFQUERYPARAM rather than leave variables unqualified and potentially available to SQL injection attacks.