How does <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 any potential for performance degradation?

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

My findings were inconclusive.

If this is important, suppose ColdFusion9 and Oracle db 11g.

EDIT:

I just would like to repeat that I ask you to use cfqueryparam tags that are used specifically with constants and / or null values, and performance updates, if any.

Answer 1

Is there a potential performance improvement?

No. Bind variables are most useful when using various parameters. Without them, the database will generate a new execution plan each time the query parameters are changed (which is expensive). Bind variables cause the database to cache and reuse a single execution plan, even if the parameters change. This saves collection costs, increasing productivity. There is no use for constants. Because the value never changes, the database will always reuse the execution plan. Therefore, it makes no sense to use it in constants.

Is there any potential for performance degradation?

I saw a few mentions of special cases where using binding variables in constants can actually degrade performance. But this is true in each case.

• 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

Answer 2

I don’t think so, since you already have one <cfqueryparam> , and this will turn it into a prepared statement, but I'm not sure.

Answer 3

Using a query parameter will help in two ways.

Firstly, it will protect you from SQLI. It will add a level of protection to ensure that the data in the parameter is expected.

Secondly, you will see an increase in productivity. However, the increase depends on the data schema and indexes. The parameter allows the database to cache the query plan. This speeds up the initial request overhead. The more complex the query, the more important the caching of the query plan becomes.

Also, make sure that you have coverage indexes for all columns in the where clause and that they are in the correct order. If not, the query optimizer may refuse to ignore indexes and go directly to table scanning.