One Star jgo
One Star

Exception in component tSalesforceInput_ QUERY_TIMEOUT: Your query re

I have a job with multiple sub-jobs and having multiple look-ups to Salesforce data and all the look-ups use the tSalesforceConnection for making a connection with the same login. I have only one look-up tSalesforceInput to a custom object using a new connection using the same login - If I use the tSalesforceConnection for this look-up to a custom object it always times out. Hence I have used a separate connection for this - it works sometimes and many times it times out, so I have increased the timeout to 120,0000. 
Please advice what do I do to get rid of the timeout errors and have the job complete successfully every single time?
Thank you!

Starting job UploadSFData_MAUAT at 10:26 21/07/2014.
connecting to socket on port 3801
Exception in component tSalesforceInput_9
com.salesforce.soap.partner.UnexpectedErrorFault: QUERY_TIMEOUT: Your query request was running for too long.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.salesforce.soap.partner.SforceServiceStub.query(
at org.talend.salesforce.SforceManagementImpl.query(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tSalesforceInput_9Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tMSSqlInput_4Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tMSSqlInput_5Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tMSSqlInput_3Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tMSSqlInput_2Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tMSSqlInput_1Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tJava_1Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.tSalesforceConnection_1Process(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.runJobInTOS(
at salesforceupload.uploadsfdata_mauat_0_1.UploadSFData_MAUAT.main(
Job UploadSFData_MAUAT ended at 10:47 21/07/2014.


Seventeen Stars

Re: Exception in component tSalesforceInput_ QUERY_TIMEOUT: Your query re

I would say you should make the filter a bit more selective. Probably you can use a SF query which is more related to the main data flow by NOT loading all lookups at once but loading the lookup per input line and use more parameters from the main input flow (of the tMap) to enhance the filter for the SF lookup.
Five Stars

Re: Exception in component tSalesforceInput_ QUERY_TIMEOUT: Your query re

There are two timeouts involved.
The timeout you specify within the Talend components. 60,000 (default) is not long at a minute.
I often increase much higher depending on the expected response time of the query.
In your case, you're probably hitting the timeout described here
How big is the bank account table? You have an un-indexed filter. Could you remove the order by and sort locally? Alternatively, you could look at the Bulk Query component.
One Star jgo
One Star

Re: Exception in component tSalesforceInput_ QUERY_TIMEOUT: Your query re

jlolling - I am fairly new to Talend, would you please describe in detail as to how I could use a SF query related to main data flow per input line and use parameters from the main input flow? Are you referring to setting up an inner join on the tMap for the Bank Account object lookup?
In terms of the filter - I cannot be more selective because I need to lookup all the records with for Affiliate__c = ABT. 
tal00000- I removed the two timeouts involved because I had two separate connections to SF, and now I am using the same connection for all lookups. I also increased the main SF connection timeout to 120,0000 milliseconds which is 20mins.  Note there are 767,218 BA records that I am trying to pull back, and I have limited the schema to return only two columns that are needed. Also I removed the Order By from the filter and that improved the performance. One thing I changed and it made a huge difference is the batch size of the SF connection from 250 to 2000. I tried loading the job thrice and no time outs yet. I will try again next week with a different data set and report the results. Smiley Happy
Note I also tried the Bulk Query option and that was the worst and was taking more time and I had to abort it for every single attempt.