Yes. The first query uses bind variables to pass the value, while the second uses a literal.
>From a performance standpoint, bind variables are preferable, since Oracle can cache the execution plan for the query, whereas when passing a literal value, each time the query is made a new execution plan is determined. How much a performance boost (if any) you are likely to see depends on the complexity of the query. The sample you give is simple enough that there isn't likely to be a significant difference. It really starts making a difference when there are joins involved and other more complex constructs, since the calculation of the execution plan begins to become significant in those instances. Bind variables are also extremely useful when the variable is a large arbitrary string value where any type of special characters may be involved. The bind variable encapsulates the value, and Oracle can be certain that the string is meant as the variable, and doesn't attempt to interpret it as part of the SQL statement. This makes it more reliable, more performant, and is why using bind variables is an important protection against SQL injection attacks. On 7/3/07, Dave Hatz <[EMAIL PROTECTED]> wrote: > > I am curious if someone could tell me the difference between these 2 > queries from a performance standpoint. We are using CF7 with Oracle 9i. > > SELECT First, Last FROM Customers WHERE ID=<cfqueryparam > cfsqltype="cf_sql_varchar" value="#ID#"> > > vs. > > SELECT First, Last FROM Customers WHERE ID='1234' > > Is there a performance difference between these 2 queries? > > Thanks, > Dave Hatz > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282837 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
