Alef
Reputation: 13
Query generated by LINQ is terrible slow compared by creating your own SQL view
I've created the following query with LINQ:
var today = DateTime.Today;
var month = new DateTime(today.Year, today.Month, 1);
var first = month.AddMonths(-1);
var last = month.AddDays(-1);
var baseQuery = (from jr in DollarU_JobRuns
where (jr.Day >= first && jr.Day <= last)
group jr by new { System = jr.SYSU.Substring(0, 2) }
into systemGroup
let NbrOfJobRunsSucceeded = systemGroup.Sum(x => x.NbrOfJobRunsSucceeded)
let NbrOfJobRunsFailed = systemGroup.Sum(s => s.NbrOfJobRunsFailed)
select new
{
System = systemGroup.Key.System,
NbrOfJobRunsSucceeded,
NbrOfJobRunsFailed,
Total = NbrOfJobRunsSucceeded + NbrOfJobRunsFailed
}).OrderByDescending(item => item.Total);
var query = baseQuery.Take(10).Concat(
baseQuery.Skip(10).Select(item => new
{
System = "Others",
NbrOfJobRunsSucceeded = item.NbrOfJobRunsSucceeded,
NbrOfJobRunsFailed = item.NbrOfJobRunsFailed,
Total = item.NbrOfJobRunsSucceeded + item.NbrOfJobRunsFailed,
}
)
)
.GroupBy(g => g.System)
.Select(
g => new
{
System = g.Key,
NbrOfJobRunsSucceeded = g.Sum(x => x.NbrOfJobRunsSucceeded),
NbrOfJobRunsFailed = g.Sum(x => x.NbrOfJobRunsFailed),
Total = g.Sum(x => x.Total)
}
)
.OrderByDescending(item => item.Total);
query.Dump();
which generates the following SQL query:
SELECT
[Project9].[C5] AS [C1],
[Project9].[C4] AS [C2],
[Project9].[C1] AS [C3],
[Project9].[C2] AS [C4],
[Project9].[C3] AS [C5]
FROM ( SELECT
[GroupBy5].[A1] AS [C1],
[GroupBy5].[A2] AS [C2],
[GroupBy5].[A3] AS [C3],
[GroupBy5].[K1] AS [C4],
1 AS [C5]
FROM ( SELECT
[UnionAll1].[C1] AS [K1],
SUM([UnionAll1].[C2]) AS [A1],
SUM([UnionAll1].[C3]) AS [A2],
SUM([UnionAll1].[C4]) AS [A3]
FROM (SELECT TOP (10)
[Project3].[C2] AS [C1],
[Project3].[C1] AS [C2],
[Project3].[C4] AS [C3],
[Project3].[C3] AS [C4]
FROM ( SELECT
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
[Project2].[C1] + [Project2].[C3] AS [C3],
[Project2].[C3] AS [C4]
FROM ( SELECT
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
(SELECT
SUM([Extent2].[NbrOfJobRunsFailed]) AS [A1]
FROM [dbo].[DollarU_JobRuns] AS [Extent2]
WHERE ([Extent2].[Day] >= '2017-09-01') AND ([Extent2].[Day] <= '2017-09-30') AND (([Project1].[C2] = (SUBSTRING([Extent2].[SYSU], 0 + 1, 2))) OR (([Project1].[C2] IS NULL) AND (SUBSTRING([Extent2].[SYSU], 0 + 1, 2) IS NULL)))) AS [C3]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [C2]
FROM ( SELECT
[Filter1].[K1] AS [K1],
SUM([Filter1].[A1]) AS [A1]
FROM ( SELECT
SUBSTRING([Extent1].[SYSU], 0 + 1, 2) AS [K1],
[Extent1].[NbrOfJobRunsSucceeded] AS [A1]
FROM [dbo].[DollarU_JobRuns] AS [Extent1]
WHERE ([Extent1].[Day] >= '2017-09-01') AND ([Extent1].[Day] <= '2017-09-30')
) AS [Filter1]
GROUP BY [K1]
) AS [GroupBy1]
) AS [Project1]
) AS [Project2]
) AS [Project3]
ORDER BY [Project3].[C3] DESC
UNION ALL
SELECT
[Project7].[C3] AS [C1],
[Project7].[C1] AS [C2],
[Project7].[C5] AS [C3],
[Project7].[C4] AS [C4]
FROM ( SELECT
[Project6].[C1] AS [C1],
[Project6].[C1] + [Project6].[C2] AS [C2],
N'Others' AS [C3],
[Project6].[C1] + [Project6].[C2] AS [C4],
[Project6].[C2] AS [C5]
FROM ( SELECT
[Project5].[C1] AS [C1],
(SELECT
SUM([Extent4].[NbrOfJobRunsFailed]) AS [A1]
FROM [dbo].[DollarU_JobRuns] AS [Extent4]
WHERE ([Extent4].[Day] >= '2017-09-01') AND ([Extent4].[Day] <= '2017-09-30') AND (([Project5].[C2] = (SUBSTRING([Extent4].[SYSU], 0 + 1, 2))) OR (([Project5].[C2] IS NULL) AND (SUBSTRING([Extent4].[SYSU], 0 + 1, 2) IS NULL)))) AS [C2]
FROM ( SELECT
[GroupBy3].[A1] AS [C1],
[GroupBy3].[K1] AS [C2]
FROM ( SELECT
[Filter3].[K1] AS [K1],
SUM([Filter3].[A1]) AS [A1]
FROM ( SELECT
SUBSTRING([Extent3].[SYSU], 0 + 1, 2) AS [K1],
[Extent3].[NbrOfJobRunsSucceeded] AS [A1]
FROM [dbo].[DollarU_JobRuns] AS [Extent3]
WHERE ([Extent3].[Day] >= '2017-09-01') AND ([Extent3].[Day] <= '2017-09-30')
) AS [Filter3]
GROUP BY [K1]
) AS [GroupBy3]
) AS [Project5]
) AS [Project6]
) AS [Project7]
ORDER BY [Project7].[C2] DESC
OFFSET 10 ROWS ) AS [UnionAll1]
GROUP BY [UnionAll1].[C1]
) AS [GroupBy5]
) AS [Project9]
ORDER BY [Project9].[C3] DESC
I've created also a SQL view which provides me the same results:
With TopItems As
(
SELECT SUBSTRING( [SYSU],1,2) AS [System]
, Sum([NbrOfJobRunsSucceeded]) AS NbrOfJobRunsSucceeded
, Sum([NbrOfJobRunsFailed]) AS NbrOfJobRunsFailed
, Sum([NbrOfJobRunsSucceeded] + [NbrOfJobRunsFailed]) AS Total
, ROW_NUMBER() OVER (ORDER BY Sum([NbrOfJobRunsSucceeded] + [NbrOfJobRunsFailed]) DESC) AS Num
FROM [dbo].[DollarU_JobRuns]
WHERE [Day] >= DATEADD(month, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
AND [Day] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
GROUP BY SUBSTRING( [SYSU],1,2)
)
SELECT [System], NbrOfJobRunsSucceeded, NbrOfJobRunsFailed, Total
FROM TopItems
WHERE Num <= 10
UNION ALL
SELECT
'Others'
, Sum([NbrOfJobRunsSucceeded]) AS NbrOfJobRunsSucceeded
, Sum([NbrOfJobRunsFailed]) AS NbrOfJobRunsFailed
, Sum(Total)
FROM TopItems
WHERE Num > 10
When comparing the results first of all I find that my own crafted SQL view is more concise. But I like to create LINQ statements and it is also quite concise but the SQL query generated is very big and not understandable. But whatever is generated (it is anyway under the hood) is not so much important as long as the performance is fine. But the SQL view has an execution time of 27.5 ms which is a lot faster than 120.3 ms of the query generated by LINQ.
Is LINQ so bad to use or do I miss something to make it more performant?
Upvotes: 1
Views: 81
Answers (2)
Ivan Stoev
Reputation: 205649
Looks like a defect in EF query translation of the 2 let statements using group aggregates. You can try using multiple projection instead:
var baseQuery = DollarU_JobRuns
.Where(jr => jr.Day >= first && jr.Day <= last)
.GroupBy(jr => new { System = jr.SYSU.Substring(0, 2) })
.Select(systemGroup => new
{
System = systemGroup.Key.System,
NbrOfJobRunsSucceeded = systemGroup.Sum(x => x.NbrOfJobRunsSucceeded),
NbrOfJobRunsFailed = systemGroup.Sum(x => x.NbrOfJobRunsFailed),
})
.Select(item => new
{
item.System,
item.NbrOfJobRunsSucceeded,
item.NbrOfJobRunsFailed,
Total = item.NbrOfJobRunsFailed + item.NbrOfJobRunsFailed
})
.OrderByDescending(item => item.Total);
so the final query now translates to:
SELECT
[Project5].[C5] AS [C1],
[Project5].[C4] AS [C2],
[Project5].[C1] AS [C3],
[Project5].[C2] AS [C4],
[Project5].[C3] AS [C5]
FROM ( SELECT
[GroupBy3].[A1] AS [C1],
[GroupBy3].[A2] AS [C2],
[GroupBy3].[A3] AS [C3],
[GroupBy3].[K1] AS [C4],
1 AS [C5]
FROM ( SELECT
[UnionAll1].[C1] AS [K1],
SUM([UnionAll1].[C2]) AS [A1],
SUM([UnionAll1].[C3]) AS [A2],
SUM([UnionAll1].[C4]) AS [A3]
FROM (SELECT TOP (10)
[Project1].[C3] AS [C1],
[Project1].[C1] AS [C2],
[Project1].[C2] AS [C3],
[Project1].[C4] AS [C4]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2],
[GroupBy1].[K1] AS [C3],
[GroupBy1].[A2] + [GroupBy1].[A2] AS [C4]
FROM ( SELECT
[Filter1].[K1] AS [K1],
SUM([Filter1].[A1]) AS [A1],
SUM([Filter1].[A2]) AS [A2]
FROM ( SELECT
SUBSTRING([Extent1].[SYSU], 0 + 1, 2) AS [K1],
[Extent1].[NbrOfJobRunsSucceeded] AS [A1],
[Extent1].[NbrOfJobRunsFailed] AS [A2]
FROM [dbo].[DollarU_JobRuns] AS [Extent1]
WHERE ([Extent1].[Day] >= @p__linq__0) AND ([Extent1].[Day] <= @p__linq__1)
) AS [Filter1]
GROUP BY [K1]
) AS [GroupBy1]
) AS [Project1]
ORDER BY [Project1].[C4] DESC
UNION ALL
SELECT
[Project3].[C4] AS [C1],
[Project3].[C1] AS [C2],
[Project3].[C2] AS [C3],
[Project3].[C5] AS [C4]
FROM ( SELECT
[GroupBy2].[A1] AS [C1],
[GroupBy2].[A2] AS [C2],
[GroupBy2].[A2] + [GroupBy2].[A2] AS [C3],
N'Others' AS [C4],
[GroupBy2].[A1] + [GroupBy2].[A2] AS [C5]
FROM ( SELECT
[Filter2].[K1] AS [K1],
SUM([Filter2].[A1]) AS [A1],
SUM([Filter2].[A2]) AS [A2]
FROM ( SELECT
SUBSTRING([Extent2].[SYSU], 0 + 1, 2) AS [K1],
[Extent2].[NbrOfJobRunsSucceeded] AS [A1],
[Extent2].[NbrOfJobRunsFailed] AS [A2]
FROM [dbo].[DollarU_JobRuns] AS [Extent2]
WHERE ([Extent2].[Day] >= @p__linq__2) AND ([Extent2].[Day] <= @p__linq__3)
) AS [Filter2]
GROUP BY [K1]
) AS [GroupBy2]
) AS [Project3]
ORDER BY [Project3].[C3] DESC
OFFSET 10 ROWS ) AS [UnionAll1]
GROUP BY [UnionAll1].[C1]
) AS [GroupBy3]
) AS [Project5]
ORDER BY [Project5].[C3] DESC
Upvotes: 1
bjnr
Reputation: 3437
I suggest you create a view closer to your business domain and consider referring that view in your EF model.
Upvotes: 0
Related Questions
- Why is my linq to SQL query very slow?
- Linq to SQL Slow Query
- Query in Entity Framework takes longer than direct sql
- Linq queries are very slow
- Why is SqlQuery a lot faster than using LINQ expression on views?
- Slow performance in LINQ query
- Entity Framework: Query is slow
- slow sql query C#
- LINQ Query takes too long
- Standard SQL Query much faster than Linq 2 SQL