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 cfset sqlstring = / cfset questionIDLen = arrayLen(questionIDArray)!---put this in a variable so it doesn't re-evaluate 2200 times--- cfset questionIDCheck = 0 cfloop index=i from=2 to=#questionIDLen# !---remove if statement so it doesn't evaluate 2200 times either. --- cfset questionIDCheck = isNumeric(questionIDArray[i] parentQuestionIDArray[i] parentLevelArray[i])!---since all your params were checking for a numeric value, lump together and be sure they are all numeric. probably could use a regEx or other cffunctions to accomplish the same thing if this does not fit the bill--- cfif questionIDCheck!---only add to sql string if it passes the numeric check. --- cfset sqlstring = '#sqlstring#,(#questionIDArray[i]#, #parentQuestionIDArray[i]#,#parentLevelArray[i]#)' /cfif /cfloop !---run query. manually add first line since we didn't loop over it. --- cfquery name=insertData datasource=dbname INSERT INTO parentquestions VALUES (cfqueryparam value=#questionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentQuestionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentLevelArray[1]# cfsqltype=cf_sql_bigint maxlength=20) #sqlstring# /cfquery ~| 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
Re: slow cfquery cfqueryparam?
Hey, maybe try something like this? I haven't tested this, but it should theoretically be faster cfset sqlstring = / cfset questionIDLen = arrayLen(questionIDArray)!---put this in a variable so it doesn't re-evaluate 2200 times--- cfset questionIDCheck = 0 cfloop index=i from=2 to=#questionIDLen# !---remove if statement so it doesn't evaluate 2200 times either.--- cfset questionIDCheck = isNumeric(questionIDArray[i] parentQuestionIDArray[i] parentLevelArray[i])!---since all your params were checking for a numeric value, lump together and be sure they are all numeric. probably could use a regEx or other cffunctions to accomplish the same thing if this does not fit the bill--- cfif questionIDCheck!---only add to sql string if it passes the numeric check. --- cfset sqlstring = '#sqlstring#,(#questionIDArray[i]#,#parentQuestionIDArray[i]#,#parentLevelArray[i]#)' /cfif /cfloop !---run query. manually add first line since we didn't loop over it.--- cfquery name=insertData datasource=dbname INSERT INTO parentquestions VALUES (cfqueryparam value=#questionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentQuestionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentLevelArray[1]# cfsqltype=cf_sql_bigint maxlength=20) #sqlstring# /cfquery ~| 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
slow cfquery cfqueryparam?
Hi, I am running the following cfquery which inserts a large amount of rows into a table (approx 2200) cfquery name=insertData datasource=dbname INSERT INTO parentquestions VALUES cfloop index=i from=1 to=#arraylen(questionIDArray)# cfif i neq 1,/cfif (cfqueryparam value=#questionIDArray[i]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentQuestionIDArray[i]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentLevelArray[i]# cfsqltype=cf_sql_bigint maxlength=20) /cfloop /cfquery 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! cfset sqlstring = / cfset sqlstring = INSERT INTO parentquestions VALUES / cfloop index=i from=1 to=#arraylen(questionIDArray)# cfif i neq 1cfset sqlstring = #sqlstring#,/cfif cfset sqlstring = '#sqlstring#(#questionIDArray[i]#,#parentQuestionIDArray[i]#,#parentLevelArray[i]#)' /cfloop cfquery name=insertData datasource=dbname #sqlstring# /cfquery 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
