On Wed, Nov 10, 2010 at 12:40 AM, Richard White wrote: > [query with 6600 cfqueryparams taking 9 seconds vs. 1 for plain SQL]
> In understand that adding the cfqueryparam is adding approx 6600 validations, > however would it really be the cause of slowing this query down so much? cfqueryparam is not adding 6600 validations. Each use of cfqueryparam is causing one variable to be set on the database side. So without cfqueryparam you are sending 1 SQL statement to the JDBC driver, with cfqueryparam you are first sending a prepare command with the 'structure' of the query, then the command to create an instance of it, then 6600 commands to set the 6600 parameters, then the command to execute it. I am not familiar with the wire protocol for MySQL and can not look into the propietary JDBC driver Adobe uses for MySQL, but I would not be surprised if this were implemented as a synchronous protocol and there is significant roundtripping between the database server and ColdFusion. You are testing the pathological worst case scenario for cfqueryparam. In addition to the overhead from specifiying the variables in separate statements, you add the overhead for preparing the query and caching the execution plan, and then you only execute it once. But anyway, do you really care? If this is part of a scheduled job that takes a few hours anyway ... Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339098 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
