Problem
I want to count email addresses with different status. I use ASP.NET MVC and this query is supposed to run every 5 minutes on these tables, which every table is on different .ndf files. About 5 million records will be inserted into email address table every day.
Is this query good enough, in terms of performance? You can also suggest any other query or ways.
var query = from p in sentMailPagingBiz.FetchMulti()
join s in emailAddressBiz.FetchMulti() on p.Id equals s.SentMailPaging_Id
where p.SentMail_Id == 5648
group s by s.SentMailPaging_Id
into g
select new
{
All = g.Count(),
ReadyToSend = g.Count(q => q.Status_Id == 1),
Sent = g.Count(q => q.Status_Id == 2),
Rejected = g.Count(q => q.Status_Id == 3),
Queued = g.Count(q => q.Status_Id == 4),
SoftBounce = g.Count(q => q.Status_Id == 5),
HardBounce = g.Count(q => q.Status_Id == 6),
};
Diagram:
Generated query:
SELECT
1 AS [C1],
[Project6].[C1] AS [C2],
[Project6].[C2] AS [C3],
[Project6].[C3] AS [C4],
[Project6].[C4] AS [C5],
[Project6].[C5] AS [C6],
[Project6].[C6] AS [C7],
[Project6].[C7] AS [C8]
FROM ( SELECT
[Project5].[C1] AS [C1],
[Project5].[C2] AS [C2],
[Project5].[C3] AS [C3],
[Project5].[C4] AS [C4],
[Project5].[C5] AS [C5],
[Project5].[C6] AS [C6],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent13]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent14] ON [Extent13].[Id] = [Extent14].[SentMailsPaging_Id]
WHERE (5648 = [Extent13].[SentMail_Id]) AND ([Project5].[SentMailsPaging_Id] = [Extent14].[SentMailsPaging_Id]) AND (6 = [Extent14].[SentMailsEmailAddressStatus_Id])) AS [C7]
FROM ( SELECT
[Project4].[C1] AS [C1],
[Project4].[SentMailsPaging_Id] AS [SentMailsPaging_Id],
[Project4].[C2] AS [C2],
[Project4].[C3] AS [C3],
[Project4].[C4] AS [C4],
[Project4].[C5] AS [C5],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent11]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent12] ON [Extent11].[Id] = [Extent12].[SentMailsPaging_Id]
WHERE (5648 = [Extent11].[SentMail_Id]) AND ([Project4].[SentMailsPaging_Id] = [Extent12].[SentMailsPaging_Id]) AND (5 = [Extent12].[SentMailsEmailAddressStatus_Id])) AS [C6]
FROM ( SELECT
[Project3].[C1] AS [C1],
[Project3].[SentMailsPaging_Id] AS [SentMailsPaging_Id],
[Project3].[C2] AS [C2],
[Project3].[C3] AS [C3],
[Project3].[C4] AS [C4],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent9]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent10] ON [Extent9].[Id] = [Extent10].[SentMailsPaging_Id]
WHERE (5648 = [Extent9].[SentMail_Id]) AND ([Project3].[SentMailsPaging_Id] = [Extent10].[SentMailsPaging_Id]) AND (4 = [Extent10].[SentMailsEmailAddressStatus_Id])) AS [C5]
FROM ( SELECT
[Project2].[C1] AS [C1],
[Project2].[SentMailsPaging_Id] AS [SentMailsPaging_Id],
[Project2].[C2] AS [C2],
[Project2].[C3] AS [C3],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent7]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent8] ON [Extent7].[Id] = [Extent8].[SentMailsPaging_Id]
WHERE (5648 = [Extent7].[SentMail_Id]) AND ([Project2].[SentMailsPaging_Id] = [Extent8].[SentMailsPaging_Id]) AND (3 = [Extent8].[SentMailsEmailAddressStatus_Id])) AS [C4]
FROM ( SELECT
[Project1].[C1] AS [C1],
[Project1].[SentMailsPaging_Id] AS [SentMailsPaging_Id],
[Project1].[C2] AS [C2],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent5]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent6] ON [Extent5].[Id] = [Extent6].[SentMailsPaging_Id]
WHERE (5648 = [Extent5].[SentMail_Id]) AND ([Project1].[SentMailsPaging_Id] = [Extent6].[SentMailsPaging_Id]) AND (2 = [Extent6].[SentMailsEmailAddressStatus_Id])) AS [C3]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [SentMailsPaging_Id],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent3]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent4] ON [Extent3].[Id] = [Extent4].[SentMailsPaging_Id]
WHERE (5648 = [Extent3].[SentMail_Id]) AND ([GroupBy1].[K1] = [Extent4].[SentMailsPaging_Id]) AND (1 = [Extent4].[SentMailsEmailAddressStatus_Id])) AS [C2]
FROM ( SELECT
[Extent2].[SentMailsPaging_Id] AS [K1],
COUNT(1) AS [A1]
FROM [dbo].[SentMailsPagings] AS [Extent1]
INNER JOIN [dbo].[SentMailsEmailAddresses] AS [Extent2] ON [Extent1].[Id] = [Extent2].[SentMailsPaging_Id]
WHERE 5648 = [Extent1].[SentMail_Id]
GROUP BY [Extent2].[SentMailsPaging_Id]
) AS [GroupBy1]
) AS [Project1]
) AS [Project2]
) AS [Project3]
) AS [Project4]
) AS [Project5]
) AS [Project6]
Solution
Generally, for things like this, Linq does not perform particularly well. You’re better off working through stored procedures.
I would group by status type, project to an anonymous type, then parse that into a new object outside of Linq (Untested)
var query = from p in sentMailPagingBiz.FetchMulti()
join s in emailAddressBiz.FetchMulti() on p.Id equals s.SentMailPaging_Id
where p.SentMail_Id == 5648
group s by new { s.SentMailPaging_Id, s.Status_Id}
into g
select new { g.Key.SentMailPaging_Id, g.Key.Status_Id, Count = g.Count() };
you parse the results of this by
var result = query.Select(g => new
{
All = g.Sum(s => s.Count),
ReadyToSend = g.Where(s => s.Status_Id == 1).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
Sent = g.Where(s => s.Status_Id == 2).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
Rejected = g.Where(s => s.Status_Id == 3).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
Queued = g.Where(s => s.Status_Id == 4).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
SoftBounce =g.Where(s => s.Status_Id == 5).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
HardBounce = g.Where(s => s.Status_Id == 6).Select(s => s.Count).DefaultIfEmpty(0).Sum(),
});
...