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 1><cfset 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