Performance data when loading data from CRM

Question

There is currently a problem with our slow response time (more than 1 minute) when we request CRM from our website. We use CRM 2011, although a web service. When we researched, we found that the time was spent at the time of the CRM request.

We used CrmSvcUtil.exe to generate our proxy classes that map to CRM objects. Then we instantiate the context and the CRM request using LINQ with C #.

When we request, we load the parent from LINQ into CRM, and then we use LoadProperty to load the related children.

I would like to find out if anyone is there using another CRM request method, and if you encounter such problems in your implementation.

Ive included a simplified sample example 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

Based on what you provided, this seems like a standard lazy loading problem, except that I assume that each lazy loading leads to a web service call. This will be called chatta service architecture. Your goal should be to make as few service calls as possible to retrieve data for a single request.

Calling to fill out details may seem like a good idea, because you can reuse individual service methods for cases where you only need data at level 1 or level 2 or all the way down, but you pay a hefty penalty for performance.

You would be better off defining a web service call that returns a complete graph of objects in scenarios such as this. I don’t know if / what you use for the ORM layer in CRM, but if you make a specific call to get a complete delivery schedule, ORM will be able to query data in fewer SQL statements. Fewer web service calls (and therefore fewer calls to the CRM data store) should noticeably improve performance.

Answer 2

As mentioned in other answers, the 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 can 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 foreach on query_join and put it together.

*** Improtant: do not use the basic Id (e.Id) property, stick to e.EntityNameId.Value (I don’t know why, but it took me a while to figure this out. Id returns the default Guid value to "00000 ..") .

Answer 3

So, I understand why this may take some time. I think, as everyone has commented, you are making quite a few calls to web services. If you get the moment, it would be interesting to know if individual calls are slow, or simply because you are doing so much, I would suggest profiling this.

In any case, I suspect that you will get better performance without using strictly type entities.

I would suggest using a FetchXml request, this will allow you to build a Sql Xml-Style request. Basically, you should be able to replace your many bservice calls with one call. For example, MSDN , also check out Stunnware FetchXml Designer, Products> Stunnware Tools> Download and Pricing. It was built for Crm 4, but supports almost all the features you need.

If you don't like this, you can also try QueryExpression or OData , both of which should allow you to receive data in one hit.

Answer 4

After trying all the suggested tips in other answers and doing further profiling, in our specific scenario using CRM and how it was configured, we decided to just work around it .

As a result, we used some built-in views, this is not recommended in the CRM documentation, but we really needed to achieve higher performance, and the CRM approach in this case was only in our way.

To others who read this, also see other answers.

Answer 5

Since the query does not know which fields will be needed later, all columns will be returned from the object if only an entity is specified in the select clause. To specify only the fields that you will use, you must return a new object in the select clause, specifying the fields that you want to use.

So instead:

 var accounts = from acct in xrm.AccountSet where acct.Name.StartsWith("Test") select acct; 

Use this:

 var accounts = from acct in xrm.AccountSet where acct.Name.StartsWith("Test") select new Account() { AccountId = acct.AccountId, Name = acct.Name }; 

Read more here.

In Linq or not in Linq