1 2 Previous Next 16 Replies Latest reply on Nov 25, 2010 4:58 AM by wpernath

    Salesforce integration: Performance problems with multiple JOINs

    wpernath

      Hi,

       

      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,
        o.Name,
        o.Amount,
        o.StageName,
        o.ForecastCategory,
        oli.SYBAmount,
        oli.OYBAmount,
        oli.TotalPrice,
        oli.Quantity,
        oli.ListPrice,
        oli.UnitPrice,
        p.PrimaryBusinessUnit,
        p.ProductLine,
        p.ProductCode as ProductCode,
        p.Name as ProductName,
        p.Family as ProductFamily,
        p.Description as ProductDescription
      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'

      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.

       

        • 1. Re: Salesforce integration: Performance problems with multiple JOINs
          wpernath

          it looks like I am getting the issue described here:

           

          https://jira.jboss.org/browse/TEIID-211 (subquery optimizations)

           

          Is this related?

           

          Thanks + cheers,

            Wanja

          • 2. Re: Salesforce integration: Performance problems with multiple JOINs
            jdoyle

            Could be related.  The SalesForce API does not support full joins, it supports semi-joins.


            Querying large data sets has been an issue with this connector since it's inception, it's simple a question of pulling all that data across the network.  You've started down that path to the general solution to the issue, caching.  That said there may be other steps we can take to improve the performance.  Can you attach the query plan for the query you want to execute?  (no need to let it complete once the plan is logged).

             

            append SHOWPLAN ON to your query

             

            ~jd

            • 3. Re: Salesforce integration: Performance problems with multiple JOINs
              wpernath

              Please find attached query plan.

               

              Cheers,

                Wanja

              • 4. Re: Salesforce integration: Performance problems with multiple JOINs
                jdoyle

                Wanja,

                 

                Have you read the section about Relationship Queries in the SalesForce translator doc?  It spells out a syntax that you must use in order to have the translator push a join down to SalesForce, rather than pull back all the data and apply the criteria locally.

                 

                http://docs.jboss.org/teiid/7.2.0.Final/reference/en-US/html/translators.html#d0e8723

                 

                Assuming no modifications to your SF schema, I believe you should be able to push the relationship query between Opportunity and OpportunityLineItem down to salesforce, as well as PricebookEntry and OLI.

                 

                I'm not knowledgeable anough about the optimizer to spell out what the optimizer might do to your query if it's provided in the snytax I'm suggesting.  It may not get to the connector without being changed.  In that case, you might try putting the SQL for the relationship queries in procedures, so they wil be executed as desired.  I don't know if there is another way to get the optimizer to leave your query as is.

                 

                Let me know if this helps.

                • 5. Re: Salesforce integration: Performance problems with multiple JOINs
                  wpernath

                  John,

                   

                  thanks for your reply. If I am doing a SELECT like this:

                   

                   

                  select o.Name, oli.TotalPrice, oli.UnitPrice

                  from sfdc.salesforce.OpportunityLineItem oli

                         LEFT OUTER JOIN sfdc.salesforce.Opportunity o ON o.id = oli.OpportunityId

                    where o.Fiscal = '2011 3'

                    and   o.RegionOfOrder = 'CE'

                    and   o.OwnerId = (select Id from general.salesforce.User where Name = 'xyz')

                   

                   

                  The query takes ages.

                   

                  But if I am doing like this (O and OLI changed)

                   

                  select o.Name, oli.TotalPrice, oli.UnitPrice
                  from general.salesforce.Opportunity o LEFT OUTER JOIN general.salesforce.OpportunityLineItem oli ON o.id = oli.OpportunityId
                    where o.Fiscal = '2011 3'
                    and   o.RegionOfOrder = 'CE'
                    and   o.OwnerId = (select Id from general.salesforce.User where Name = 'Dirk Zechiel')

                  select o.Name, oli.TotalPrice, oli.UnitPrice

                  from sfdc.salesforce.Opportunity o

                         LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem oli ON o.id = oli.OpportunityId

                    where o.Fiscal = '2011 3'

                    and   o.RegionOfOrder = 'CE'

                    and   o.OwnerId = (select Id from general.salesforce.User where Name = 'xyz')

                   

                  I get a very quick answer (a few seconds), BUT data from OLI is just NULL. And it looks like the dependent table was not scanned at all.

                   

                  Am I wrong with my interpretation of the link you gave me?

                   

                   

                  Thanks a lot.

                   

                  Cheers,

                    Wanja

                  • 6. Re: Salesforce integration: Performance problems with multiple JOINs
                    wpernath

                    Sorry, It is of course vice versa:

                     

                    This takes ages:

                    LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem oli ON o.id = oli.OpportunityId

                     

                    And this just a few seconds, but with NULL results:

                    LEFT OUTER JOIN sfdc.salesforce.Opportunity o ON o.id = oli.OpportunityId

                     

                    Cheers,

                      Wanja

                    • 7. Re: Salesforce integration: Performance problems with multiple JOINs
                      shawkins

                      Wanja,

                       

                      What you were describing initially (using one side of a join to feed values to the other) we call a dependent join.  We need either costing information or a makedep hint (see the reference guide) to know that we should perform a dependent join.

                       

                      I can see from the attached plan that the source tables do not have costing information.  Typically if you just add table cardinalities (on the table properties in Designer) that will be enough for the planner to automicially figure out the most appropriate join without an explicit hint.

                       

                      As for issues with what is happening with Salesforce pushdown, be sure to turn up logging to detail to get the actual salesforce source query to validate that it should return what you are expecting in the plan.

                       

                      Steve

                      • 8. Re: Salesforce integration: Performance problems with multiple JOINs
                        wpernath

                        Thanks for your answer, Steve!
                        I've turned on DEBUG logging for CONNECTOR and found the following for this query:
                        select o.Name, oli.TotalPrice, oli.UnitPrice
                        from sfdc.salesforce.Opportunity o LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem oli ON o.id = oli.OpportunityId
                          where o.Fiscal = '2011 3'
                          and   o.Region_Of_Order__c = 'CE'
                          and   o.OwnerId = (select Id from general.salesforce.User where Name = 'xyz')
                        2010-11-16 19:55:32,861 DEBUG [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue22) 1Agw56o/Yv9V.0.2.2 Create State
                        2010-11-16 19:55:32,865 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Processing NEW request: SELECT sfdc.salesforce.Opportunity.Name, sfdc.salesforce.OpportunityLineItem.TotalPrice, sfdc.salesforce.OpportunityLineItem.UnitPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.Opportunity.id = sfdc.salesforce.OpportunityLineItem.OpportunityId WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')
                        2010-11-16 19:55:32,876 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Obtained execution
                        2010-11-16 19:55:32,876 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) sfdc.1Agw56o/Yv9V.1Agw56o/Yv9V.0.2:  Incoming Query: SELECT Opportunity.Name, OpportunityLineItem.TotalPrice, OpportunityLineItem.UnitPrice FROM Opportunity LEFT OUTER JOIN OpportunityLineItem ON Opportunity.Id = OpportunityLineItem.OpportunityId WHERE Opportunity.Fiscal = '2011 3' AND Opportunity.Region_of_Order__c = 'CE' AND Opportunity.OwnerId = '00560000000mlXDAAY'
                        2010-11-16 19:55:32,877 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) sfdc.1Agw56o/Yv9V.1Agw56o/Yv9V.0.2:  Executing Query: SELECT Opportunity.Name, (SELECT OpportunityLineItem.TotalPrice, OpportunityLineItem.UnitPrice FROM OpportunityLineItems) FROM Opportunity WHERE (Opportunity.Fiscal = '2011 3') AND ((Opportunity.Region_of_Order__c = 'CE') AND (Opportunity.OwnerId = '00560000000mlXDAAY'))
                        2010-11-16 19:55:32,910 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker3_QueryProcessorQueue23) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Executed command
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Sending results from connector
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) SalesForce Object Name = Opportunity
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) FieldCount = 2
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) Field # 0 is Name
                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) Field # 1 is OpportunityLineItems
                        2010-11-16 19:55:35,622 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Obtained last batch, total row count: 46
                        2010-11-16 19:55:35,623 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Processing Close : SELECT sfdc.salesforce.Opportunity.Name, sfdc.salesforce.OpportunityLineItem.TotalPrice, sfdc.salesforce.OpportunityLineItem.UnitPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.Opportunity.id = sfdc.salesforce.OpportunityLineItem.OpportunityId WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')
                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) SynchQueryExecution.close() called
                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Closed execution
                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Remove State
                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Closed connection
                        So it seems that the query is properly transformed to the model to sfdc. Unfortunately, the actual values are not correctly read from the subselect: The result set looks like:
                        <opportunity name>, null, null
                        <opportunity name>, null, null
                        <opportunity name>, null, null
                        Thanks for any hints.
                        Cheers,
                          Wanja

                        Thanks for your answer, Steve!

                         

                        I've turned on DEBUG logging for CONNECTOR and found the following for this query:

                         

                        select o.Name, oli.TotalPrice, oli.UnitPrice

                        from sfdc.salesforce.Opportunity o LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem oli ON o.id = oli.OpportunityId

                          where o.Fiscal = '2011 3'

                          and   o.Region_Of_Order__c = 'CE'

                          and   o.OwnerId = (select Id from general.salesforce.User where Name = 'xyz')

                         

                         

                        2010-11-16 19:55:32,861 DEBUG [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue22) 1Agw56o/Yv9V.0.2.2 Create State

                        2010-11-16 19:55:32,865 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Processing NEW request: SELECT sfdc.salesforce.Opportunity.Name, sfdc.salesforce.OpportunityLineItem.TotalPrice, sfdc.salesforce.OpportunityLineItem.UnitPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.Opportunity.id = sfdc.salesforce.OpportunityLineItem.OpportunityId WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')

                        2010-11-16 19:55:32,876 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Obtained execution

                        2010-11-16 19:55:32,876 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) sfdc.1Agw56o/Yv9V.1Agw56o/Yv9V.0.2:  Incoming Query: SELECT Opportunity.Name, OpportunityLineItem.TotalPrice, OpportunityLineItem.UnitPrice FROM Opportunity LEFT OUTER JOIN OpportunityLineItem ON Opportunity.Id = OpportunityLineItem.OpportunityId WHERE Opportunity.Fiscal = '2011 3' AND Opportunity.Region_of_Order__c = 'CE' AND Opportunity.OwnerId = '00560000000mlXDAAY'

                        2010-11-16 19:55:32,877 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) sfdc.1Agw56o/Yv9V.1Agw56o/Yv9V.0.2:  Executing Query: SELECT Opportunity.Name, (SELECT OpportunityLineItem.TotalPrice, OpportunityLineItem.UnitPrice FROM OpportunityLineItems) FROM Opportunity WHERE (Opportunity.Fiscal = '2011 3') AND ((Opportunity.Region_of_Order__c = 'CE') AND (Opportunity.OwnerId = '00560000000mlXDAAY'))

                        2010-11-16 19:55:32,910 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker3_QueryProcessorQueue23) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Executed command

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Sending results from connector

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) SalesForce Object Name = Opportunity

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) FieldCount = 2

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) Field # 0 is Name

                        2010-11-16 19:55:35,621 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) Field # 1 is OpportunityLineItems

                        2010-11-16 19:55:35,622 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue23) 1Agw56o/Yv9V.0.2.2 Obtained last batch, total row count: 46

                        2010-11-16 19:55:35,623 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Processing Close : SELECT sfdc.salesforce.Opportunity.Name, sfdc.salesforce.OpportunityLineItem.TotalPrice, sfdc.salesforce.OpportunityLineItem.UnitPrice FROM sfdc.salesforce.Opportunity LEFT OUTER JOIN sfdc.salesforce.OpportunityLineItem ON sfdc.salesforce.Opportunity.id = sfdc.salesforce.OpportunityLineItem.OpportunityId WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')

                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) SynchQueryExecution.close() called

                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Closed execution

                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Remove State

                        2010-11-16 19:55:35,625 DEBUG [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue25) 1Agw56o/Yv9V.0.2.2 Closed connection

                         

                         

                        So it seems that the query is properly transformed to the model to sfdc. Unfortunately, the actual values are not correctly read from the subselect: The result set looks like:

                         

                        <opportunity name>, null, null

                        <opportunity name>, null, null

                        <opportunity name>, null, null

                         

                        Thanks for any hints.

                         

                         

                        Cheers,

                          Wanja

                        • 9. Re: Salesforce integration: Performance problems with multiple JOINs
                          wpernath

                          Steve,

                           

                          I have another problem. After I've seen that teiid does not gather subquery results of OUTER JOINs as expected (see above), I've tried to give hints on OpportunityLineItem (OLI) as mentioned by you. It looks like that the optimizer is now correclty selecting OLIs with a set of OpportunityId's instead of querying all OLIs.

                           

                          Unfortunately, I get another exception now:

                           

                          2010-11-17 12:22:55,831 DEBUG [org.teiid.CONNECTOR] (Worker26_QueryProcessorQueue258) h/IKzLdeJsr/.0.4.32 Create State

                          2010-11-17 12:22:55,838 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) h/IKzLdeJsr/.0.4.32 Processing NEW request: SELECT sfdc.salesforce.Opportunity.id, sfdc.salesforce.Opportunity.Name FROM sfdc.salesforce.Opportunity WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')

                          2010-11-17 12:22:59,624 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker27_QueryProcessorQueue259) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.

                          2010-11-17 12:23:01,044 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) Login was successful for username ***

                          2010-11-17 12:23:01,048 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker27_QueryProcessorQueue259) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.

                          2010-11-17 12:23:01,587 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) h/IKzLdeJsr/.0.4.32 Obtained execution

                          2010-11-17 12:23:01,587 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) sfdc.h/IKzLdeJsr/.h/IKzLdeJsr/.0.4:  Incoming Query: SELECT Opportunity.Id, Opportunity.Name FROM Opportunity WHERE Opportunity.Fiscal = '2011 3' AND Opportunity.Region_of_Order__c = 'CE' AND Opportunity.OwnerId = '00560000000mlXDAAY'

                          2010-11-17 12:23:01,587 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) sfdc.h/IKzLdeJsr/.h/IKzLdeJsr/.0.4:  Executing Query: SELECT Opportunity.Id, Opportunity.Name FROM Opportunity WHERE (Opportunity.Fiscal = '2011 3') AND ((Opportunity.Region_of_Order__c = 'CE') AND (Opportunity.OwnerId = '00560000000mlXDAAY'))

                          2010-11-17 12:23:01,589 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker27_QueryProcessorQueue259) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.

                          2010-11-17 12:23:02,061 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) h/IKzLdeJsr/.0.4.32 Executed command

                          2010-11-17 12:23:02,061 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) h/IKzLdeJsr/.0.4.32 Sending results from connector

                          2010-11-17 12:23:02,061 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) SalesForce Object Name = Opportunity

                          2010-11-17 12:23:02,061 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) FieldCount = 1

                          2010-11-17 12:23:02,061 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) Field # 0 is Name

                          2010-11-17 12:23:02,062 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue259) h/IKzLdeJsr/.0.4.32 Obtained last batch, total row count: 46

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker26_QueryProcessorQueue260) h/IKzLdeJsr/.0.8.33 Create State

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue261) h/IKzLdeJsr/.0.4.32 Processing Close : SELECT sfdc.salesforce.Opportunity.id, sfdc.salesforce.Opportunity.Name FROM sfdc.salesforce.Opportunity WHERE (sfdc.salesforce.Opportunity.Fiscal = '2011 3') AND (sfdc.salesforce.Opportunity.Region_Of_Order__c = 'CE') AND (sfdc.salesforce.Opportunity.OwnerId = '00560000000mlXDAAY')

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue261) SynchQueryExecution.close() called

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue261) h/IKzLdeJsr/.0.4.32 Closed execution

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue261) h/IKzLdeJsr/.0.4.32 Remove State

                          2010-11-17 12:23:02,072 DEBUG [org.teiid.CONNECTOR] (Worker27_QueryProcessorQueue261) h/IKzLdeJsr/.0.4.32 Closed connection

                          2010-11-17 12:23:02,156 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue262) h/IKzLdeJsr/.0.8.33 Processing NEW request: SELECT sfdc.salesforce.OpportunityLineItem.PricebookEntryId, sfdc.salesforce.OpportunityLineItem.OpportunityId, sfdc.salesforce.OpportunityLineItem.TotalPrice, sfdc.salesforce.OpportunityLineItem.Year1Amount__c FROM sfdc.salesforce.OpportunityLineItem WHERE sfdc.salesforce.OpportunityLineItem.OpportunityId IN ('0066000000BUu49AAD', '0066000000BVSQKAA5', '0066000000ByFz5AAF', '0066000000BzRNgAAN', '0066000000C0AwBAAV', '0066000000CPzjMAAT', '0066000000CSrOJAA1', '0066000000CT42uAAD', '0066000000D4T4hAAF', '0066000000DIT6XAAX', '0066000000DIT7RAAX', '0066000000DIwG8AAL', '0066000000DJQVQAA5', '0066000000DKq0aAAD', '0066000000DKurmAAD', '0066000000DMKouAAH', '0066000000DMONdAAP', '0066000000Dne6hAAB', '0066000000Do7EcAAJ', '0066000000DoIHaAAN', '0066000000DoIk5AAF', '0066000000DoImMAAV', '0066000000E2WrMAAV', '0066000000EGwNiAAL', '0066000000EHmgsAAD', '0066000000EiVGoAAN', '0066000000EiYvqAAF', '0066000000Ej31kAAB', '0066000000Ej7xAAAR', '0066000000EjuG2AAJ', '0066000000EkwGQAAZ', '0066000000ElELyAAN', '0066000000ElOq9AAF', '0066000000EldgCAAR', '0066000000Em9u4AAB', '0066000000EmPSeAAN', '0066000000EmUboAAF', '0066000000FKURLAA5', '0066000000FKZhCAAX', '0066000000FKezpAAD', '0066000000FLIyMAAX', '0066000000FLdfBAAT', '0066000000FMovPAAT', '0066000000FO2KbAAL', '0066000000FO2N7AAL', '0066000000FO3A7AAL')

                          2010-11-17 12:23:02,162 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue262) h/IKzLdeJsr/.0.8.33 Obtained execution

                          2010-11-17 12:23:02,162 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue262) sfdc.h/IKzLdeJsr/.h/IKzLdeJsr/.0.8:  Incoming Query: SELECT OpportunityLineItem.PricebookEntryId, OpportunityLineItem.OpportunityId, OpportunityLineItem.TotalPrice, OpportunityLineItem.Year1Amount__c FROM OpportunityLineItem WHERE OpportunityLineItem.OpportunityId IN ('0066000000BUu49AAD', '0066000000BVSQKAA5', '0066000000ByFz5AAF', '0066000000BzRNgAAN', '0066000000C0AwBAAV', '0066000000CPzjMAAT', '0066000000CSrOJAA1', '0066000000CT42uAAD', '0066000000D4T4hAAF', '0066000000DIT6XAAX', '0066000000DIT7RAAX', '0066000000DIwG8AAL', '0066000000DJQVQAA5', '0066000000DKq0aAAD', '0066000000DKurmAAD', '0066000000DMKouAAH', '0066000000DMONdAAP', '0066000000Dne6hAAB', '0066000000Do7EcAAJ', '0066000000DoIHaAAN', '0066000000DoIk5AAF', '0066000000DoImMAAV', '0066000000E2WrMAAV', '0066000000EGwNiAAL', '0066000000EHmgsAAD', '0066000000EiVGoAAN', '0066000000EiYvqAAF', '0066000000Ej31kAAB', '0066000000Ej7xAAAR', '0066000000EjuG2AAJ', '0066000000EkwGQAAZ', '0066000000ElELyAAN', '0066000000ElOq9AAF', '0066000000EldgCAAR', '0066000000Em9u4AAB', '0066000000EmPSeAAN', '0066000000EmUboAAF', '0066000000FKURLAA5', '0066000000FKZhCAAX', '0066000000FKezpAAD', '0066000000FLIyMAAX', '0066000000FLdfBAAT', '0066000000FMovPAAT', '0066000000FO2KbAAL', '0066000000FO2N7AAL', '0066000000FO3A7AAL')

                          2010-11-17 12:23:02,162 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue262) sfdc.h/IKzLdeJsr/.h/IKzLdeJsr/.0.8:  Executing Query: SELECT OpportunityLineItem.PricebookEntryId, OpportunityLineItem.OpportunityId, OpportunityLineItem.TotalPrice, OpportunityLineItem.Year1Amount__c FROM OpportunityLineItem WHERE

                          2010-11-17 12:23:02,167 ERROR [org.jboss.remoting.transport.http.HTTPClientInvoker] (Worker28_QueryProcessorQueue262) Error creating SSL Socket Factory for client invoker: Error initializing socket factory SSL context: Can not find truststore url.

                          2010-11-17 12:23:02,400 ERROR [org.jboss.ws.core.CommonClient] (Worker28_QueryProcessorQueue262) 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 $Proxy157.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-17 12:23:02,402 WARN  [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue262) Connector worker process failed for atomic-request=h/IKzLdeJsr/.0.8.33

                          [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 $Proxy157.query(Unknown Source)

                              at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:171)

                              ... 13 more

                           

                          [...]

                           

                          2010-11-17 12:23:02,448 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue264) SynchQueryExecution.close() called

                          2010-11-17 12:23:02,448 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue264) h/IKzLdeJsr/.0.8.33 Closed execution

                          2010-11-17 12:23:02,448 WARN  [org.teiid.PROCESSOR] (Worker26_QueryProcessorQueue263) Processing exception 'MALFORMED_QUERY: unexpected token: '<EOF>'' for request h/IKzLdeJsr/.0.  Exception type org.teiid.core.TeiidProcessingException thrown from sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable more detailed logging to see the entire stacktrace.

                          2010-11-17 12:23:02,459 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue264) h/IKzLdeJsr/.0.8.33 Remove State

                          2010-11-17 12:23:02,459 DEBUG [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue264) h/IKzLdeJsr/.0.8.33 Closed connection

                           

                           

                          So it looks like the IN criteria is not correctly parsed by the salesforce adaptor.

                           

                          A

                          select a from oli where oppId IN ('1', '2', ....)

                           

                          results in a

                          SELECT a from oli where

                           

                           

                          which does of course not work.  Should I file a jira?

                           

                           

                          thanks again + best,

                            Wanja

                          • 10. Re: Salesforce integration: Performance problems with multiple JOINs
                            jdoyle

                            Wanja,

                             

                            I think I see a defect in the way that IN critera are handled.  Can you check to see if the Name and Name in Source for the OpportunityLineItem.OpportunityId are different from each other?

                             

                            ~john

                            • 11. Re: Salesforce integration: Performance problems with multiple JOINs
                              shawkins

                              Wanja,

                               

                              The exception is definitely a bug, I'll log a JIRA.  The salesforce handling of IN predicates makes some assumptions about the string form of the expression, which is causing the problem.  To workaround, the name in source of the table must match the design time name.

                               

                              As for the relationship query, given that we are producing the correct source query I'll let John weigh in how those values are being retrieved.  If you know that they are wrong, then go ahead and log a JIRA for that.

                               

                              Steve

                              • 12. Re: Salesforce integration: Performance problems with multiple JOINs
                                wpernath

                                John,

                                 

                                no, "Name" and "Name in Source"  are not different from each other. Must they?

                                 

                                BTW, I generated the sfdc data model with Teiid 7.1.

                                 

                                Cheers,

                                  Wanja

                                • 13. Re: Salesforce integration: Performance problems with multiple JOINs
                                  wpernath

                                  Steve,

                                   

                                  Steven Hawkins schrieb:

                                   

                                  The exception is definitely a bug, I'll log a JIRA.  The salesforce handling of IN predicates makes some assumptions about the string form of the expression, which is causing the problem.  To workaround, the name in source of the table must match the design time name.

                                   

                                   

                                  Sorry, I don't understand your workaround description.

                                   

                                  What do you mean by "the name in source of the table must match the design time name"?

                                   

                                  Regardless how I would execute the following statement, I always get an Exception:

                                   

                                  select id, Year1Amount__c
                                  from sfdc.salesforce.OpportunityLineItem
                                  where sfdc.salesforce.OpportunityLineItem.OpportunityId IN ('1', '2')

                                   

                                   

                                  select id, Year1Amount__c
                                  from sfdc.salesforce.OpportunityLineItem
                                  where OpportunityId IN ('1', '2')

                                   

                                  The design time name of the Table was generated by teiid during import. This name is "sfdc.salesforce.OpportunityLineItem". And the name of the column is "OpportunityId" with a capital "O" and a capital "I".

                                   

                                   

                                   

                                   

                                  Steven Hawkins schrieb:

                                   

                                  As for the relationship query, given that we are producing the correct source query I'll let John weigh in how those values are being retrieved.  If you know that they are wrong, then go ahead and log a JIRA for that.

                                   

                                  It definitely seems to be a bug as the source query gets correctly transformed. But the result of salesforce is never transformed

                                   

                                  GIVEN:

                                  select a.name, b.sumary

                                  from Opp a OUTER JOIN OLI b ON a.id = b.foreignId

                                   

                                  JoinQueryVisitor of SFDC translator correctly creates:

                                  select a.name, (select b.summary from OLI b) from Opp a

                                   

                                  As of SFDC docs:

                                  http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_relationships.htm

                                   

                                  you get a result set of for example:

                                   

                                  "opp #1", [b] QueryResult for row #1

                                  "opp #2", [b] QueryResult for row #2

                                   

                                  Which must be transformed into something like this (EXPECTED)

                                  "opp #1", 100

                                  "opp #1", 150

                                  "opp #1", 43000

                                  "opp #2", 10000

                                  "opp #2", 100

                                  etc.

                                   

                                  Right now, it gets transformed into something like (CURRENT)

                                  "opp #1", null

                                  "opp #2", null

                                  "opp #3", null

                                  etc.

                                   

                                  So I will file a jira for this.

                                   

                                   

                                  Thanks a lot for your help!

                                   

                                   

                                  Cheers,

                                    Wanja

                                  • 14. Re: Salesforce integration: Performance problems with multiple JOINs
                                    wpernath

                                    I've filed the jira: https://jira.jboss.org/browse/TEIID-1362

                                     

                                    Best,

                                      Wanja

                                    1 2 Previous Next