如何修复执行多条SQL语句的超慢EF/LINQ查询

Question

我有以下代码，这是错误的行为：

TPM_USER user = UserManager.GetUser(context, UserId);
var tasks = (from t in user.TPM_TASK
             where t.STAGEID > 0 && t.STAGEID != 3 && t.TPM_PROJECTVERSION.STAGEID <= 10
             orderby t.DUEDATE, t.PROJECTID
             select t);

第一行，UserManager.GetUser只是在数据库中进行简单的查找，以获得正确的TPM_USER记录。但是，第二行会导致各种SQL混乱。

首先，它在这里执行两个SQL语句。第一个抓取TPM_TASK中与该用户链接的每一行(有时是数万行)：

SELECT 
 -- Columns
 FROM  TPMDBO.TPM_USERTASKS "Extent1"
 INNER JOIN TPMDBO.TPM_TASK "Extent2" ON "Extent1".TASKID = "Extent2".TASKID
 WHERE "Extent1".USERID = :EntityKeyValue1

这个查询对拥有大量任务的用户大约需要18秒。我希望WHERE子句也包含STAGEID过滤器，这将删除大多数行。

接下来，它似乎对上面列表中的每个TPM_PROJECTVERSION对执行一个新的查询：

SELECT 
 -- Columns
 FROM TPMDBO.TPM_PROJECTVERSION "Extent1"
 WHERE ("Extent1".PROJECTID = :EntityKeyValue1) AND ("Extent1".VERSIONID = :EntityKeyValue2)

即使这个查询很快，如果用户在一堆项目中有任务，它也会执行几百次。

我想要生成的查询如下所示：

SELECT 
 -- Columns
 FROM  TPMDBO.TPM_USERTASKS "Extent1"
 INNER JOIN TPMDBO.TPM_TASK "Extent2" ON "Extent1".TASKID = "Extent2".TASKID
 INNER JOIN TPMDBO.TPM_PROJECTVERSION "Extent3" ON "Extent2".PROJECTID = "Extent3".PROJECTID AND "Extent2".VERSIONID = "Extent3".VERSIONID
 WHERE "Extent1".USERID = 5 and "Extent2".STAGEID > 0 and "Extent2".STAGEID <> 3 and "Extent3".STAGEID <= 10

上面的查询将在大约1秒内运行。通常，我可以使用JOIN方法指定Include。然而，这似乎不适用于属性。换句话说，我做不到：

from t in user.TPM_TASK.Include("TPM_PROJECTVERSION")

有任何方法来优化这个LINQ语句吗？我使用.NET4和Oracle作为后端DB。

解决方案：

该解决方案基于下面的Kirk建议，并且工作正常，因为不能直接查询context.TPM_USERTASK：

var tasks = (from t in context.TPM_TASK.Include("TPM_PROJECTVERSION")
             where t.TPM_USER.Any(y => y.USERID == UserId) &&
             t.STAGEID > 0 && t.STAGEID != 3 && t.TPM_PROJECTVERSION.STAGEID <= 10
             orderby t.DUEDATE, t.PROJECTID
             select t);

它确实会导致嵌套的SELECT，而不是直接查询TPM_USERTASK，但它似乎相当有效。

Answer 1

是的，您正在拉下一个特定的用户，然后引用关系TPM_TASK。它正在拉下与用户相关的每一个任务，这正是它应该做的。这样做时，没有ORM转换。您需要一个用户，然后将他的所有任务输入内存，然后执行一些客户端过滤。所有这些都是使用延迟加载完成的，因此SQL将变得异常低效，因为它不能批量处理任何内容。

相反，重写查询以直接针对TPM_TASK并对用户进行筛选：

var tasks = (from t in context.TPM_TASK
         where t.USERID == user.UserId && t.STAGEID > 0 && t.STAGEID != 3 && t.TPM_PROJECTVERSION.STAGEID <= 10
         orderby t.DUEDATE, t.PROJECTID
         select t);

注意我们是如何检查t.USERID == user.UserId的。这会产生与user.TPM_TASK相同的效果，但现在所有的繁重工作都是由数据库而不是内存完成的。