c# EF Core Or修改为Union优化,速度大幅度提升!
2022/8/6 1:52:44
本文主要是介绍c# EF Core Or修改为Union优化,速度大幅度提升!,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
最近在优化代码时发现linq 采用了||查询导致查询效率非常慢,查询语句出现扫表情况,
采用了Union大大提升了效率,解决了问题
优化c# 前的linq:
dateTime = dateTime.Date; DateTime BegTime = dateTime; DateTime EndTime = dateTime.AddDays(1); var v = from q in _MyDbContext.Document where q.Customer.AgentId == agentId && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS) && ((q.OutputTime >= BegTime && q.OutputTime <= EndTime && q.DocumentStatus == DocumentStatus.OutputComplete) || (q.Order.Status == OrderStatusType.SUCCESS && q.Order.SuccessTime >= BegTime && q.Order.SuccessTime <= EndTime)) select new PrinterStatisticsDto { PrinterId = q.PrinterId, PrinterName = q.PrinterName, CustomerId = q.CustomerId.Value, CustomerName = q.Customer.Name, JobType = q.JobType, SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong, PageSize = q.PageSize, ColorType = q.ColorType, PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount, Copies = q.Copies, GivePageCount = q.GivePageCount, TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0 }; var s = from item in v group item by new { item.PrinterId, item.PrinterName, item.CustomerId, item.CustomerName, item.JobType, item.PageSize, item.ColorType, item.SimpleDuplexType } into gr select new StatisticsDto { Balance = gr.Sum(a => a.TotalCost.Value), ColorType = gr.Key.ColorType, Count = gr.Sum(a => a.PageCount * a.Copies), JobType = gr.Key.JobType, SimpleDuplexType = gr.Key.SimpleDuplexType, PageSize = gr.Key.PageSize, PrinterName = gr.Key.PrinterName, PrinterId = gr.Key.PrinterId, CustomerId = gr.Key.CustomerId, CustomerName = gr.Key.CustomerName, GivePageCount = gr.Sum(a => a.GivePageCount), }; return s.ToList();
这样的代码生成的sql 语句如下:
SELECT SUM(CASE WHEN `d`.`OrderId` IS NOT NULL AND (`o`.`Status` = 1) THEN `d`.`TotalCost` ELSE 0 END) AS `Balance`, `d`.`ColorType`, SUM(CASE WHEN `d`.`CalculationCount` > 0 THEN `d`.`CalculationCount` ELSE `d`.`PageCount` END * `d`.`Copies`) AS `Count`, `d`.`JobType`, CASE WHEN `d`.`SimpleDuplexType` = 1 THEN 1 ELSE 2 END AS `SimpleDuplexType`, `d`.`PageSize`, `d`.`PrinterName`, `d`.`PrinterId`, `d`.`CustomerId`, COUNT(*) AS `OrderNumber`, SUM(`d`.`GivePageCount`) AS `GivePageCount`, timestamp('2022-08-04 00:00:00') AS `DateTime` FROM `Document` AS `d` LEFT JOIN `UserRefund` AS `u` ON `d`.`UserRefundId` = `u`.`Id` LEFT JOIN `Order` AS `o` ON `d`.`OrderId` = `o`.`Id` LEFT JOIN `Customer` AS `c` ON `d`.`CustomerId` = `c`.`Id` WHERE ((`d`.`CustomerId` = 100076) AND (`d`.`UserRefundId` IS NULL OR ((`u`.`RefundStatus` <> 3) OR `u`.`RefundStatus` IS NULL))) AND ((((`d`.`OutputTime` >= timestamp('2022-08-04 00:00:00')) AND (`d`.`OutputTime` <= timestamp('2022-08-05 00:00:00'))) AND (`d`.`DocumentStatus` = 9)) OR (((`o`.`Status` = 1) AND (`o`.`CreateTime` >= timestamp('2022-08-04 00:00:00'))) AND (`o`.`CreateTime` <= timestamp('2022-08-05 00:00:00')))) GROUP BY `d`.`PrinterId`, `d`.`PrinterName`, `d`.`CustomerId`, `c`.`Name`, `d`.`JobType`, `d`.`PageSize`, `d`.`ColorType`, CASE WHEN `d`.`SimpleDuplexType` = 1 THEN 1 ELSE 2 END ORDER BY `d`.`PrinterId` DESC, `d`.`JobType` LIMIT 100 OFFSET 0
通过优化linq后代码如下,这样效率大大提升,没有出现扫表情况:
dateTime = dateTime.Date; DateTime BegTime = dateTime; DateTime EndTime = dateTime.AddDays(1); var v = from q in _MyDbContext.Document where q.CustomerId == customerId && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS) && (q.OutputTime >= BegTime && q.OutputTime <= EndTime && q.DocumentStatus == DocumentStatus.OutputComplete) select new PrinterStatisticsDto { PrinterId = q.PrinterId, PrinterName = q.PrinterName, CustomerId = q.CustomerId.Value, CustomerName = q.Customer.Name, JobType = q.JobType, SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong, PageSize = q.PageSize, ColorType = q.ColorType, PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount, Copies = q.Copies, GivePageCount = q.GivePageCount, TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0 }; var v1 = from q in _MyDbContext.Document where q.CustomerId == customerId && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS) && q.Order.Status == OrderStatusType.SUCCESS && q.Order.SuccessTime >= BegTime && q.Order.SuccessTime <= EndTime select new PrinterStatisticsDto { PrinterId = q.PrinterId, PrinterName = q.PrinterName, CustomerId = q.CustomerId.Value, CustomerName = q.Customer.Name, JobType = q.JobType, SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong, PageSize = q.PageSize, ColorType = q.ColorType, PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount, Copies = q.Copies, GivePageCount = q.GivePageCount, TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0 }; v = v.Union(v1); var s = from item in v group item by new { item.PrinterId, item.PrinterName, item.CustomerId, item.CustomerName, item.JobType, item.PageSize, item.ColorType, item.SimpleDuplexType } into gr select new PrinterStatistics { Balance = gr.Sum(a => a.TotalCost.Value), ColorType = gr.Key.ColorType, Count = gr.Sum(a => a.PageCount * a.Copies), JobType = gr.Key.JobType, SimpleDuplexType = gr.Key.SimpleDuplexType, PageSize = gr.Key.PageSize, PrinterName = gr.Key.PrinterName, PrinterId = gr.Key.PrinterId, CustomerId = gr.Key.CustomerId, GivePageCount = gr.Sum(a => a.GivePageCount), OrderNumber = gr.Count(), DateTime = dateTime, }; return s.ToList();
这篇关于c# EF Core Or修改为Union优化,速度大幅度提升!的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2022-03-01沐雪多租宝商城源码从.NetCore3.1升级到.Net6的步骤
- 2024-05-08首个适配Visual Studio平台的国产智能编程助手CodeGeeX正式上线!C#程序员必备效率神器!
- 2024-03-30C#设计模式之十六迭代器模式(Iterator Pattern)【行为型】
- 2024-03-29c# datetime tryparse
- 2024-02-21list find index c#
- 2024-01-24convert toint32 c#
- 2024-01-24Advanced .Net Debugging 1:你必须知道的调试工具
- 2024-01-24.NET集成IdGenerator生成分布式全局唯一ID
- 2024-01-23用CI/CD工具Vela部署Elasticsearch + C# 如何使用
- 2024-01-23.NET开源的简单、快速、强大的前后端分离后台权限管理系统