如何< cfqueryparam>影响常量和空值的性能？ [英] How can <cfqueryparam> affect performance for constants and null values?

问题描述

请考虑以下内容：

 < cfquery name =aQuerydatasource =＃aSource＃> 
 SELECT aColumn 
 FROM aTable 
 WHERE bColumn =< cfqueryparam value =＃aVariable＃cfsqltype =＃aSqlType＃/> 
 AND cColumn ='someConstant'
 AND dColumn为null 
< / cfquery> 
  

如果我更改

  AND cColumn ='someConstant'
  

到

  AND cColumn =< cfqueryparam value =someConstantcfsqltype =＃aSqlType＃/> 
  

是否有潜在的性能提升？

如果我用做同样的操作（使用cfqueryparam），并且dColumn为null

如果重要，请假设ColdFusion9和Oracle db 11g。

编辑：

我想重申一下，我要问关于cfqueryparam标签，

解决方案

是否有潜在的性能改善？

否。当涉及变化参数时，绑定变量是最有用的。没有它们，数据库将在每次查询参数改变（这是昂贵的）时生成新的执行计划。绑定变量鼓励数据库缓存和重用一个执行计划，即使参数更改。这节省了编译的成本，提高了性能。用常数确实没有什么好处。由于值永远不会改变，数据库将始终重用执行计划。所以没有太多理由在常量上使用它。

是否有损害效果的潜力？

我看到几个特殊情况下使用绑定变量在常量上可能实际上会降低性能。但这确实是一个个案的基础。

• http://decipherinfosys.wordpress.com/2007/04/02/scenario-for-using-a-constant-instead -ofa-a-bind-variable-in-an-oltp-system /


• http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:24110#121568

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.

解决方案

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

这篇关于如何< cfqueryparam>影响常量和空值的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！