> 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?
There will likely be a performance difference between the two queries. In most cases, the first query should perform better. The first query will be able to take advantage of cached execution plans. Assuming that the ID value isn't really hard-coded in the second query, but is something like this: SELECT First, Last FROM Customers WHERE ID='#ID#' it will have a literal value embedded within it when it reaches the database, and won't be able to take advantage of a generic cached execution plan where the ID might contain a different value. On the flip side, if the optimal execution plan differs greatly when given different literal values, you wouldn't want to use a cached execution plan. However, this is unlikely in the case of this query. Finally, you might find that the performance difference is negligible. You would still want to use CFQUERYPARAM to prevent SQL injection attacks. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282830 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
