getting exceeded cfhttp timeout error

Please consider the following code:

I am sending a SOAP request, passing a number and getting 7-8 fields of information back. The number which I am passing in my soap envelop is getting pulled from a CSV file of 170,000 records. Here is a code snippet of what I am doing:

<cffile action="READ" file="http://filepath/Myfile.csv" variable="FileContent">
<cfset CSVArray = CSVtoArray(FileContent)>

<cfset CSVArrayLength = ArrayLen(CSVarray)>

Total Records:<cfdump var="#CSVArrayLength#" >

<cfloop index="LoopCount" from = "2" to = "#CSVArrayLength#">

<cfsavecontent variable="soap"><?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
        xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="urn:vtsInfoLookup" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns="http://schemas.xmlsoap.org/wsdl/" targetNamespace="urn:vtsInfoLookup">
    <SOAP-ENV:Header>
        <userName>xyz</userName>
        <password>JiunskeT1</password>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
           <infoLookup SOAP-ENV:EncodingStyle="http://schemas.xmlsoap.org/soap/encoding/" >

                  <Number><cfoutput>#CSVArray[LoopCount][2]#</cfoutput></Number> 
               <userName>xyz</userName>
               <password>passwd</password> 
           </infoLookup>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

</cfsavecontent>

<cfhttp url ="https://myurl/abc.php?wsdl"  method = "post"  result =   "httpResponse" throwonerror=   "Yes">
    <cfhttpparam type="header" name="accept-encoding" value="no-compression" />
            <cfhttpparam type="header" name="content-type" value="application/soap+xml">

            <cfhttpparam type="header" name="content-length" value="#len(soap)#">
            <cfhttpparam type="xml" value="#trim(soap)#">

</cfhttp>

<cfset XMLResponse = XmlParse(httpResponse.fileContent.Trim()) />
    <cfset arrNumber = XmlSearch(XMLResponse,"//*[name()='Number']") />
    <cfset Number = trim(arrNumber[1].xmlText)>


    // Similarly parsing for other 7-8 fields


     <cfquery name="vQuery" datasource="XX.XX.X.XXX">

    INSERT INTO 



          VALUES (<cfqueryparam cfsqltype = "cf_sql_varchar" value = "#trim(Number)#" null = "#NOT len(trim(Number))#"/>,

                     // 7 - 8 fields more here
                   )



    </cfquery>   


</cfloop>

The reason I am starting from 2 here is that my CSV file has column name in first row. The number starts from second row in the CSV. That's the reason I have mentioned #CSVArray[LoopCount][2]# above

I have used the CSVToarray function as mentioned over here

Since, in my Server-->Settings, the value for Timeout Requests after ( seconds) is set to 7200 seconds, my request timed out after 2 hrs with the error message:

The request has exceeded the allowable time limit Tag: cfhttp

So, out of 170,000 records in CSV, it stopped after inserting 19000 records in SQL Server 2008 as a result of request timeout.

Is there a way I can make my code efficient? Read somewhere people suggesting the usage of <cfthread> ?

Solution

Increase http timeout + page time out as well. If you are working with such large no of records always try to divide records into small chunks.

Inserting 17k records and calling insert query in a loop is not feasible.

You can simply increase performance by dividing (example: 17000/2000 = 9 text/SQL files) and use SQL feature to import data into DB from SQL or text file.

queryObj = new query();
queryObj.setDatasource(session.datasource);
result = queryObj.execute(sql="LOAD DATA INFILE '#VARIABLES.textPath#' INTO TABLE tblEmployee FIELDS TERMINATED BY ':,:' LINES TERMINATED BY '\r\n'  (emp_Name,emp_City)");

In text file: new rows are add in new line '\r\n' and fields are delimeted with ':,:'