getting exceeded cfhttp timeout error

Question

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> ?

you could always increase the timeout just for that page. Or you can use that csv to create a .sql file that you can then execute directly with sql server rather than passing 170k different queries to sql. Or break the inserts into chunks of say, 1000 inserts per chunk. — Kevin B, Sep 04 '14 at 17:28

score 2 · Accepted Answer · answered Sep 05 '14 at 06:41

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 ':,:'

that would be good way to do passing heavy processing to DB. But I reckon you need not convert into multiple files if you are sending it to DB. To be on safe side, just run the process in a separate thread. — CFML_Developer, Sep 05 '14 at 07:22

getting exceeded cfhttp timeout error

1 Answers1