slow cfquery cfqueryparam?
Hi, I am running the following cfquery which inserts a large amount of rows into a table (approx 2200) INSERT INTO parentquestions VALUES , (,,) The query was taking on average 9 seconds to complete. However, sometimes it was erratically taking 40 seconds. I output the query as text, copied it into MySQL and it only took 0.14 seconds. So I then tried to build the sql text without applying the cfqueryparam as below, and it took only 1 second! #sqlstring# 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? If so would there be any alternatives that I am missing? thanks for any help ~| 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:339059 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
Hey, maybe try something like this? I haven't tested this, but it should theoretically be faster INSERT INTO parentquestions VALUES (,,) #sqlstring# ~| 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:339076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
> Hi, > > I am running the following cfquery which inserts a large amount of > rows into a table (approx 2200) Just out of curiosity, where does your insert data originate from? ~| 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:339080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
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
Re: slow cfquery cfqueryparam?
thanks, this solution worked great and is now processing at a steady 2 seconds > Hey, maybe try something like this? I haven't tested this, but it > should theoretically be faster > > > > > > > parentQuestionIDArray[i] & parentLevelArray[i])> > > > > > > > INSERT INTO parentquestions > VALUES ( cfsqltype="cf_sql_bigint" maxlength="20">, value="#parentQuestionIDArray[1]#" cfsqltype="cf_sql_bigint" > maxlength="20">, cfsqltype="cf_sql_bigint" maxlength="20">) > #sqlstring# > ~| 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:339108 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
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
Re: slow cfquery cfqueryparam?
transferring data from one app to another >> Hi, >> >> I am running the following cfquery which inserts a large amount of >> rows into a table (approx 2200) > >Just out of curiosity, where does your insert data originate from? ~| 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:339110 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
>transferring data from one app to another > I'd just use Navicat to transfer the data. No CF needed. Quick and easy. Will ~| 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:339115 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
nice link, thanks will! >>transferring data from one app to another >> > > >I'd just use Navicat to transfer the data. No CF needed. Quick and easy. > >Will ~| 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:339128 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
