Performance issue loading data from CRM

Question

Currently our website is facing a problem with slow response times (more than 1 min) when we query CRM from our website. We are using CRM 2011 though a web service. When we investigated we found that the time was spent at the point of querying CRM.

We have used the CrmSvcUtil.exe to generate our proxy classes that map to CRM entities. Then we create an instance of context and query CRM using LINQ with C#.

When we query, We load our parent object with LINQ to CRM and then we use LoadProperty to load the related children.

I would like to know if anyone out there using a different method of querying CRM, and if you have come across issues like this in your implementation.

I’ve included a simplified sample query below.

    public void SelectEventById(Guid id)
     {
            var crmEventDelivery = this.ServiceContext.EventDeliverySet.FirstOrDefault(eventDelivery => eventDelivery.Id == id);
            if (crmEventDelivery != null)
            {
              this.SelectCrmEventDeliveryWithRelationships(crmEventDelivery);
            }
    }

    private void SelectCrmEventDeliveryWithRelationships(EventDelivery crmEventDelivery)
     {
            // Loading List of Venue Delivery on parent crmEventDelivery thats been passed
            this.ServiceContext.LoadProperty(crmEventDelivery, Attributes.EventDelivery.eventdelivery_venuedelivery);

            foreach (var venueDelivery in crmEventDelivery.eventdelivery_venuedelivery)
            {
                 // Loading Venue on each Venue Delivery
                 ServiceContext.LoadProperty(venueDelivery, Attributes.VenueDelivery.venue_venuedelivery);
            }

            // Loading List of Session Delivery on parent crmEventDelivery thats been passed
            this.ServiceContext.LoadProperty(crmEventDelivery, Attributes.EventDelivery.eventdelivery_sessiondelivery);

            foreach (var sessionDelivery in crmEventDelivery.eventdelivery_sessiondelivery)
            {
              // Loading Presenters on each Session Delivery
              ServiceContext.LoadProperty(sessionDelivery, Attributes.SessionDelivery.sessiondelivery_presenterbooking);
            }
   }

Answer 1

Like mentioned on the other answers your main problem is the number of web service calls. What no one mentioned is that you can retrieve many objects with a single call using query joins. So you could try something like:

var query_join = (from e in ServiceContext.EventDeliverySet
                         join v in ServiceContext.VenueDeliverySet on e.EventDeliveryId equals v.EvendDeliveryId.Id
                         join vn in ServiceContext.VenueSet on v.VenueDeliveryId equals vn.VenueDeliveryId.Id
                         join s in ServiceContext.SessionDeliverSet on e.EventDeliveryId equals s.EvendDeliveryId.Id
                         where e.EventDeliveryId == id // *improtant (see below)
                         select new { EventDelivery = e, VenueDelivery = v, Venue = vn, SessionDeliver = s }).ToList();

Then you can run a foreach on query_join and put it together.

***improtant: do not use the base Id property (e.Id), stick with e.EntityNameId.Value (don't know why but it took a while for me to figure it out. Id returns default Guid value "00000..").

Answer 2

Based on what you've provided this looks like a standard lazy-load issue, except my guess is that each lazy load is resulting in a web service call. This would be called a "chatty" service architecture. Your goal should be to make as few service calls as possible to retrieve data for a single request.

Calling to fill in details can seem like a good idea because you can re-use the individual service methods for cases where you only want data 1 or 2 levels deep, or all the way down, but you pay a steep performance penalty.

You would be better off defining a web service call that returns a complete object graph in scenarios like this. I don't know if/what you're using for an ORM layer within the CRM but if you make a specific call to fetch a complete graph of Deliveries then the ORM can eager-fetch the data into fewer SQL statements. Fewer calls to the web service (and subsequently fewer calls into the CRM's data store) should noticeably improve your performance.