wpf linq mysql 查询效率问题

[wilsonhong]

本机测试3秒，连服务器数据库6-7秒 ，请假下有没有优化的可能

页面有3个图表，这个方法是获取数据的


public static PictureDataModel GetAffairInfoByMonthLineIdSelectedAreaChildren(DateTime firstDay, int lineId)
{
var lastDay = firstDay.AddMonths(1);

var selectedArea = Storage.Areas.FirstOrDefault(p => p.AreaId == Storage.SelectedAreaId);
//获取所有子节点
var childrenAreaId = AreaHelper.GetGrandChild(selectedArea).Select(x => x.AreaId);
//获取第一所有子节点
var childrenArea = AreaHelper.GetMyChildrenArea(Storage.SelectedAreaId);
//以上都是读缓存数据下面是连接数据库
_db = new YmsDb();

if (Storage.SelectedAreaId == Storage.MyArea.AreaId)
{
var areaCount = from ai in _db.AffairsInfo
join a in _db.Affairs on ai.AffairId equals a.AffairId
join w in _db.Window on a.WindowId equals w.WindowId
where Storage.MyChildrenAreaId.Contains(ai.AreaId) && w.AffairLineId.Equals(lineId) &&
ai.LastTransTime > firstDay && ai.LastTransTime < lastDay

group ai by ai.AreaId
into g
select new { Id = g.Key, ValueInt = g.Count() };

var columnData = (from item in childrenArea
let children = AreaHelper.GetGrandChild(item).Select(x => x.AreaId)
let count = areaCount.Where(p => (children.Contains(p.Id))).Select(p => p.ValueInt).DefaultIfEmpty().Sum()

select new ChartDataClass
{
Id = item.AreaId,
XValue = item.AreaName,
YValue = (int?)count

}).ToList();

var pieCount = from ai in _db.AffairsInfo
join a in _db.Affairs on ai.AffairId equals a.AffairId
join w in _db.Window on a.WindowId equals w.WindowId
where Storage.MyChildrenAreaId.Contains(ai.AreaId) && w.AffairLineId.Equals(lineId) &&
ai.LastTransTime > firstDay && ai.LastTransTime < lastDay

group ai by ai.AreaId
into g
select new { Id = g.Key, ValueInt = g.Count() };

var pieData = (from item in childrenArea
let children = AreaHelper.GetGrandChild(item).Select(x => x.AreaId)
let count = pieCount.Where(p => (children.Contains(p.Id))).Select(p => p.ValueInt).DefaultIfEmpty().Sum()

select new ChartDataClass
{
Id = item.AreaId,
XValue = item.AreaName,
YValue = (int?)count

}).ToList();

var affairInfoHourCount = from ai in _db.AffairsInfo.AsEnumerable()
join a in _db.Affairs on ai.AffairId equals a.AffairId
join w in _db.Window on a.WindowId equals w.WindowId
where Storage.MyChildrenAreaId.Contains(ai.AreaId) && w.AffairLineId.Equals(lineId) &&
ai.LastTransTime > firstDay && ai.LastTransTime < lastDay && ai.LastTransTime.HasValue
group ai by ai.LastTransTime.Value.Hour
into g
select new { Hour = g.Key, ValueInt = g.Count() };


var lineData = WorkTimer.Select(hour =>
new ChartDataClass
{
XValue = hour + ":00",
YValue = affairInfoHourCount.Where(p => p.Hour == hour).Select(p => p.ValueInt).Sum()
}).ToList();

var countAllTime = affairInfoHourCount.Select(p => p.ValueInt).DefaultIfEmpty().Sum();

var countOnWorkTime = (int)lineData.Sum(x => x.YValue.GetValueOrDefault());

var chartDataOtherTime = new ChartDataClass
{
XValue = "其他时段",
YValue = countAllTime - countOnWorkTime
};

lineData.Add(chartDataOtherTime);

var obj = new PictureDataModel();
obj.ColumnData = columnData;
obj.PieData = pieData;
obj.LineData = lineData;


return obj;
}

[exception92]

代码如此乱，linq 最终会转成mysql语句执行。代码中存在 好几个AreaHelper.GetGrandChild 类似这样的查询，每写一次，都会执行一次，不如做成公共方法；类似children.Contains(p.Id) 中 看看能不能创建数据库索引

[wilsonhong]

引用 1 楼 duanzi_peng 的回复:

代码如此乱，linq 最终会转成mysql语句执行。代码中存在 好几个AreaHelper.GetGrandChild 类似这样的查询，每写一次，都会执行一次，不如做成公共方法；类似children.Contains(p.Id) 中 看看能不能创建数据库索引

AreaHelper.GetGrandChild 不读取数据库的，读的是内存数据

[宇峰科技]

为何不用原生SQl肯定比Linq快

[exception92]

引用 2 楼 wilsonhong 的回复:

[quote=引用 1 楼 duanzi_peng 的回复:] 代码如此乱，linq 最终会转成mysql语句执行。代码中存在 好几个AreaHelper.GetGrandChild 类似这样的查询，每写一次，都会执行一次，不如做成公共方法；类似children.Contains(p.Id) 中 看看能不能创建数据库索引

AreaHelper.GetGrandChild 不读取数据库的，读的是内存数据[/quote] 不要频繁的 toList

[vbfool]

引用 4 楼 blogtjf 的回复:

为何不用原生SQl肯定比Linq快

慢也没慢到几毫秒，区别不是在这里，而是在于SQL优化。