为 Entity Framework 4 选择 SQL 连接类型

发布于 2024-10-08 01:26:10 字数 1223 浏览 5 评论 0原文

例如，我有一个查询：

var personList = context.People;

People 是一个视图，它有 2 个联接，大约有 2500 行，执行时间大约为 10 秒。查看估计执行计划告诉我它正在使用嵌套循环。

现在，如果我这样做：

var personList = context.People.Where(r => r.Surname.Length > -1);

执行时间不到一秒，并且执行计划正在使用哈希连接。

在生成的 SQL 中添加“OPTION (HASH JOIN)”可以达到提高性能的预期效果。

所以我的问题是......

我怎样才能让查询使用哈希连接？它无法添加到视图中（我尝试过，它出错了）。 EF4 中是否有一个选项可以强制执行此操作？或者我必须将其放入存储过程中？

RE：查看

SELECT     dbo.DecisionResults.ID, dbo.DecisionResults.UserID, dbo.DecisionResults.HasAgreed, dbo.DecisionResults.Comment, 
                      dbo.DecisionResults.DateResponded, Person_1.Forename, Person_1.Surname, Site_1.Name, ISNULL(dbo.DecisionResults.StaffID, - 999) 
                      AS StaffID
FROM         dbo.DecisionResults INNER JOIN
                      Server2.DB2.dbo.Person AS Person_1 ON Person_1.StaffID = dbo.DecisionResults.StaffID INNER JOIN
                      Server2.DB2.dbo.Site AS Site_1 ON Person_1.SiteID = Site_1.SiteID
ORDER BY Person_1.Surname

如果我将 OPTION(HASH JOIN) 添加到末尾，则会出现错误：

“查询提示”不能用于此查询类型。

但是将该脚本作为查询运行效果很好。

原文

I have a query for example:

var personList = context.People;

People is a view that has 2 joins on it and about 2500 rows and takes ~10 seconds to execute.
Looking at the Estimated Execution plan tells me that it is using a nested loop.

Now if i do this:

var personList = context.People.Where(r => r.Surname.Length > -1);

Execution time is under a second and the execution plan is using a Hash Join.

Adding "OPTION (HASH JOIN)" to the generated SQL has the desired effect of increasing performance.

So my question is ...

How can i get the query to use a Hash Join? It can't be added to the view (I tried, it errors).
Is there an option in EF4 that will force this? Or will i have to put it in a stored procedure?

RE: View

SELECT     dbo.DecisionResults.ID, dbo.DecisionResults.UserID, dbo.DecisionResults.HasAgreed, dbo.DecisionResults.Comment, 
                      dbo.DecisionResults.DateResponded, Person_1.Forename, Person_1.Surname, Site_1.Name, ISNULL(dbo.DecisionResults.StaffID, - 999) 
                      AS StaffID
FROM         dbo.DecisionResults INNER JOIN
                      Server2.DB2.dbo.Person AS Person_1 ON Person_1.StaffID = dbo.DecisionResults.StaffID INNER JOIN
                      Server2.DB2.dbo.Site AS Site_1 ON Person_1.SiteID = Site_1.SiteID
ORDER BY Person_1.Surname

If i add OPTION(HASH JOIN) to the end it will error with :