使用LINQ . select()转换为新类型太慢了
本文关键字:类型 LINQ select 转换 使用 新类型 | 更新日期: 2023-09-27 18:05:35
当前项目,打破了这个问题:
客户端存储库:
public class ClientRepository
{
// Members
private masterDataContext _db;
// Constructor
public ClientRepository()
{
_db = new masterDataContext();
}
public IEnumerable<ClientName> GetCorporateClientNames()
{
return _db.corporate_client_tbs.Select(o => new ClientName { id = o.id, name = o.company_name }).AsEnumerable();
}
public IEnumerable<ClientName> GetRetailClientNames()
{
return _db.retail_client_tbs.Select(o => new ClientName { id = o.id, name = o.name }).AsEnumerable();
}
// Define return type
public class ClientName
{
public int id { get; set; }
public string name { get; set; }
}
}
现在在控制器中我有以下内容:
public ActionResult Index()
{
var _visits = _db.GetAllServiceVisits();
return View(_visits);
}
加载当前存在的200行奇数行的视图大约需要4秒。
我想添加一个属性"client"到包含客户端名称的访问模型。客户端的名称将来自两个不同的表中的一个,该表是从两个类型为"ClientName"的数组中的一个中获取的。
这是第一种方法,使用LINQ:
public ActionResult Index()
{
private ClientRepository _cr = new ClientRepository();
var _retailclients = _cr.GetRetailClientNames().ToArray();
var _corporateclients = _cr.GetCorporateClientNames().ToArray();
var _visits = _db.GetAllServiceVisits();
var _temp = _visits.Select(o => new ServiceVisitViewModel
{
service_visit = o,
client = (o.client_type ? _corporateclients.Where(p => p.id == o.client_id).First().name : _retailclients.Where(p => p.id == o.client_id).First().name)
}).ToArray();
return View(_temp);
}
这是第二种方法,使用普通的c#:
public ActionResult Index()
{
private ClientRepository _cr = new ClientRepository();
var _retailclients = _cr.GetRetailClientNames().ToArray();
var _corporateclients = _cr.GetCorporateClientNames().ToArray();
var _visits = _db.GetAllServiceVisits();
List<ServiceVisitViewModel> _temp = new List<ServiceVisitViewModel>();
foreach (service_visit_tb v in _visits)
{
_temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p => p.id == v.client_id).First().name : _retailclients.Where(p => p.id == v.client_id).First().name) });
}
return View(_temp);
}
根据我的测试,第二种方法大约快8 - 10倍。
我能看到的唯一区别是。select语句。
有人能告诉我,如果我在第一种方法或替代方法中做错了什么,为什么第一种方法如此!@#$ing缓慢?
编辑:_db.GetAllServiceVisits()定义如下:
public IEnumerable<service_visit_tb> GetAllServiceVisits()
{
var _visits = _db.service_visit_tbs;
return _visits.AsEnumerable();
}
结束编辑
第二个编辑:我已经从日志中的每个条目中删除了这一行:
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
上下文日志如下:
// This query is to fetch all the clients of Type One (corresponding to _cr.GetRetailClientNames() )
SELECT [t0].[id], [t0].[name]
FROM [genii].[retail_client_tb] AS [t0]
// This query is to fetch all the clients of Type Two (corresponding to _cr.GetCorporateClientNames() )
SELECT [t0].[id], [t0].[company_name] AS [name]
FROM [genii].[corporate_client_tb] AS [t0]
// This is the main query (loading roughly 250 records) which fetchs all Visits
SELECT [t0].[id], [t0].[client_type], [t0].[client_id], [t0].[machine_type], [t0].[machineID], [t0].[visit_type], [t0].[scheduledon], [t0].[arrivedon], [t0].[completedon], [t0].[reported_problem], [t0].[diagnosed_problem], [t0].[action_taken], [t0].[visit_status], [t0].[engineer_id], [t0].[reference_id], [t0].[addedby], [t0].[addedon], [t0].[modifiedby], [t0].[modifiedon]
FROM [genii].[service_visit_tb] AS [t0]
// These next queries are not being manually called by me, I assume they are being
// called when the Razor view is compiled since I am calling the name value of a linked table as such:
// @item.service_visit.engineer_tb.name
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8]
SELECT [t0].[id], [t0].[status]
FROM [genii].[visit_status_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
SELECT [t0].[id], [t0].[name]
FROM [genii].[engineer_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [11]
SELECT [t0].[id], [t0].[name]
FROM [genii].[engineer_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [7]
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
SELECT [t0].[id], [t0].[type]
FROM [genii].[visit_type_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
SELECT [t0].[id], [t0].[name]
FROM [genii].[engineer_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [5]
SELECT [t0].[id], [t0].[name]
FROM [genii].[engineer_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [4]
SELECT [t0].[id], [t0].[status]
FROM [genii].[visit_status_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8]
SELECT [t0].[id], [t0].[status]
FROM [genii].[visit_status_tb] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
问题的附录:是否有更好的方法来提取这些数据?我总是假定(从未检查过)LINQ上下文给我的基于外键的数据访问是最好的,但是看到这些额外的查询,我不再那么确定了!
将在今天晚些时候公布第二部分执行的速度(这是孟买的一个长周末,但我们正在努力完成)
结束编辑
第三编辑(我正在考虑从web服务器到客户端的响应,因为所有的计算/抓取/绑定等都应该考虑在内。)
方法一:6.85秒(从3个表调用,然后使用c#将其转换为视图模型)
public IEnumerable<service_visit_tb> GetAllServiceVisits()
{
var _visits = _db.service_visit_tbs;
_db.Log = new DebuggerWriter();
return _visits.AsEnumerable();
}
public ActionResult Index()
{
var _retailclients = _cr.GetRetailClientNames().ToArray();
var _corporateclients = _cr.GetCorporateClientNames().ToArray();
var _visits = _db.GetAllServiceVisits();
List<ServiceVisitViewModel> _temp = new List<ServiceVisitViewModel>();
foreach (service_visit_tb v in _visits)
{
_temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p => p.id == v.client_id).First().name : _retailclients.Where(p => p.id == v.client_id).First().name) });
//}
return View(_temp);
}
方法二:8.59秒(从3个表调用,然后使用LINQ对视图模型进行强制转换)
public IEnumerable<service_visit_tb> GetAllServiceVisits()
{
var _visits = _db.service_visit_tbs;
_db.Log = new DebuggerWriter();
return _visits.AsEnumerable();
}
public ActionResult Index()
{
var _retailclients = _cr.GetRetailClientNames().ToArray();
var _corporateclients = _cr.GetCorporateClientNames().ToArray();
var _visits = _db.GetAllServiceVisits();
var _temp = _visits.Select(o => new ServiceVisitViewModel
{
service_visit = o,
client = (o.client_type ? _corporateclients.Where(p => p.id == o.client_id).First().name : _retailclients.Where(p => p.id == o.client_id).First().name)
});
return View(_temp);
}
方法三:5.76秒(所有内容在一个LINQ查询中-在数据库上执行)
public IEnumerable<ServiceVisitViewModel> GetAllServiceVisitsNew()
{
var _visits = _db.service_visit_tbs.Select(o => new ServiceVisitViewModel
{
service_visit = o,
client = (o.client_type ? _db.corporate_client_tbs.Where(c=> c.id == o.client_id).First().company_name : _db.retail_client_tbs.Where(c=> c.id == o.client_id).First().name)
});
_db.Log = new DebuggerWriter();
return _visits;
}
public ActionResult Index()
{
var _visits = _db.GetAllServiceVisitsNew();
return View(_visits());
}
我想这就决定了。谢谢大家的帮助。我把Jon标记为正确答案,因为他在数据库端做所有事情的方法是真正成功的。非常感谢所有不辞辛苦回复的人。
结束编辑
Select
语句在这里产生了巨大的差异-因为它改变了数据库中正在做的事情。(我假设_db.GetAllServiceVisits()
返回IQueryable<T>
)
您已经将所有零售和公司客户端获取到内存,因为您正在使用ToArray
。您的Select
调用将(我怀疑)然后将所有数据发送回到数据库,以便可以在那里执行Select
和Where
。我怀疑如果你查看SQL profiler,它将是一个非常奇怪的查询。
如果您强制所有都在客户端完成,它应该与后一种方法基本相同。这很容易做到,使用:
var _visits = _db.GetAllServiceVisits().ToList();
…但是,这意味着每次访问此页面时,都要从数据库中提取三个表的所有。我觉得那不是个好计划。
但是,如果您可以在数据库中执行所有操作,而不需要先将所有零售和企业客户端读取到内存中,那将会更好。
可能就像更改存储库方法以返回IQueryable<T>
而不是IEnumerable<T>
,并删除对AsEnumerable
的调用一样简单。
哇。你在这里做了很多循环。循环中的每个Where
在循环的每次迭代中都循环一个数组来查找一个元素。
制作客户端字典,以便快速查找。这应该会给你一个显著的速度提升:
public ActionResult Index()
{
private ClientRepository _cr = new ClientRepository();
var _retailclients = _cr.GetRetailClientNames().ToDictionary(c => c.id);
var _corporateclients = _cr.GetCorporateClientNames().ToDictionary(c => c.id);
var _visits = _db.GetAllServiceVisits();
var _temp = _visits.Select(o => new ServiceVisitViewModel
{
service_visit = o,
client = (o.client_type ? _corporateclients[o.client_id].name : _retailclients[o.client_id].name)
}).ToArray();
return View(_temp);
}