How can <cfqueryparam> affect performance for constants and null values?

Question

Consider the following:

<cfquery name="aQuery" datasource="#aSource#">
    SELECT aColumn
      FROM aTable
     WHERE bColumn = <cfqueryparam value="#aVariable#" cfsqltype="#aSqlType#" />
       AND cColumn = 'someConstant'
       AND dColumn is null
</cfquery>

If I change

AND cColumn = 'someConstant'

to

AND cColumn = <cfqueryparam value="someConstant" cfsqltype="#aSqlType#" />

Is there a potential performance improvement? Is there potential to hurt performance?

What if I do the same (use cfqueryparam) with AND dColumn is null?

My findings have been inconclusive.

If it's important, assume ColdFusion9 and Oracle db 11g.

EDIT:

I'd just like to reiterate that I'm asking about cfqueryparam tags being used specifically with constants and/or null values and the performance remifications, if any.

Answer 1

Is there a potential performance improvement?

No. Bind variables are most useful when varying parameters are involved. Without them, the database would generate a new execution plan every time the query parameters changed (which is expensive). Bind variables encourage the database to cache and reuse a single execution plan, even when the parameters change. This saves the cost of compiling, boosting performance. There is really no benefit with constants. Since the value never changes, the database will always reuse the execution plan. So there is not much reason to use it on constants.

Is there potential to hurt performance?

I have a seen a few mentions of special cases where using bind variables on constants may actually degrade performance. But that is really on a case-by-case basis.

• http://decipherinfosys.wordpress.com/2007/04/02/scenario-for-using-a-constant-instead-of-a-bind-variable-in-an-oltp-system/
• http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:24110#121568