1 2 Previous Next 18 Replies Latest reply on Nov 7, 2014 7:47 AM by shawkins

    Salesforce connector: not able to query dataset with 8000 rows




      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?






      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.


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


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


           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) ~


           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


           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


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


           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]

        • 1. Re: Salesforce connector: not able to query dataset with 8000 rows

          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.

          1 of 1 people found this helpful
          • 2. Re: Salesforce connector: not able to query dataset with 8000 rows



            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?





            • 3. Re: Salesforce connector: not able to query dataset with 8000 rows

              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?

              • 4. Re: Salesforce connector: not able to query dataset with 8000 rows

                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.

                • 5. Re: Salesforce connector: not able to query dataset with 8000 rows

                  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.

                  • 6. Re: Salesforce connector: not able to query dataset with 8000 rows

                    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?

                    • 7. Re: Salesforce connector: not able to query dataset with 8000 rows

                      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.

                      • 8. Re: Salesforce connector: not able to query dataset with 8000 rows



                        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:




                        PartnerConnection connection;

                        private static void connect()


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

                            ConnectorConfig connectorConfig = new ConnectorConfig();








                              connection = new PartnerConnection(connectorConfig);


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


                            catch(ConnectionException e)






                           public static void tryQuery()


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




                              QueryResult result = connection.query(query);


                            catch(ConnectionException e)





                        • 9. Re: Salesforce connector: not able to query dataset with 8000 rows



                          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? 



                          • 10. Re: Salesforce connector: not able to query dataset with 8000 rows



                            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.




                            • 11. Re: Salesforce connector: not able to query dataset with 8000 rows

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

                              • 12. Re: Salesforce connector: not able to query dataset with 8000 rows

                                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.

                                • 13. Re: Salesforce connector: not able to query dataset with 8000 rows

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

                                  • 14. Re: Salesforce connector: not able to query dataset with 8000 rows

                                    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.

                                    1 2 Previous Next