简单实体关系的搜索速度非常慢
我们在我们的机构中使用 CRM 4.0，目前没有升级计划，因为我们在过去一年半的时间里定制和扩展了 CRM 以与我们的流程配合使用。
模型的一小部分是一个简单的层次结构，我们有一组学习室，它们与另一个描述该学习室可用课程的实体具有一对多关系。 另一个实体拥有对任何课程表示兴趣的所有潜在学生和已注册学生的名单。
这一切都很简单并且工作得很好，并且被建模为 3 个自定义实体。
现在，我们有一个管理应用程序，它可以读取房间，然后显示该房间的课程，但仅限于有注册学生的地方。
在 SQL 中，这被简化为：
SELECT DISTINCT r.CourseName, r.OtherInformation
FROM Rooms r
INNER JOIN Students S
ON S.CourseId = r.CourseId
WHERE r.RoomId = @RoomId
这确实非常接近 CRM 生成的最终 SQL。 我们使用 Crm QueryEntity、Filter 和 LinkEntity 来表示相同的结构。
现在的问题是，CRM 将自定义实体规范化为一个基表，其中包含所有共享的标准 CRM 实体数据，然后是一个包含我们自定义的扩展基表。为了提供对此的扁平化访问，它创建了一个合并两个表的视图。 生成的 SQL 使用此视图。
现在基表有索引，但视图没有。
我们遇到的问题是，我们想要做的就是返回满足内部联接的课程，这足以证明存在条目并且 CRM 使其选择不同，因此我们只为 Room 返回一项。 起初这工作得很好，但现在我们有数千个查询，它需要超过 30 秒，当然会导致除 SMS 之外的任何内容超时。
我相信我们可以在 CRM 中的表上创建和更改索引，这不被认为是不受支持的修改；但是视图呢？ 我知道，如果我们改变一个实体，那么它的视图就会被重新创建，这当然会让我们在发生这种情况时重做索引。
有什么方法可以向 CRM4.0 暗示我们想要一个特定的索引吗？
另一个消息来源建议，如果遇到这样的问题，最好将数据更紧密地结合在一起，但这并不是我在尝试将其融入到我们的解决方案中时感到舒服的事情。
我曾考虑过放入一个新实体，其中仅包含 RoomId、CourseId 和 Enrollment Count，但这也显得非常 hacky；毕竟，索引可以解决复制这些数据的需要，并有某种触发器在每次学生操作后更新数据。
最后，虽然我知道我们目前仍停留在 CRM4 上，但这是我们可以期望在 CRM2011 中解决的问题吗？这肯定会为升级这款已有 5 年历史的产品的论点增添更多分量。
We use CRM 4.0 at our institution and have no plans to upgrade presently as we've spend the last year and a half customising and extending the CRM to work with our processes.
A tiny part of model is a simply hierarchy, we have a group of learning rooms that has a one-to-many relationship with another entity that describes the courses available for that learning room.
Another entity has a list of all potential and enrolled students who have expressed an interest in whichever course.
That bit's all straightforward and works pretty well and is modelled into 3 custom entities.
Now, we've got an Admin application that reads the rooms and then wants to show the courses for that room, but only where there are enrolled students.
In SQL this is simplified to:
SELECT DISTINCT r.CourseName, r.OtherInformation
FROM Rooms r
INNER JOIN Students S
ON S.CourseId = r.CourseId
WHERE r.RoomId = @RoomId
And this indeed is very close to the eventual SQL that CRM generates.
We use a Crm QueryEntity, a Filter and a LinkEntity to represent this same structure.
The problem now is that the CRM normalizes the a customize entity into a Base Table which has the standard CRM entity data that all share, and then an ExtensionBase Table which has our customisations. To Give a flattened access to this, it creates a view that merges both tables.
This view is what is used by the Generated SQL.
Now the base tables have indices but the view doesn't.
The problem we have is that all we want to do is return Courses where the inner join is satisfied, it's enough to prove there are entries and CRM makes it SELECT DISTINCT, so we only get one item back for Room.
At first this worked perfectly well, but now we have thousands of queries, it takes well over 30 seconds and of course causes a timeout in anything but SMS.
I'm given to believe that we can create and alter indices on tables in CRM and that's not considered to be an unsupported modification; but what about Views ?
I know that if we alter an entity then its views are recreated, which would of course make us redo our indices when this happens.
Is there any way to hint to CRM4.0 that we want a specific index in place ?
Another source recommends that where you get problems like this, then it's best to bring data closer together, but this isn't something I'd feel comfortable in trying to engineer into our solution.
I had considered putting a new entity in that only has RoomId, CourseId and Enrolment Count in to it, but that smacks of being incredibly hacky too; After all, an index would resolve the need to duplicate this data and have some kind of trigger that updates the data after every student operation.
Lastly, whilst I know we're stuck on CRM4 at the moment, is this the kind of thing that we could expect to have resolved in CRM2011 ? It would certainly add more weight to the upgrading this 5 year old product argument.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
由于视图是“动态的”（从概念上讲，它们的内容是每次使用时从基表动态生成的），因此通常无法对它们建立索引。然而，SQL Server 确实支持所谓的“索引视图”。您需要在视图上创建唯一的聚集索引，并且查询分析器应该能够使用它来加速您的联接。
Since views are "dynamic" (conceptually, their contents are generated on-the-fly from the base tables every time they are used), they typically can't be indexed. However, SQL Server does support something called an "indexed view". You need to create a unique clustered index on the view, and the query analyzer should be able to use it to speed up your join.
有人在这里问了类似的问题 我没有看到结论性的答案。 Microsoft 提到的问题是引用完整性（此处不是问题）和升级复杂性。您提到了不支持的选项，即添加视图并通过升级和实体更改对其进行管理。这是一个选项，尽管它不受支持且很黑客，但它应该可行。
FetchXml 确实有聚合，但查询执行计划仍然使用视图：这是从事件中的简单选择计数生成的 SQL：
我没有看到针对您的问题的受支持的解决方案。
如果您正在构建外部管理应用程序并且在本地托管 CRM 4，您可以绕过 CRM API 直接访问数据库进行查询。不支持，但可以让您解决问题。
Someone asked a similar question here and I see no conclusive answer. The cited concerns from Microsoft are Referential Integrity (a non-issue here) and Upgrade complications. You mention the unsupported option of adding the view and managing it over upgrades and entity changes. That is an option, as unsupported and hackish as it is, it should work.
FetchXml does have aggregation but the query execution plans still uses the views: here is the SQL generated from a simple select count from incident:
I dont see a supported solution for your problem.
If you are building an outside admin app and you are hosting CRM 4 on-premise you could go directly to the database for your query bypassing the CRM API. Not supported but would allow you to solve the problem.
我将把它添加为一个潜在的答案，尽管我不相信它是一个可持续的或确实有效的长期解决方案。
在分析了 CRM 自动定义的索引后，我意识到在查询中选择更多信息就足以满足索引的列要求，现在查询运行时间不到一秒。
I'm going to add this as a potential answer although I don't believe its a sustainable or indeed valid long-term solution.
After analysing the indexes that CRM had defined automatically, I realised that selecting more information in my query would be enough to fulfil the column requirements of an Index and now the query runs in less then a second.