Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am running a fetchXML statement with CRM 2011. It joins Opportunity against several custom entities. One of these custom entities has Annotations under it. When I try to outer join against these Annotations I get very slow performance, around 25-30 seconds for a query to execute. After I remove the outer join the query executes in under a second, I just don't get the notes. Could this have something to do with querying based on a date field?

I have removed the fields being returned from each entity for brevity, the Annotations table brings back three columns, the rest 3-5 columns.

            String logFetchXML = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
          <entity name='" + Opportunity.EntityLogicalName + @"'>
            <order attribute='sa_dealership' descending='false' />
            <order attribute='customerid' descending='false' />
            <filter type='and'>
              <condition attribute='" + sa_dealership.EntityLogicalName + @"' operator='eq' uitype='" + sa_dealership.EntityLogicalName + @"' value='{" + DealerID.ToString() + @"}' />
            </filter>
            <link-entity name='" + sa_showroomvisit.EntityLogicalName + @"' from='regardingobjectid' to='opportunityid' alias='showroomVisit'>
                <link-entity name='" + Annotation.EntityLogicalName + @"' 
                 from='objectid' to='activityid' alias='showroomVisitNote' link-type='outer'>
                <attribute name='annotationid' />
                <attribute name='createdon' />
                <attribute name='notetext' />
              </link-entity>
              <filter type='and'>
                <condition attribute='actualstart' operator='on-or-after' value='" + FromDate.ToShortDateString() + @"' />
                <condition attribute='actualstart' operator='on-or-before' value='" + ToDate.ToShortDateString() + @"' />
              </filter>
            </link-entity>
            <link-entity name='" + Contact.EntityLogicalName + @"' from='sa_activeopportunityid' to='opportunityid' alias='dealerContact'></link-entity>
            <link-entity name='" + sa_interestedvehicles.EntityLogicalName + @"' from='sa_opportunityid' to='opportunityid' alias='interestedVehicle' link-type='outer'></link-entity>
            <link-entity name='" + sa_tradevehicles.EntityLogicalName + @"' from='sa_opportunityid' to='opportunityid' alias='tradeVehicle' link-type='outer'></link-entity>
            <link-entity name='" + Annotation.EntityLogicalName + @"' from='objectid' to='opportunityid' alias='opportunityNote' link-type='outer'></link-entity>
        </fetch>";
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.