Counting email addresses with different status

Question

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:

DB 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]

• Gentian Kasa: Why don't you create a view and filter the data through that?
• unos baghaii: Mostly, I want to use Linq . But I take into account . It can be an approach

Answer 1

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(),
 });
...