我目前有一个查询,它不需要太长时间,但由于数据库中的数据量过大,有时会崩溃。
有人能否注意到我可以做些什么来加快查询速度?
public IList<Report> GetReport(CmsEntities context, long manufacturerId, long? regionId, long? vehicleTypeId)
{
var now = DateTime.Now;
var today = new DateTime(now.Year, now.Month, 1);
var date1monthago = today.AddMonths(-1);
var date2monthago = today.AddMonths(-2);
var date3monthago = today.AddMonths(-3);
var date4monthago = today.AddMonths(-4);
var date5monthago = today.AddMonths(-5);
var date6monthago = today.AddMonths(-6);
today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));
var query = from item in context.Invoices
where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.ManufacturerId).Contains(manufacturerId)
&& (item.InvoiceDate >= date6monthago && item.InvoiceDate <= today)
&& (regionId.HasValue && regionId.Value > 0 ? item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value) : true)
&& (item.InvType == "I" || item.InvType == null)
&& (vehicleTypeId.HasValue && vehicleTypeId.Value > 0 ? item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value) : true)
select item;
var query2 = from item in query
group item by new { item.Repair.Job.Bodyshop } into g
let manufJobs = query.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId && x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
let allJobs = query.Where(x => x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
select new tReport
{
MonthSixManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthSixJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthFiveManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthFiveJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthFourManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthFourJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthThreeManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthThreeJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthTwoManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthTwoJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthOneManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),
MonthOneJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),
ManufTotal = manufJobs.GroupBy(x => x.Repair.Job).Count(),
Total = allJobs.GroupBy(x => x.Repair.Job).Count(),
PercentageOf = ((decimal)manufJobs.GroupBy(x => x.Repair.Job).Count() / (decimal)allJobs.GroupBy(x => x.Repair.Job).Count()) * 100
};
return query2.OrderBy(x => x).ToList();
}
编辑
var query = from item in context.Invoices.AsNoTracking()
where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(x => x.ManufacturerId == manufacturerId)
&& (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
&& (item.InvType == "I" || item.InvType == null)
select item;
if (regionId.HasValue && regionId.Value > 0)
{
query = query.Where(item => item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value));
}
if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
{
query = query.Where(item => item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value));
}
var query2 = from item in hey
group item by new { item.Repair.Job.Bodyshop, item.InvoiceDate.Month } into m
select new TReport
{
Bodyshop = m.Key.Bodyshop.Name,
Bays = m.Key.Bodyshop.Bays,
Region = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).Region.Name,
BodyshopCode = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).BodyshopCode,
Total = m.Count(),
ManufTotal = m.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
Totals = m.GroupBy(j => j.InvoiceDate.Month).Select(j => new TPercentReportInner
{
Month = j.Key,
ManufTotal = j.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
AllTotal = j.Count()
})
};
我已经简化了查询语句。但是,现在它的性能比以前还要差?
item.Repair.Job
,如果你直接在工作上执行查询,会不会更快呢?另外,我看到你有两个查询。有时候惰性求值是一件好事,但在这里,如果你像这样做:var query2 = from item in query.Tolist()...
可能会更好。 但我能想到的最好的改进是在数据库中创建一个视图,并使用标量函数检索信息。Entity Framework 可以处理这个问题。 即使这样可能会很慢,你最终可能还是要做跳过/取出操作。 当然,拥有一个索引数据库也是有帮助的。 - memory of a dream