LOL Dave, you crack me up. Michael, your first two assumptions are correct. A cfquery with no params is compiled into a Java java.sql.CallableStatement, and a cfquery with at least one query param compiles into a java.sql.PreparedStatement.
I would recommend you run some tests and use SQL Profiler to capture the SQL being sent to the database. You can see exactly what is happening. As far as your first question-- like Dave said, the only way NOT to use a prepared statement is to drop the query param. Also, on this subject, dropping the number of max pooled statements to 0 or unchecking maintain connections in your data source settings will cause your prepared statements to be unprepared after they are called so they are not reused on subsequent calls. From my understanding, that doesn't necessarily keep the SQL server from caching a reusable execution plan, it simply means the statement must be re-prepared every time as opposed to the CF server saying "hey DB, remember that one statement that I sent you earlier that you told me to reference as prepared statement 37? I want to call it again with these inputs". As far as the second question, the answer is yes, but most people like to overlook it because it is not *generally* true-- and they want people to always use query params. :) Here is some reading on how and why: http://www.codersrevolution.com/index.cfm/2008/7/26/cfqueryparam-its-not-just-for-security-also-when-NOT-to-use-it http://www.coldfusionmuse.com/index.cfm/2008/11/18/performance.and.cfqueryparam ~Brad -------- Original Message -------- Subject: RE: CF prepared statements From: Dave Watts <dwa...@figleaf.com> Date: Wed, July 15, 2009 5:31 pm To: cf-talk <cf-talk@houseoffusion.com> 1. No 2. Yes Dave Watts, CTO, Fig Leaf Software -----Original Message----- From: Michael Christensen <mich...@strib.dk> Sent: Wednesday, 15 July, 2009 18:12 To: cf-talk <cf-talk@houseoffusion.com> Subject: CF prepared statements Okay, so here's how I figure it works: - If you use cfqueryparams in your query, the query is executed as a prepared statement on the SQL server. - If you don't use cfqueryparams, the query is not executed as a prepared statement. Am I right so far? If so, here's a couple of questions: 1) can you have queries with cfqueryparams not be executed as prepared statements on the SQL server? 2) could prepared statements have an adverse effect on performance? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324561 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
