Friday, September 27, 2013

So... Just How Performant Can the CRM 2011 SDK Be?

David Evans replied to a different blog post of mine and pointed out that the product team has done a very large amount of work to make things work quicker through the CRM SDK and shared the video below.   I have heard David Cai tout how he has seen well over 200 rows per second in SSIS though his product.  I thought this was AMAZING considering I was used to Scribe and if I got 15 records per second I was pretty dang happy.  I could run more than one thread at once though if I could chunk up the job properly.

In the video below I was blown away.  Literally you will see using the maximum threads per second against CRM Online which hasn't always been known for being the most performant depending on where in the country you are accessing it from. You will see here shortly after 3:10 in the video where in a matter of seconds the process they run revs up to OVER 650 ROWS PER SECOND!  They are running 16 threads, but now I have a new appreciation for just what the SDK is capable of handling if you hit it just the right way.

Check out the video here and just skip to 3:10-ish to see them start running first with one thread and then with 16.  WOW!!!

- I hope you enjoy this video!


Tuesday, September 24, 2013

Dealing With the 8k SQL Server Row Limits in Microsoft Dynamics CRM

Once or twice I have run into a CRM implementation in the course of my work that runs afoul of SQL Server's 8k row limit.  SQL Server has always had a limitation to how big a row in a table can be and SQL Server DB admins and database designers have long had to deal with.  For many applications you have options by using specific datatypes and other constructs to be able to work around these limits, but for CRM you do not have any control over the schema directly, and any kind of manual change would definitely be unsupported.

You can tell if you run afoul with this issue, it will throw a MSSQLSERVER ERROR 576.

There are things you can consider when though when designing your CRM entity structure that will help you get around these issues.   You can also apply many of these same principals in re-design after the fact to help get the problem back under control.

Here they are:

1. Look for entities that could be broken out into 1-M relationships

Maybe you don't need all of those fields in the same entity.  Look for entities that can be broken out into their own entities within the system.  This will alleviate the number of rows and over all  length of the table.

2. Shorten your columns

Don't use a bunch of space if you don't need to for fields.  Give your self enough room, but not so much room that you will never possibly use it all.

3. Look to see if there are fields you don't need

If there are fields in your source system that you know you never use, don't migrate them to the new system.  also if you find that there are unneeded columns in your Dynamics CRM system already, delete them.

4.  Look to see if there is any other logical way to break up your entity into multiple entities.

Maybe you don't have clear cut 1-M relationships all in the same table but maybe you are modeling something that is really more of a 1-1 relationship in your entity design.  Maybe you combined these at first for simplicity's sake, but breaking these out could also help if you are running up against this limitation.


Again, this is not a really terribly common issue in CRM implementations, or it hasn't been in my experiencem but if you run into this you will know that there are ways to work around this.

I also know this list probably isn't exhaustive, so feel free to add more things in the comments.


Friday, September 6, 2013

Solid Reasons Not To Do Direct Database (T-SQL) Operations on Dynamics CRM Database From the Pros

I got an email this morning from an old college classmate of mine in kind of a exasperated tone with a one word subject "HELP!".  It turns out that she has fellow IT folks that want to replace some updates she is doing regularly using workflow that have worked fine for a long time with nightly T-SQL batch jobs pushing the data from their data warehouse back into CRM, even though it was already in CRM to begin with.

I decided to delve into this a bit to see what are the real reasons for why this shouldn't happen.  I knew some but I also asked some of my other MVP buddies to get their feedback.  Here is what I came up with.   Feel free to use these arguments if you need to explain to your IT department why manual database operations are a bad idea.

Jamie Miley 
  • If you are using Auditing in CRM, it won't reflect any changes done by manual database update.
  • Modified, by, modified on, etc... will also not be adjusted properly
  • PrincipleObjectAccess table will not be updated based on manual updates and so permissions will not be properly set on any inserted, updated, deleted records.
Scott Sewell
  •  Database updates to CRM, if you did updates to something that affects security (owner, business unit, etc...) no updates would occur to PrincipleObjectAccess table
  • Updating Name fields will cause issues because lookups tend to cache names

Damian Sinay:
  • Plugins and/or workflows won't fire
  • Also caching is an issue, any cached data won't be invalidated by a database operation.
Gustaf Westerlund
  • If you are not 110% sure what youa re doing you are risking the stability of the entire system.
Julie Yack
  • It’s bad karma to do unsupported stuffs when a supported way will do it.
Carston Groth
  • Relations might get lost if you´re only performing the action on one datebase table ignoring all related tables
Joel Lindstrom

  • Biggest reason is that it will appear to work initially but problems will crop up later and you won’t be able to connect the dots to the real issue because the issue won’t be caught by the normal error reporting mechanisms.

Customer manually loaded contact records and later couldn’t reassign them. Turned out to be because in their manual load they didn’t populate businessunitid. The contacts worked, but couldn’t be reassigned later because that field wasn’t populated, but the error message generated didn’t explain what the problem was, because records created in a supported way always have that field populated

Different customer manually overwrote the createdby and modifiedby using unsupported T-SQL. Records initially appeared to work OK; however, when users attempted to forward the message in CRM, they got an error. Again, since this was a delayed error situation that showed up months later, it was very difficult to find the real reason for the error—all diagnostics did not show the real problem.

So to me, that is the biggest reason not to create records in an unsupported way. It is very difficult to verify that it is correct because standard system data validation does not fire, and if you miss anything, the real problem may not show up for months and will most likely be outside of the normal error reporting mechanism. You are on your own. Was it worth it?

In Conclusion

In the end, I think these are all great ideas regarding the issue.  I really just want to echo Gustaf above.  The fact is that it is considered unsupported for a reason.  Microsoft doesn't want to deal with it either.  All unsupported customizations are adding serious risk to your entire implementation as Microsoft would be entirely within their right to wipe their hands of the entire implementation when they can show that these types of things are being used.  The API and SDK are there for a reason, please use them. There are ways to do almost anything you could be trying to do that would cause you to go an unsupported route.  This is where a good partner can really steer you in the right direction.  You have already invested a lot of money on your Dynamics CRM implementation in software costs and in most cases consulting time to put it into place and get it customized properly, protect that investment!

- I hope this helps!