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>";