一、ef core 读取text类型慢_ef core读取大字符串字段慢
分析:
当服务器配置低,text富文本字符串比较大超过100kb的时候,使用ef读取数据明显变慢。
变慢的两大使用特点:
1.text 类型富文本字段内容太长,100kb以上
2.读取时候还关联了其他表的数据。
此两种特点,导致ef core读取数据慢,慢的明显。
解决方案:
业务方案:
建议text字段不要存入太大的字符;再客户编辑富文本时候,复制粘贴后去掉样式,重新排版。
业务方案优先考虑,如果确实字段需要内容大,再考虑下方技术方案。
技术方案:
1.列表读取内容 过滤掉text的字段,不从数据读取大文本
2.详情读取内容,将关联表数据和text的字段分开查询或者,拆分成2个接口。
二、ef core大文本字段列表读取优化
优化之前:
var list = query.Include(q => q.Sch_Label).Include(q => q.Sch_Month).Skip(skip)
.Take(10)
.ToList()
.Select(q => new
{
q.ID,
q.Name,
q.Logo,
q.ViewCount,
q.CreateTime,
AreaShow = q.GetAreaShow(),
MonthShow = q.GetMonthShow(),
LabelList = q.Sch_Label
}).ToList();
优化之后:
var list = query
.Include(q => q.Sch_Label)
.Include(q => q.Sch_Month).Skip(skip)
.Select(q => new School() {
ID = q.ID,
Name = q.Name,
ViewCount = q.ViewCount,
CreateTime = q.CreateTime,
Logo = q.Logo,
Sch_Month = q.Sch_Month,
Sch_Label = q.Sch_Label
})
.Take(10)
.ToList()
.Select(q => new
{
q.ID,
q.Name,
q.Logo,
q.ViewCount,
q.CreateTime,
AreaShow = q.GetAreaShow(),
MonthShow = q.GetMonthShow(),
LabelList = q.Sch_Label
}).ToList();
性能提升相当明显。
三、ef core大文本字段详情读取优化
关联表和 text大文本字段,分开获取;性能提升明显。
优化之前:
//获取详情
public IActionResult GetDetail(int id)
{
var model = _school.GetQueryable().Where(q => q.ID == id)
.Include(q => q.Sch_Image)
.Include(q => q.Sch_Label)
.Include(q => q.Sch_Month)
.FirstOrDefault();
JObject obj = JObject.FromObject(model, MvcContext.GetJsonSerializer());
//学校视频
VideoOperate _video = new VideoOperate();
var videoList = _video.GetBySchool(id, 8).Select(q => new
{
q.ID,
q.Title,
q.ImgUrl,
q.ViewCount
});
obj.Add("videoList", JArray.FromObject(videoList, MvcContext.GetJsonSerializer()));
//流量++
model.ViewCount++;
_school.Modify(model);
return Json(obj);
}
优化之后:
//获取详情
public IActionResult GetDetail(int id)
{
var model = _school.GetQueryable().Where(q => q.ID == id)
//.Include(q => q.Sch_Image)
//.Include(q => q.Sch_Label)
//.Include(q => q.Sch_Month)
.FirstOrDefault();
//JObject obj = JObject.FromObject(model.Result, MvcContext.GetJsonSerializer());
//学校视频
//VideoOperate _video = new VideoOperate();
//var videoList = _video.GetBySchool(id, 8).Select(q => new
//{
// q.ID,
// q.Title,
// q.ImgUrl,
// q.ViewCount
//});
//obj.Add("videoList", JArray.FromObject(videoList, MvcContext.GetJsonSerializer()));
//流量++
model.ViewCount++;
_school.Modify(model);
//return Json(obj);
return Json(model);
}
//获取关联表资料
public IActionResult GetDetailMore(int id)
{
VideoOperate _video = new VideoOperate();
Sch_ImageOperate _img = new Sch_ImageOperate();
//学校图片
var Sch_Image = _img.GetQueryable().Where(q => q.SchID == id).OrderByDescending(q => q.Sort).ToList();
//学校标签
var Sch_Label = _img.Context.Sch_Label.Where(q => q.SchID == id).ToList();
//学校月份
var Sch_Month = _img.Context.Sch_Month.Where(q => q.SchID == id).ToList();
//学校视频
var videoList = _video.GetBySchool(id, 8).Select(q => new
{
q.ID,
q.Title,
q.ImgUrl,
q.ViewCount
});
return Json(new
{
Sch_Image,
Sch_Label,
Sch_Month,
videoList
});
}
其他相关:好大一个坑: EF Core 异步读取大字符串字段比同步慢100多倍 | 易学教程
更多: