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

    Salesforce integration: Performance problems with multiple JOINs

    Wanja Pernath Newbie

      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
          Wanja Pernath Newbie

          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
            John Doyle Apprentice

            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

            • 4. Re: Salesforce integration: Performance problems with multiple JOINs
              John Doyle Apprentice

              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
                Wanja Pernath Newbie

                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
                  Wanja Pernath Newbie

                  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
                    Steven Hawkins Master

                    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
                      Wanja Pernath Newbie

                      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
                        Wanja Pernath Newbie

                        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
                          John Doyle Apprentice

                          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
                            Steven Hawkins Master

                            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
                              Wanja Pernath Newbie

                              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
                                Wanja Pernath Newbie

                                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

                                1 2 Previous Next