ExcelToCI Error Occurred in Routine sendSOAPRequest_SubmitToDB: The Operation Timed Out

When I was trying to upload about 7000 rows using ExcelToCI for a custom component, i was getting  the following error,

Error occurred in routine sendSOAPRequest_SubmitToDB:
Error:
Number: 2147012894
Description: The operation timed out

After checking the web server and appserver access log, I found the following issues and i have followed the following steps to resolve the issues,

1.I have found that ExcelToCI uses SERVERXMLHTTP MSXML 6.0 object to send the SOAP request to the webserver,
Set xHTTP = CreateObject(“MSXML2.SERVERXMLHTTP.6.0?)
This request had a default 30 second timeout for receiving a packet of response data from the target server,
Check the link : http://msdn.microsoft.com/en-us/library/ms760403 for more details on timeouts.

2. After checking the webserver access log (PIA_Access.log), i have observed that some of the POST requests are taking about 59 seconds to complete and hence the error was occurring.

To resolve this issue, i have changed the VB Macro code in ExcelToCI spreadsheet as follows

=======================================================

Added  this line before xHTTP.send xDoc.xml in Staging And Submission Module under function sendSOAPRequest_SubmitToDB
Dim lResolve, lConnect, lSend, lReceive As Long
lResolve = 60 * CLng(1000)
lConnect = 90 * CLng(1000)
lSend = 90 * CLng(1000)
lReceive = 120 * CLng(1000)

xHTTP.setTimeouts lResolve, lConnect, lSend, lReceive
=======================================================

Even after changing this value, the ExcelToCI was still failing.  Once again I checked the web server & appserver log and found that after loading about 50 to 60 rows, request is taking more than five minutes to respond and appserver is killing the appserv thread and therefore I was getting the error ‘The Operation timed out’.

To overcome the issue,one can increase the XMLHTTP timeout and also increase the appserver timeout or find out why a request is taking more than 5 minutes to complete. Using Precise i3 performance monitoring tool and also the live Oracle session, I have found out the following issues,

1. Component Interface was firing sql to fetch location code description, using a view that was not correctly joined with other large table using location code.

2. A Save Edit Peoplecode was written at Level 2 to fetch some data from the Oracle table using SQL Exec. This was causing this sql to fire 160,000 times as it was firing for every row in the scroll as new rows are being added.

I have followed the below steps to improve the performance

Removed the related display field from the page. Alternative option was to tune the sql for related display field.Moved the save edit code to field edit, so that it fires only for the newly inserted rows. Alternative option was to write conditional logic to see if row is changed.

After the above changes, the process finished inserting 7100 rows in 23 minutes. Only two request exceeded the default timeout. One took 32 seconds to respond while the other 35 seconds.

I have also noticed that if chunking factor is reduced to 1 to have smaller number of rows processed, PSAPPSRV is restarting due to recycle count of 5000 being reached, and this results in a login Error and HTML (invalid XML) data is being sent to the excel. The only solution to this issue is to increase the recycle count temporarily and change it back to original. Fortunately this parameter is dynamic and does not require restarting the appserver.