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

问题描述

考虑以下事项:

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

如果我改变了

AND cColumn = 'someConstant'

到

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

是否有潜在的性能改进?是否有可能损害性能?

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

如果我对 AND dColumn is null 做同样的事情(使用 cfqueryparam)会怎样?

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

我的发现尚无定论.

如果这很重要，请假设 ColdFusion9 和 Oracle db 11g.

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

我想重申一下，我在询问 cfqueryparam 标签是否专门用于常量和/或空值以及性能改进(如果有的话).

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.

是否有可能损害性能?

我看到一些特殊情况的提及，在这些情况下，在常量上使用绑定变量实际上可能会降低性能.但这实际上是根据具体情况而定的.

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屋！