There has to be a better way

IT News & Views

Subscribe to IT News & Views: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get IT News & Views: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

IT News Authors: Joe Austin, Satyen Vyas, Elena Yakymchuk, Jim Malone, OnPage Blog

Related Topics: RIA Developer's Journal, CRM, Microsoft Dynamics


Top Three Performance Problems in Custom Microsoft CRM Applications

Microsoft CRM provides two core SDK interfaces

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.

Two 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 three 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.

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.

Related reading:

  1. Top .NET Performance Problems and how to avoid them Every time I work with one of our .NET customers...
  2. Linq2Sql: Prevent performance issues when operating on multiple rows with Stored Procedures Many of you bloggers out there have already covered the...
  3. SharePoint: Identifying memory problems introduced by custom code SharePoint is a great platform that makes it easy to...
  4. How to Find Invisible Performance Problems In the first post for this year, I will discuss...
  5. VS2010 Load Testing for Distributed and Heterogeneous Applications powered by dynaTrace Visual Studio 2010 is almost here – Microsoft just released...

More Stories By Andreas Grabner

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