thanks for providing the details as to what is happening behind the scenes
> 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:339109 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
