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:
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:
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:
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:
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:
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.
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.