首页新闻找找看学习计划

EF查询速度很慢怎样解决呢?

0
悬赏园豆:15 [已关闭问题] 关闭于 2017-05-10 14:02

EF在查询10万条数据的时候,发现速度特别慢,请问大神有什么好的建议我借鉴一下?

EF
大华q的主页 大华q | 初学一级 | 园豆:2
提问于:2016-11-22 09:37
< >
分享
所有回答(6)
0
Jeffcky | 园豆:2563 (老鸟四级) | 2016-11-22 09:42
0

我倒想知道为什么你要一次性撸出来10万条数据?

Daniel Cai | 园豆:10374 (专家六级) | 2016-11-22 09:43

不是 ,数据库一共有10万条数据,查询其中的10条数据结果就很慢

 

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 09:45

@画沙落叶: 每次都慢?其他查询呢?

支持(0) 反对(0) Daniel Cai | 园豆:10374 (专家六级) | 2016-11-22 10:02

@Daniel Cai: 每次都慢

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 10:02

@画沙落叶: 你query的语句是什么样的?贴下相关代码。

支持(0) 反对(0) Daniel Cai | 园豆:10374 (专家六级) | 2016-11-22 10:03

@Daniel Cai: 

 

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[RoomID] AS [RoomID],
[Extent1].[BranchID] AS [BranchID],
[Extent1].[TypeID] AS [TypeID],
[Extent1].[Cleaner] AS [Cleaner],
[Extent1].[Remark] AS [Remark],
[Extent1].[IsClean] AS [IsClean],
[Extent1].[Checker] AS [Checker],
[Extent1].[RealCleaningDate] AS [RealCleaningDate],
[Extent1].[CleaningDate] AS [CleaningDate],
[Extent1].[Cleaner2] AS [Cleaner2],
[Extent1].[Cleaner3] AS [Cleaner3],
[Extent1].[CheckRoomStatus] AS [CheckRoomStatus],
[Extent1].[CheckRoomRemark] AS [CheckRoomRemark],
[Extent1].[CheckRoomRealName] AS [CheckRoomRealName],
[Extent1].[CheckRoomPersonnelID] AS [CheckRoomPersonnelID],
[Extent1].[CheckRoomDate] AS [CheckRoomDate],
[Extent1].[CreateDate] AS [CreateDate],
[Extent1].[CreateStaffID] AS [CreateStaffID],
[Extent1].[CreateStaffName] AS [CreateStaffName],
[Extent2].[ID] AS [ID1],
[Extent2].[Name] AS [Name],
[Extent2].[BranchID] AS [BranchID1],
[Extent2].[HousingID] AS [HousingID],
[Extent2].[Type] AS [Type],
[Extent2].[CreateDate] AS [CreateDate1],
[Extent2].[CreateStaffID] AS [CreateStaffID1],
[Extent2].[CreateStaffName] AS [CreateStaffName1],
[Extent3].[ID] AS [ID2],
[Extent3].[Name] AS [Name1],
[Extent3].[ProvinceID] AS [ProvinceID],
[Extent3].[CityID] AS [CityID],
[Extent3].[AreaID] AS [AreaID],
[Extent3].[Address] AS [Address],
[Extent3].[IsShow] AS [IsShow],
[Extent3].[IsUse] AS [IsUse],
[Extent3].[ShowParameter] AS [ShowParameter],
[Extent3].[Contact] AS [Contact],
[Extent3].[Telephone] AS [Telephone],
[Extent3].[Mobile] AS [Mobile],
[Extent3].[QQ] AS [QQ],
[Extent3].[Introduce] AS [Introduce],
[Extent3].[Attractions] AS [Attractions],
[Extent3].[Service] AS [Service],
[Extent3].[MapLine] AS [MapLine],
[Extent3].[Equipment] AS [Equipment],
[Extent3].[CheckInNotes] AS [CheckInNotes],
[Extent3].[Longitude] AS [Longitude],
[Extent3].[Dimension] AS [Dimension],
[Extent3].[InfoLabel] AS [InfoLabel],
[Extent3].[BranchTypeID] AS [BranchTypeID],
[Extent3].[BusinessID] AS [BusinessID],
[Extent3].[CreateDate] AS [CreateDate2],
[Extent3].[CreateStaffID] AS [CreateStaffID2],
[Extent3].[CreateStaffName] AS [CreateStaffName2],
[Extent4].[ID] AS [ID3],
[Extent4].[Name] AS [Name2],
[Extent4].[IsDelete] AS [IsDelete],
[Extent4].[ProvinceID] AS [ProvinceID1]
FROM [dbo].[OM_Cleaning] AS [Extent1]
INNER JOIN [dbo].[MM_Room] AS [Extent2] ON [Extent1].[RoomID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[MM_Branch] AS [Extent3] ON [Extent1].[BranchID] = [Extent3].[ID]
LEFT OUTER JOIN [dbo].[C_City] AS [Extent4] ON [Extent3].[CityID] = [Extent4].[ID]

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 10:04

IQueryable<Cleaning> clean = context.Clean.Include("Branch").Include("Room").Include("Branch.City");
if (request.BranchID > 0)
{
clean = clean.Where(m => m.BranchID == request.BranchID);
}

  return clean.OrderByDescending(m => m.ID).ToPagedList(request.PageIndex, request.PageSize);

MVC底层语句就是这样写的

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 10:06

@画沙落叶: 你的sql语句和你的linq语句貌似对不上啊。你的sql从哪里抓出来的?

支持(0) 反对(0) Daniel Cai | 园豆:10374 (专家六级) | 2016-11-22 11:00

@Daniel Cai: 我的底层数据只贴出一部分来,我的sql是通过mvc自动生成sql

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:02

@画沙落叶: 根据你的linq语句,看不出来什么问题,最多也就是确保BranchID上有个索引。建议贴完整一些。

支持(0) 反对(0) Daniel Cai | 园豆:10374 (专家六级) | 2016-11-22 11:16

@Daniel Cai: 

IQueryable<Cleaning> clean = context.Clean.Include("Branch").Include("Room").Include("Branch.City");
if (request.BranchID > 0)
{
clean = clean.Where(m => m.BranchID == request.BranchID);
}
if (request.CityId > 0)
{
clean = clean.Where(m => m.Branch.CityID == request.CityId);
}
if (!string.IsNullOrEmpty(request.CheckRoomRealName))
{
clean = clean.Where(m => m.CheckRoomRealName == request.CheckRoomRealName);
}
if (!string.IsNullOrEmpty(request.RoomName))
{
clean = clean.Where(m => m.Room.Name == request.RoomName);
}
if (request.CheckRoomStatus > 0)
{
clean = clean.Where(m => m.CheckRoomStatus == request.CheckRoomStatus);
}
if (request.CheckStatus > 0)
{
clean = clean.Where(m => m.IsClean == request.CheckStatus);
}
if (request.StartDate.ToString() != "0001/1/1 0:00:00")
{
clean = clean.Where(m => m.CleaningDate >= request.StartDate);
}
if (request.EndDate.ToString() != "0001/1/1 0:00:00")
{
request.EndDate = request.EndDate.AddDays(1.0);
clean = clean.Where(m => m.CleaningDate <= request.EndDate);
}
if (request.TypeID > 0)
{
clean = clean.Where(m => m.TypeID == request.TypeID);
}
if (!string.IsNullOrEmpty(request.Cleaner))
{
clean = clean.Where(m => m.Cleaner == request.Cleaner);
}
return clean.OrderByDescending(m => m.ID).ToPagedList(request.PageIndex, request.PageSize);

 

这是底层全部的代码你看看

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:18

好了  我解决了

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:23
0

单例模式?

疯五五爱喝咖啡 | 园豆:247 (菜鸟二级) | 2016-11-22 10:24
0

你的topagedlist是啥东西.你的sql里都没看到分页的的语句.

拿sql监视器看下他真实执行的sql吧.

吴瑞祥 | 园豆:28770 (高人七级) | 2016-11-22 11:11

public static PagedList<T> ToPagedList<T>
(
this IQueryable<T> allItems,
int pageIndex,
int pageSize
)
{
if (pageIndex < 1)
pageIndex = 1;
var itemIndex = (pageIndex - 1) * pageSize;
var pageOfItems = allItems.Skip(itemIndex).Take(pageSize).ToList();
var totalItemCount = allItems.ToList().Count();
return new PagedList<T>(pageOfItems, pageIndex, pageSize, totalItemCount);
}

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:13

@画沙落叶: var totalItemCount = allItems.ToList().Count(); ..........

支持(0) 反对(0) 吴瑞祥 | 园豆:28770 (高人七级) | 2016-11-22 11:13

@吴瑞祥: 怎么有问题吗?

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:14

@吴瑞祥: 好了 我解决了

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2016-11-22 11:23
0

好了知道了

qsy | 园豆:92 (初学一级) | 2016-11-25 15:44
0

您好,您是怎么解决的呢?

方方方先生 | 园豆:204 (菜鸟二级) | 2019-08-05 10:15

var totalItemCount = allItems.ToList().Count(); 这句话造成的,因为我是取数据数量的时候先进行list集合转变,然后再去的数据,也就是说内存中有10万数据,然后再计算数量

支持(0) 反对(0) 大华q | 园豆:2 (初学一级) | 2019-08-05 11:21
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册