EF6. We have a request:

var changedDocs1 = (from d in nsql.Doc //join c in nsql.Cnt on d.Receive equals c.NOwner join ad in nsql.AlterationDoc.Where(x => x.AlterTime > localVersionDate) on d.NDoc equals ad.NDoc join cep in nsql.ChangeEndPeriod.Where(x => x.prev_Employee == eqEmployee && x.DateModific > localVersionDate) on new { d.NDoc, d.Employee } equals new { cep.NDoc, Employee = cep.curr_Employee ?? -1 } into CEP from cep in CEP.DefaultIfEmpty(null) let deleteFlag = (!d.IsShipped || (cep != null && cep.curr_Employee != cep.prev_Employee)) ? true : false where d.AccountDate == Date && (d.Employee == eqEmployee || cep != null) select new { d.NDoc, Doc = d.Doc1 ?? -1, NOwner = d.Receive, d.Terms, deleteFlag, changeDate = ad.AlterTime >= (cep == null ? ad.AlterTime : cep.DateModific) ? ad.AlterTime : (cep.DateModific ?? ad.AlterTime), //c.Owner, //c.DocAddress Owner = "", DocAddress = "" }).ToList();

We look at the generated query by logs:

 SELECT [Extent1].[NDoc] AS [NDoc], CASE WHEN ([Extent1].[Doc] IS NULL) THEN -1 ELSE [Extent1].[Doc] END AS [C1], [Extent1].[Receive] AS [Receive], [Extent1].[Terms] AS [Terms], CASE WHEN (([Extent1].[IsShipped] <> 1) OR (([Extent3].[Id] IS NOT NULL) AND ( NOT (([Extent3].[curr_Employee] = [Extent3].[prev_Employee]) AND ((CASE WHEN ([Extent3].[curr_Employee] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent3].[prev_Employee] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))))) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C2], CASE WHEN ([Extent2].[AlterTime] >= (CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST( [Extent2].[AlterTime] AS datetime2) ELSE [Extent3].[DateModific] END)) THEN [Extent2].[AlterTime] WHEN ([Extent3].[DateModific] IS NULL) THEN [Extent2].[AlterTime] ELSE [Extent3].[DateModific] END AS [C3], N'' AS [C4], N'' AS [C5] FROM [dbo].[Doc] AS [Extent1] WITH (NOLOCK) INNER JOIN [dbo].[AlterationDoc] AS [Extent2] WITH (NOLOCK) ON [Extent1].[NDoc] = [Extent2].[NDoc] LEFT OUTER JOIN [dbo].[ChangeEndPeriod] AS [Extent3] WITH (NOLOCK) ON ([Extent3].[prev_Employee] = @p__linq__1) AND ([Extent3].[DateModific] > @p__linq__2) AND ([Extent1].[NDoc] = [Extent3].[NDoc]) AND ([Extent1].[Employee] = (CASE WHEN ([Extent3].[curr_Employee] IS NULL) THEN -1 ELSE [Extent3].[curr_Employee] END)) WHERE ([Extent2].[AlterTime] > @p__linq__0) AND ([Extent1].[AccountDate] = @p__linq__3) AND (([Extent1].[Employee] = @p__linq__4) OR ([Extent3].[Id] IS NOT NULL))

The execution time of this query without any changes via the SqlAdapter is 0.2 seconds.
Execution time via EntityFramework - 120 seconds.
The execution results are the same.

What can cause such a difference?

UPD. The problem is localized.
At some point, SQL Server cached an extremely stupid query plan. The query through EF used it, the query through ADO - no (due to the casting of parameter types). Adding OPTION (RECOMPILE) solved the problem.

  • You still need to get the parameters. You not only work with sql fields, but also create anonymous classes. As a result, it turns into not quite obvious requests plus work already in memory, most likely. - Monk
  • parameters - primitives, two int, 3 datetime. They are defined earlier. The log shows that they are passed to the request. - Zufir
  • This "new {d.NDoc, d.Employee} equals new {cep.NDoc, Employee = cep.curr_Employee ?? -1}" is translated into sql? - Monk
  • one
    Indeed, the request was normal. I would useful to profile. Suddenly sags on something else, and the request is real for 0.2 seconds and executed. - Monk
  • one
    Do you measure time in a heated context or with the initialization of EF? EF has a very long initialization of the first context. - Pavel Mayorov

0