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
