Andreas Grabner About the Author

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

Top 3 Performance Problems in Custom Microsoft CRM Applications

After spending a lot of time focusing on Client-Side Web 2.0 Performance Problems it is time to focus on specific Server-Side Performance Problems. Last week I worked with a client that runs a custom application on Microsoft CRM 4.0 and experienced performance problems in their test environment. Individual web requests to query or update data took several minutes, with some of them eventually timing out. We analyzed some of these long running and failing transactions and in this blog I present my analysis and hope it will be beneficial for other MS CRM Developers.

Quick Overview of MS CRM

Microsoft CRM provides two core SDK interfaces (ICrmService and ICrmMetaDataService) that allows the CRM Developer to query and update data from the CRM Data Store. CRM provides a way to model any type of business object. The MetaData service provides access to the object entity types and allows you to explore the properties and relationships to other entities. The CrmService allows you to query objects from certain entities and manipulate them. For a quick overview check out the Developer Ramp Up Kit.

2 Options to access data

Besides accessing data through the ICrmService interface, CRM provides database views to query data. Check out the paragraph Applications that Connect to Microsoft CRM to learn more about what CRM calls Filtered Views. These views provide read-only access to data entities through ADO.NET. Do not attempt accessing the CRM SQL Tables directly. Why? Because the relational model is really complex as CRM was built to support generic types – which – when I did some research and figured that out made me think of my research on SharePoint which does something similar, leading to huge performance problems when accessing and, particularly, updating data

Problems we have found

I focused on several individual transactions that ran slow. These were transactions that queried data from the CRM Datastore to display on a web page and transactions that did some updates to existing CRM objects. We identified the following 3 major problems by looking at the captured PurePaths:

Problem 1: Too many Database calls executed by CRM Services

I identified several transactions that executed several thousand SQL statements each time they were executed. The following screenshot shows the SQL Statements of a single Page Request that queried entities from the CRM Datastore in order to present these objects to the user:

A single web request to CRM executes several thousand SQL statements totalling up to 25s in SQL execution time

A single web request to CRM executes several thousand SQL statements totaling up to 25s in SQL execution time

dynaTrace also captures the bind values for SQL executions. Looking at the bind variables we see that most of these SQL calls actually use the same bind variables as well:

Identicial SQL queries were executed up to 2280 times for a single page request

Identical SQL queries were executed up to 2280 times for a single page request

Looking at the actual PurePath (which is the transactional trace of the request I am observing) shows me where all these SQL Statements came from:

Client code makes calls to the ICrmService Execute and Fetch methods which execute several SQL statements to retrieve entity data

Client code makes calls to the ICrmService Execute and Fetch methods which execute several SQL statements to retrieve entity data

The PurePath shows us all these Web Service calls. That is because the ICrmService and IMetaDataService implementation uses the ASP.NET Web Service stack even when the call is executed within the same CLR (but more on that in the next problem description). The problem in this single request, however, is not calling the Execute method. The problem is that it calls it 200 times to retrieve the metadata of only a handful of entities – meaning that it retrieves metadata for the same entity type multiple times. This results in the identical SQL calls we saw in the screenshot above. Instead of caching the Meta information that was requested the data is request again when needed. From a developer’s perspective this is just a single line of code to retrieve this data and one would not assume that these calls have to go through a Web Service layer and execute up to 400 SQL statements (the number depends on the Entity type that is retrieved). The duplicated Execute calls do not explain all duplicated SQL queries. I assume that the other duplicated queries come from requesting inherited property values meaning that the same property GUIDs are used in multiple derived entities. Anybody with more insight? It just sounds logical …

How to speed this up? To start, it is important to eliminate all duplicated calls. Even though it seems like a simple call to a local ICrmService or IMetaDataService interface it actually makes a web service call that results in many SQL statements. Therefore: Cache data that has already been retrieved. Second, make use of FilteredViews. These views provide a faster read access to data stored in the CRM Data Store. It eliminates the Web Service call and allows SQL Server to do the work of collecting the information from different tables instead of letting the CRM Service implementation do the work within the App Server.

Problem 2: CRM Service calls always use Web Service Stack

We already saw that ICrmService and IMetaDataService are implemented as ASP.NET Web Services. This implementation has the benefit of using the same code whether you run within IIS or within a Rich Client Application. The drawback when running within IIS is that you have all the overhead of the web service stack who’s main contributor is the serialization/deserialization of the transferred data from and to XML. By looking at the execution time of the Web Service Proxy class of the MetaData and CrmService class it is easy to see what overhead the web service layer brings with it:

284 calls to the Web Service Proxies consume 8.5s of execution time for mainly data serialization

284 calls to the Web Service Proxies consume 8.5s of execution time for mainly data serialization

The methods view shows us that we had a total of 284 calls into the web service proxy classes of MS CRM. The execution time of these proxy methods takes more than 8.5 seconds. That is 8.5 seconds mainly spent with XML serialization and deserialization. Unfortunately MS CRM only offers this mechanism to update data in the CRM Data Store. To query data this overhead can be avoided by using Filtered Views meaning that you go directly to the database.

How to speed things up? Avoid using the service interfaces when possible, e.g: use direct access to the database via the Filtered Views to retrieve data for read-only purposes.

Problem 3: Database Contention

The excessive use of database queries as explained in the first problem also has negative impacts on all other queries. SQL Server gets overwhelmed with too many queries and connection pools get exhausted resulting in timeouts. The way CRM modeled its database also puts additional pressure on. Due to its generic nature the property values of a single object are not stored in a single row resulting in more expensive update calls to the database. When looking at the slowest running SQL statements across multiple requests that were captured during our test run I can see that some of them have a very long execution time. The more load there is on the system the slower these statements become as the database server needs to handle too many calls through the service interfaces:

Top Select statement has an execution time variation of 372s

Top Select statement has an execution time variation of 372s

How to speed things up? This problem seems to mainly be a result of Problem 1 and 2. Solving those will reduce the pressure on the database and will speed up SQL executions. Nevertheless a DBA should look into these statements to identify if there is a configuration issue or option on SQL Server to speed up those calls.

Conclusion

I am by no means an expert in MS CRM therefore I hope that some of the experts out there can comment on my findings. These problems are very similar to the problems I discovered in SharePoint – check out my blog series about performance problems on that platform. The more generic a framework is the less it is optimized for specific use cases. There is a give and take: Productivity (in terms of developing a custom app) vs. Performance.

 

Comments

  1. Its all about making the calls more efficient in the first place, if they are efficient enough the overhead of connecting/closing is less prevelent

  2. Any luck? We’re having the same problems and noticing the same trends – calls being made over and over. If you have anything, it would be much appreciated. Thanks.

    David

  3. hi,
    After facing the same problem told above, we created a custom webservice application which queries the filteredviews using ADO.Net.
    For this app, it ran under the same CRMAppPool under IIS and also did not have a web.config on its own. And had windows authentication true.

    Now the scenario has changed. The CRM app now runs on a separate server 2008 under IIS7. and the DB runs on another server.

    Now our Custom app is unable to connect to the CRM DB! Its says Authentication Failed for user anonymous!
    We have specified the server , the uid and password in the connection string. Now it seems like a HOP issue.

    Is there a way to use the same connection string as that of the CRM app? We checked the registries, and except for the OLEDB thing , all is the same !

    We get access to CRM Server, and our custom app works with any other data except SQLData!

    Will you be in a position to provide some insights ?

    Sanuja

    • Hi Sanuja
      I would post your question in a MS CRM Forum. You should be able to use the same connection string as your CRM DB. SQL Server should be able to tell you which exact connection string that is as you can monitor the incoming connections.
      But – again – I guess you are better off asking your CRM specific questions in a CRM Forum
      Cheers
      Andi

Comments

*


9 + = seventeen