Salesforce connector: not able to query dataset with 8000 rows

[pranavk]

Hi,

I am able to connect to my Salesforce account using the salesforce connector provided in Teiid embedded. I'm also able to properly query objects (tables) with less number of records, but when I try querying a table with a slightly higher number of records (~8000 rows 8 colums), the connector throws a cxf exception: org.apache.cxf.staxutils.DepthExceededStaxException: reach the innerElementCountThreshold:50000,

As per my understanding, the data transfer is being limited by some cxf default values (i.e. 50000, which doesn't seem to be configurable through the Salesforce connector). Could anyone please provide an insight on this issue/how to get around this?

Thanks,

Pranav

Here's the exception trace for reference:

ERROR [PROCESSOR] TEIID30019 Unexpected exception for request gmJhnaECY4HP.0

org.apache.cxf.staxutils.DepthExceededStaxException: reach the innerElementCountThreshold:50000

     at org.apache.cxf.staxutils.StaxUtils.readDocElements(StaxUtils.java:1262) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.staxutils.StaxUtils.readDocElements(StaxUtils.java:1192) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.binding.soap.saaj.SAAJInInterceptor.handleMessage(SAAJInInterceptor.java:223) ~[cxf-rt-binding

     at org.apache.cxf.jaxws.handler.soap.SOAPMessageContextImpl.getMessage(SOAPMessageContextImpl.java:78) ~[cxf-rt-

     at org.apache.cxf.jaxws.handler.soap.SOAPHandlerInterceptor.createProtocolMessageContext(SOAPHandlerInterceptor.

-2.7.7.jar:2.7.7]

     at org.apache.cxf.jaxws.handler.soap.SOAPHandlerInterceptor.handleMessageInternal(SOAPHandlerInterceptor.java:16

jar:2.7.7]

     at org.apache.cxf.jaxws.handler.soap.SOAPHandlerInterceptor.handleMessage(SOAPHandlerInterceptor.java:124) ~[cxf

7]

     at org.apache.cxf.jaxws.handler.soap.SOAPHandlerInterceptor.handleMessage(SOAPHandlerInterceptor.java:71) ~[cxf-

]

     at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:272) ~[cxf-api-2.7.7.jar:2.

     at org.apache.cxf.endpoint.ClientImpl.onMessage(ClientImpl.java:835) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponseInternal(HTTPConduit.java:1606) ~

:2.7.7]

     at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponse(HTTPConduit.java:1502) ~[cxf-rt-

     at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.close(HTTPConduit.java:1309) ~[cxf-rt-transport

     at org.apache.cxf.transport.AbstractConduit.close(AbstractConduit.java:56) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.transport.http.HTTPConduit.close(HTTPConduit.java:627) ~[cxf-rt-transports-http-2.7.7.jar:2.7.

     at org.apache.cxf.interceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor.handleMessage(MessageSende

.7.7.jar:2.7.7]

     at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:272) ~[cxf-api-2.7.7.jar:2.

     at org.apache.cxf.endpoint.ClientImpl.doInvoke(ClientImpl.java:565) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:474) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:377) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:330) ~[cxf-api-2.7.7.jar:2.7.7]

     at org.apache.cxf.frontend.ClientProxy.invokeSync(ClientProxy.java:96) ~[cxf-rt-frontend-simple-2.7.7.jar:2.7.7]

     at org.apache.cxf.jaxws.JaxWsClientProxy.invoke(JaxWsClientProxy.java:135) ~[cxf-rt-frontend-jaxws-2.7.7.jar:2.7

     at com.sun.proxy.$Proxy248.query(Unknown Source) ~[na:na]

     at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:187) ~[con

.jar:8.8.0.Final]

     at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:158) ~[translato

0.Final]

     at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) ~[teiid-engine-8.8.0.Fin

     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) ~[teiid-engine-8.

     at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) ~[teiid-engine-8.8

     at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) ~[teiid-engine-8.8.0.Fin

     at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) ~[teiid-engine-8.8.0.F

     at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) ~[teiid-engine-8.8.0.F

     at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) ~[teiid-engine-8.8.0.Final.

     at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) ~[teiid-engine-8.8.0.Final.jar:8.

     at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) ~[teiid-engine-8.8.0.Final.ja

     at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) ~[teiid-engine-8.8.0.Final.ja

     at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.8.0.Fina

     at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.8.0.Final.ja

     at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.8.0.Final.jar:8

     at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.8.0.Final.jar:8.

     at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:295) [teiid-engine-8.8.0.Final.jar:8.8.0.F

     at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) ~[na:na]

     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_55]

     at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_55]

     at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:173) [teiid-runtime-8.8.0.Final

     at java.util.concurrent.FutureTask.run(FutureTask.java:262) [na:1.7.0_55]

     at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Final

     at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:258) [teiid-engine-8.8.0.Final

     at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:171) [teiid-runtime-8.8.0.Final

     at com.sun.proxy.$Proxy267.executeRequest(Unknown Source) [na:na]

     at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:634) [teiid-client-8.8.0.Final.jar:8.8.0.Final]

     at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:509) [teiid-client-8.8.0.Final.jar:8.8.0.Final]

     at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:393) [teiid-client-8.8.0.Final.jar:8.8.0.Final]

     at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:327) [teiid-client-8.8.0.Final.jar:8.8.0.Final]

[rareddy]

similar issue reported here http://stackoverflow.com/questions/19416042/cxf-jaxws-issue-in-jboss-as-7-org-apache-cxf-staxutils-depthexceededstaxexcep

The answer seems to be increase  the "org.apache.cxf.stax.maxChildElements" property to higher number in your -cxf.xml file. i.e you need to configure a endpoint cxf configuration file and define this property in it to a higher number.

[pranavk]

Thanks,

The fix you mentioned regarding cxf config file worked fine in making the query run. But the query is taking a very very long time to execute. For my reference, I had created a sample app using the Salesforce Partner API (sf web service connector api). When I ran a query on my Accounts table (having 240 columns) to fetch 600 records, it took about a minute to fetch the query results using the teiid connector, but the same worked in about 3-4 secs using my sample app.

The time frame of 1 minute mentioned above is the time taken to execute line 187 of SalesforceConnectionImpl (Teiid 8.8) as shown below:

qr = sfSoap.query(queryString);

Could you point to a possible reason for this large difference in execution times?

Thanks,

Pranav

[shawkins]

Did you do your run through a profiler?  It looks from your description like the time is being spent below Teiid in the CXF layer.  What stack is the sample app using?

[pranavk]

Yes, the querying (through the CXF layer in the Teiid connector) is where I feel the time is being spent. The sample app I made was using the jar files provided by Salesforce for connectivity. I used the Salesforce Partner API (maven: force-partner-api), which was providing the connection(PartnerConnection); and the Salesforce Web Service Connector (maven: force-wsc) from which I was just using a connection metadata class (ConnectorConfig) in the sample app. This showed a drastic performance improvement as mentioned above.

[shawkins]

Would you be able to provide profiling results for both runs?  If there is a severe performance issue with CXF, then we can look at adding an option to support the saleforce provided connectivity, which may need to be an entirely different resource adapter.

[rareddy]

Your sample app may also be a place to start. Would you be able to share that? I see that SF is also support REST services now, it that what this API using?

[shawkins]

I've been looking into this a little.  Our current logic pulls in force-wsc 26 for the support of bulk operations - which is asynchronous rest.  So we could add other operations over rest as well, but that older force-wsc version doesn't seem to have an equivalent to queryAll, just query.  Also it appears this can work along side CXF - the bulk/rest operations will seemingly use their own transport logic, but reuse the session id obtained from the soap login.  Timeouts and other config settings wouldn't necessarily be applicable.  It also appears some our SOAP logic related to query options is likely incorrect.  So I'll dig in a some more.

[pranavk]

Hi,

Ramesh, my sample app is just using the PartnerConnection class of the Force Partner API (maven: force-partner-api:26.0) which seems to be SOAP based. I was calling the query method of this connection passing the string SOQL to it.

Steven, this PartnerConnection class has the queryAll method, in addition to all other methods that are being used in the current CXF based Teiid SF connector. So should this seemingly faster non-CXF-based API be used in conjunction with the force-wsc for bulk connection in the Teiid connector?

Below is a snippet from that sample code:

Thanks

PartnerConnection connection;

private static void connect()

  {

    String endpoint = "https://www.salesforce.com/services/Soap/u/22.0";

    ConnectorConfig connectorConfig = new ConnectorConfig();

    connectorConfig.setUsername(username);

    connectorConfig.setPassword(password);

    connectorConfig.setServiceEndpoint(endpoint);

    connectorConfig.setAuthEndpoint(endpoint);

    try

    {

      connection = new PartnerConnection(connectorConfig);

      System.out.println("Salesforce connection successful");

    }

    catch(ConnectionException e)

    {

      e.printStackTrace();

    }

  }

   public static void tryQuery()

  {

    String query = "Select Id, Name from Account";

    try

    {

      QueryResult result = connection.query(query);

    }

    catch(ConnectionException e)

    {

      e.printStackTrace();

    }

  }

[shawkins]

Pranav,

I've just about got Teiid's logic using the partner api.  Do you have a query / sample app that I can use for a quick comparison? 

Steve

[pranavk]

Hi,

The code mentioned above (tryQuery method) was the sample app that I used for comparison with the existing Teiid connector. The following is one of the SOQL queries that I used with the Account table of a SF account:

SELECT Account.Id, Account.IsDeleted, Account.MasterRecordId, Account.Name, Account.Type, Account.ParentId, Account.BillingStreet, Account.BillingCity, Account.BillingState, Account.BillingPostalCode, Account.BillingCountry, Account.ShippingStreet, Account.ShippingCity, Account.ShippingState, Account.ShippingPostalCode, Account.ShippingCountry, Account.Phone, Account.Fax, Account.AccountNumber, Account.Website, Account.Sic, Account.Industry, Account.AnnualRevenue, Account.NumberOfEmployees, Account.Ownership, Account.Description, Account.Site, Account.OwnerId, Account.LastActivityDate FROM Account limit 8000

The issue surely becomes more noticeable using larger number of columns and rows (~8000 as mentioned).

Just for reference: In my performance comparisons I also used a free tool called FuseIT SFDC Explorer, which uses the same Partner API underneath. This clearly verified the perf gaps.

Thanks,

Pranav

[rareddy]

May be Pranav can test drive your patch with his queries and recheck the performance?

[shawkins]

I'm also a little unsure about our strategy about updating the partner api version.  We currently target 22, but could go to 32 with this change.

[pranavk]

Sure, I can profile and verify the performance on your patch, if that helps.

[rareddy]

We always been hoping that these versions are backward compatible, and handled as such. I agree it has been a while we had a upgrade of the API in Sales Force translator, we should go ahead and update.