Geek asks and answers

How to fix super slow EF / LINQ query executing multiple SQL statements

I have the following code that works poorly:

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);

The first line UserManager.GetUsersimply does a simple database search to get the correct record TPM_USER. However, the second line causes all kinds of SQL chaos.

First of all, it executes two SQL statements here. The first captures every line in TPM_TASKthat is associated with that user, and sometimes tens of thousands of lines:

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

This request takes about 18 seconds for users with many tasks. I would suggest that the WHERE clause also contains STAGEID filters that would delete most rows.

Then, it seems, a new query is executed for each pair TPM_PROJECTVERSIONin the above list:

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

:

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

+5
1

, , TPM_TASK. , , , - , . ORM SQL, . , , . , 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, , .

+4

More articles:


All Articles