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