> I don't know the deep technical details, but I do know that many people > argue that using query params actually gives you a performance benefit over > plain SQL (although a lot of people disagree).
This isn't really a matter for debate. In some cases, parameterized SQL is faster. In other cases, it's slower. So, you really have to examine the specifics of your case to determine whether it'll be faster or slower. Fortunately, this is pretty easy to do. But in any case, the primary reason for building prepared statements isn't to make things faster, it's to make them more secure. So, the appropriate default behavior is to parameterize your SQL to prevent SQL injection attacks, and if it's a little slower because of this, so be it. > Regardless of the debate over which method is faster, the way it works is if > you query > param ALL of your variables (including values in the SQL that don't come from > CF), > ColdFusion will create what is called a prepared statement (sometimes called > a parameterized statement, or bind parameter). From what I understand, the > way this works is that ColdFusion "compiles" your query down to machine code > that the DB just executes. If you don't query param every value, the DB has > to compile the statement, which includes syntax checking and all that jazz. This isn't really correct. The query isn't compiled in any case. And a prepared statement doesn't require that every value be represented by a parameter. When you send an SQL statement to the database, that database builds an execution plan. Building an execution plan can be an expensive operation - it can actually take longer than executing the plan once it's built. The database can then cache the execution plan so that if the query is run again, the execution plan can be reused. Prepared statements simply include placeholders where values would go. The database can build a single execution plan, and use it no matter what the values in the placeholders turn out to be for one query or another. If the same execution plan works well for all possible values that you'd plug into those placeholders, your queries will run faster. If, however, you have a set of values that would be better served by a different execution plan, that query may run slower (or may not, depending on how unoptimal the existing execution plan is and whether that is balanced by not having to build the execution plan in the first place). > That said, this does not work if you don't use cfqueryparams for > everything. For example, this query would NOT be a prepared statement (from > what I understand): > > <cfquery name="bday" datasource="#myDSN#"> > select birthDate > from familyGroups > where child = <cfqueryparam value="#form.children#" > cfsqltype="cf_sql_varchar"> > and stillMinor = 1 > </cfquery> > > The reason that it would not be a prepared statement is because of the > "stillMinor = 1" part. Even though that is a static value that never > changes, you still want to cfqueryparam that if you want your sql to be > compiled to a prepared statement. Otherwise the DB server will still have > to do the work when it receives the SQL query from ColdFusion. That's incorrect. The "stillMinor = 1" would not be represented by a placeholder, but "child" would be, and all you need is one placeholder to have a prepared statement. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, onlin ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
