Performance of Custom Queries

I got this tip from a developer at a client of mine a while back and I've been meaning to share it. They had created some custom aspx pages that lived inside the CRM sitemap that helped users search across CRM and other data. The CRM queries were using FetchXML just like a regular advanced find view, but they were performing really slowly. Sometimes they were taking as long as 16 seconds to return results. No way were the users going to live with that.

After some digging around, a DBA finally was consulted (maybe we should have asked him for his opinion earlier!). It didn't take him long to give us a helpful tip that led to fixing the query and getting it to return results in under 1 second.

The tip?
It turns out that SQL will ignore indexes when a query is run against a table when the query has a LIKE clause that uses "CONTAINS" (basically where there's a % sign before and after the value), as in

SELECT * FROM contacts WHERE lastname LIKE '%Smith%'

The remedy? Form your queries so they use the equivalent of a "STARTS WITH" so SQL will use the index:

SELECT * FROM contacts WHERE lastname LIKE 'Smith%'

Microsoft Demos Dynamics CRM 2011

Microsoft unveiled the next version of Dynamics CRM at the Worldwide Partner Conference this week. They also announced the name of what has been referred to as version 5: Dynamics CRM 2011.

Some highlights of what they revealed:

• A public beta will be available in September 2010, starting with CRM Online
• CRM Online will continue to roll out internationally with the RTM release, becoming available in 40 countries
• A new marketplace for pre-built solutions will be launched in September

Additionally, they demonstrated some of the features of CRM 2011 that have been the subject of much buzz:

• Role-based forms and views
• Inline visualizations
• User-created dashboards
• Guided dialogs (think of automated, real-time workflows with user input and feedback)
• Tight SharePoint integration supported natively

More Weirdness with the Sales Pipeline Report

So here's a new one. It's been known amongst us CRM geeks for some time that the built-in Sales Pipeline report has some, let's say, quirks. For example, if you want to group the report by your Sales Process using the parameter drop-down, you'll first have to have a Workflow applied against the Opportunities, and the workflow must contain "Stages" which have the names of each step of your sales process. This is where the Pipeline Report gets its groupings.

If you run a workflow against the Opportunities, and update the Opportunity salesstagecode or stepname fields, this out-of-the-box report will not show you your pipeline with these groupings.

Well, today I discovered something even stranger. I was testing a sales process for a client, and set it to be a manual workflow so I could run it on demand during testing. I ended up running it three times agains 50 opportunities. Guess what? The Sales Pipeline report showed that there were 150 opportunities in my pipeline! Each opportunity was counted once every time a workflow with stages had been run against it.

So I realized that since it's pulling its groupings from the completed workflows, I could just delete the workflows from the histories, and that would take care of it right? Well, only sorta. I also had to use the scale group job editor to kick off the deletion service, since the report doesn't filter out completed workflows that have been marked for deletion.

One of these days I'm just going to have to rebuild that Sales Pipeline report from scratch!

UPDATE: A support engineer at Microsoft informed me that a hotfix should be released soon that fixes this behavior in the Sales Pipeline report. Thanks, John!

You say you're in Dublin and want to learn more about Dynamics CRM?

Well, isn't that a coincidence! I'll be speaking at epicenter 2010 next week, Ireland's largest software show and developer conference.

More about my session is at this link. Oh, and my good friend and fellow CRM MVP, Ronald Lemmen will be speaking as well.
It's not too late to get tickets!

New Article on MS CRM Team Blog: Interactive Funnel Dashboard

I have a new article on the CRM Team blog on MSDN. It details how to make an interactive sales funnel dashboard, as demonstrated in this YouTube video.