Salesforce integration: Performance problems with multiple JOINs
wpernath Nov 14, 2010 4:58 PMHi,
I am currently using teiid 7.2.0.final with Salesforce integration to do some reporting projects. Until now everything was straight forward:
- Importing SFDC metadata into designer
- Creating models with some base tables which hide unnecessary columns from user (I do not need to get all 100 columns of an Opportunity)
- etc.
But now I am trying to gather all the data I need for my report. This requires some JOINs like:
select o.Id,
u.Name as OpportunityOwner,
a.Name as AccountName,
[...]
from general.salesforce.Opportunity as o
INNER JOIN general.salesforce.OpportunityLineItem as oli ON oli.OpportunityId = o.id
INNER JOIN general.salesforce.User as u ON o.OwnerId = u.id
INNER JOIN general.salesforce.Account as a ON o.AccountId = a.id
INNER JOIN general.salesforce.PricebookEntry as pbe ON oli.PricebookEntryId = pbe.id AND o.Pricebook2Id = pbe.Pricebook2Id
INNER JOIN general.salesforce.Product2 as p ON pbe.Product2Id = p.id
where 1=1
and o.Fiscal = '2011 3'
and o.RegionOfOrder = 'CE'
and u.name = 'xyz'
So I need to JOIN Opportunities and OpportunityLineItems (OLIs) to get a link to product relevant stuff and so on. Unfortunately, this query takes more than 2 hours to return 56 results.
After materializing User, PriceBookEntry and Product2, I was able to reduce it to 1 hour and 30 minutes (for all calls after the first). But it is still way too slow.
After creating a test w/o OLIs, PricebookEntry & Product2 (so, just SELECTing Opps with User & Account informations), I get the result within 1 minute.
As soon as I add OLIs to the query above (w/o product info), the statements again takes up to 1.5 hours. It looks like the QueryProcessor would fetch all OLIs (which are several millions) first and would then remove those which do not match the OpportunityID.
To clarify if this is true, I've created a simple Java test application which does the following:
1. SELECT all Opportunities with above criteria (result size 36)
2. For each Opp SELECT all OLIs of that Opp
Duration: ~1 minute (due to creating 36 times a new Statement and a new ResultSet)
I've removed the last criteria (u.Name = 'xyz') from the above test and just wanted to SELECT all OLIs for each 100 Opps:
1. SELECT all Opportunities (result size: 1800)
2. For each 100 Opps SELECT all OLIs of those opps by using IN criteria (where a IN ('1', '2'...))
--> Broken! (See exception below)
After that exception, I've done the same test with OR criteria:
1. SELECT all Opportunities (result size: 1800)
2. For each 100 Opps SELECT all OLIs of those opps by using OR criteria (where a = '1' OR a = '2' OR...)
Duration: ~30 seconds. --> This is what I would like to have in the original statement
How can I get a reasonable response time? Thanks a lot for your help.
Cheers,
Wanja
Exception on SELECTing with IN:
select id, sybamount
from general.salesforce.OpportunityLineItem
where 1=1
and OpportunityId IN ('0066000000BmUEsAAN','0066000000DoIHaAAN')
;
2010-11-14 22:53:49,254 ERROR [org.jboss.ws.core.CommonClient] (Worker48_QueryProcessorQueue3578) Exception caught while (preparing for) performing the invocation:
javax.xml.ws.soap.SOAPFaultException: MALFORMED_QUERY: unexpected token: '<EOF>'
at org.jboss.ws.core.jaxws.SOAPFaultHelperJAXWS.getSOAPFaultException(SOAPFaultHelperJAXWS.java:85)
at org.jboss.ws.core.jaxws.binding.SOAP11BindingJAXWS.throwFaultException(SOAP11BindingJAXWS.java:107)
at org.jboss.ws.core.CommonSOAPBinding.unbindResponseMessage(CommonSOAPBinding.java:558)
at org.jboss.ws.core.CommonClient.invoke(CommonClient.java:396)
at org.jboss.ws.core.jaxws.client.ClientImpl.invoke(ClientImpl.java:231)
at org.jboss.ws.core.jaxws.client.ClientProxy.invoke(ClientProxy.java:171)
at org.jboss.ws.core.jaxws.client.ClientProxy.invoke(ClientProxy.java:157)
at $Proxy159.query(Unknown Source)
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:171)
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:137)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:263)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:281)
at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:71)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:123)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:120)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:108)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY: unexpected token: '<EOF>'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.jboss.ws.metadata.umdm.FaultMetaData.toServiceException(FaultMetaData.java:383)
at org.jboss.ws.core.jaxws.SOAPFaultHelperJAXWS.getSOAPFaultException(SOAPFaultHelperJAXWS.java:144)
... 21 more
2010-11-14 22:53:49,255 WARN [org.teiid.CONNECTOR] (Worker48_QueryProcessorQueue3578) Connector worker process failed for atomic-request=NMA5vX6/x2W3.0.1.231
[TranslatorException]MALFORMED_QUERY: unexpected token: '<EOF>'
1 [ResourceException]MALFORMED_QUERY: unexpected token: '<EOF>'
2 [MalformedQueryFault]MALFORMED_QUERY: unexpected token: '<EOF>'
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:141)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:263)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:281)
at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:71)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:123)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:120)
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:108)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: javax.resource.ResourceException: MALFORMED_QUERY: unexpected token: '<EOF>'
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:176)
at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:137)
... 12 more
Caused by: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY: unexpected token: '<EOF>'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.jboss.ws.metadata.umdm.FaultMetaData.toServiceException(FaultMetaData.java:383)
at org.jboss.ws.core.jaxws.SOAPFaultHelperJAXWS.getSOAPFaultException(SOAPFaultHelperJAXWS.java:144)
at org.jboss.ws.core.jaxws.binding.SOAP11BindingJAXWS.throwFaultException(SOAP11BindingJAXWS.java:107)
at org.jboss.ws.core.CommonSOAPBinding.unbindResponseMessage(CommonSOAPBinding.java:558)
at org.jboss.ws.core.CommonClient.invoke(CommonClient.java:396)
at org.jboss.ws.core.jaxws.client.ClientImpl.invoke(ClientImpl.java:231)
at org.jboss.ws.core.jaxws.client.ClientProxy.invoke(ClientProxy.java:171)
at org.jboss.ws.core.jaxws.client.ClientProxy.invoke(ClientProxy.java:157)
at $Proxy159.query(Unknown Source)
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:171)
... 13 more
2010-11-14 22:53:49,256 WARN [org.teiid.PROCESSOR] (Worker47_QueryProcessorQueue3579) Processing exception 'MALFORMED_QUERY: unexpected token: '<EOF>'' for request NMA5vX6/x2W3.0. Exception type org.teiid.core.TeiidProcessingException thrown from sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable more detailed logging to see the entire stacktrace.