+972 52-548-6969
The CRM Horror
It is rare that I get to the foint where I am just flat out speechless from seeing something. Today I went beyond that, I was flat out speechless and aghast.
The image that you see here is a small part of the FilteredAccount view in Micorosft CRM. Yes, you got that right, a small part.
I got to this from experimenting with the DB model, trying to figure out how things work. I was strongly adviced not to make any use of any sort of view that started with Filtered. "It would make you cry", they said, "don't say we didn't warn you".
I took a peek inside, to see what all the fuss was about. I disagree with Microsoft of quite a few subjects, but they usually manage to hit the target, even if that target is on a completely wrong continent as far as I am concerned.
Some background, before we continue. Filtered Views in MS CRM are used to show the user just the data that they are supposed to see, according to role based permissions. Using the connected usernmae as the way to filter the view.
As a result of that, all the members of the organization has connect access to the CRM DB, and can just open query analyzer and start executing queries against the DB.
An immediate result of that is that you need to start managing security at the DB level, since those views will not be very good if you can just query the underlying tables.
I gues it would not suprise anyone that I have serious issues with this model, but let us put this aside for a moment. I have issues with this model, but they are manageable ones.
Anyway, using the filtered views is the only supported way of handling accessing the database directly, mostly meant for reports. I think that you can guess how efficent it is going to query a filtered view, right?
I counted, the FilteredAccount view has:
- 5 row level functions
- 40 outer joins
- 2 subqueries
At this point, I am considering creating the DETD - Developers for Etichal Treatment of Databases. Surely this is cruel and unusal behavior to put this burden on an unsuspecting database. And I am pretty sure that it is a violation of the Geneva Convention to inflict such a thing on your unsuspecting users.
Left forever wonderring when that query will ever return...
Comments
wow...that is all I can say.
I thought I had a valid gripe about a legacy system at work using 10 outer joins. I guess I have no room to gripe about that now =)
I felt a chill go up my spine when I read "40 outer joins"
Sean,
The scary thing , this is not a legacy system, this is on a default install of MS CRM 3.0, the current version, only a few years old.
This is, I am afraid, By Design.
Sounds like the MS CRM 3.0 team was in desperate need of a DBA with veto authority.
However, this paradigm is status quo right now with MS. They've been releasing revisions to database products for the past 5 years that abstract the architecture from the data model and DB engine, and subverting the traditional DB Engineer, Developer & DBA roles.
It makes the DB professions uncertain about recommending MS products, and upgrades in the end. But hey! Who cares? Just keep the .NET wizard app devs happy, and there will be people who have to use MS products, right?
Makes me wonder how hard Oracle is to learn.
The size of the diagram doesn't surprise me anymore. I've seen worse things. What I'm particularly terrified about are tables (or views?) name "u", "us", and "o".
Something that belongs to Worse Than Failure!
You ain't seen nothing until you have seen Dynamics GP 10. As an example, the view SalesLineItems has
530 columns including
72 call function calls in the SELECT
37 inline SELECTS in the main select statement
9 OUTER JOINS
and a UNION to to it off.
After formatting the query with a beautifier, it is around 1250 lines.
While looking for inspiration to creating a database-level role-based security model, I checked out MS CRM's model.
Needless to say, I immediately swore off the feasibility of this architecture after seeing that craziness.
I did finally understand why that the sales guy's Outlook took around 5 minutes to launch though...
SQLDBA,
ORA isn't hard to learn, actually, but it assumes a far for active role for the DBA.
Allow me to assure you that the ORA CRM isn't much better, although I haven't looked at their SQL.
Um,
Because they want to force you to buy SQL Enterprise and run this query over a server farm of SQL Servers?
It's been a long time since I did any MSSQL stuff, but I vaguely recall that if you do a select from view, and only select a view columns, then only the tables that contain the columns you selected and the columns in your where clause actually get accessed.
So the view definition may contain 40 outer joins, but if you do a "SELECT COUNT(*) FROM FilteredAccount WHERE CreatedByName='Contrived Example'" then you'll end up with a much leaner query plan.
Jonnosan,
This isn't possible, since something like count(*) MUST count all the joins, because they directly affect the line count.
If it is a simple join, and you are scanning columns from a single table, maybe, but that isn't it, certainly.
Hmm, I've done plenty of work on MS CRM, and I'm pretty happy with this design. For one, it means that creating reports is fast and easy, and in particular you don't have to worry about showing users information they weren't supposed to see (this can be crucial in for example the financial sector). Also, some power-users create dynamic spreadsheets on top of the views, this would be impossible if they had to do all sorts of joins.
Overall system performance is good, doing the role/rights-based security is arguably faster in the db than in code. True, if you do lots of heavy-duty reporting, Microsoft recommends having separate SQLServer image for that. Also on SQLServ 2000 you will sometimes bumb into a "max ~250 joins" limit when doing non-trivial queries, but hey -- that's life :-).
The accepted wisdom with CRM is that you just don't mess with the database (what were you doing in there anyway?) -- Microsoft built a nice web interface/web service layer on top for you to use, stick to that (except perhaps for a few judicious indexes).
Michael,
I have seen multi minutes queries for SELECT * FROM FilteredMyEntity where the system had about 10 entities of this type.
Yes, it simplify reports and it is nicer to use from Excel, but not when it get to this level.
Performance is NOT all right, and deciding to do it in the DB is a mistake if it makes it so much slower.
I am not sure where you got the idea that hitting the 250 joins is part of life. That is enough to send me running to the hills.
It is NOT something that should happen.
If you hit the builtin limits, you are so doing things wrong.
What am I doing with the CRM? Trying to build a search interface without going insane. The built in capabilities means that I have to start building the fetch xml manually, and I will be damned if I will do it dynamically.
Yeah, I know hitting the join-limit is getting criminal -- hence the smiley.
If you decide to to build FetchXML, Micheal Höhnes lib might help you out:
http://www.stunnware.com/crm2/topic.aspx?id=findingdata5
I'm using it for my query-provider:
http://www.codeplex.com/LinqtoCRM
I'm late to the party here but had to comment once I saw Ayende's post.
The point of the views is to denormalize the db for easy query building and that denormalization is going to have a performance impact -- if you query isn't tuned.
Jonnosan is correct in stating that the joins are going to occur only when retrieving data necessary for the query. And many of the joins for the filteredAccount view are stringmap joins - retreivals of strings that would otherwise be represented by int values. If those columns aren't requested the joins won't occur. Retrieving all account details from the underlying tables, with the joins required to make sense of the returned data, is going to require a large number of joins -- and a lot of knowledge about the db schema.
So surely, a query like SELECT * from filteredMyEntity is going to be non performant but the views aren't designed to keep report developers from building non performant queries. Queries still need to be tuned for performance.
Regarding a desire to build search against CRM: I'd be curious to hear Ayende's thoughts on the use of the Business Data Catalog (BDC) from Microsoft Office Server for indexing data in the CRM db.
Comment preview