使用LINQ。选择（）丢在新型太慢？ [英] Using LINQ .Select() to cast into new type is TOO slow?
问题描述
目前的项目，打破头在这个问题：
客户端库：
公共类ClientRepository
{
//会员
私人masterDataContext _db;
//构造
公共ClientRepository（）
{
_db =新masterDataContext（）;
}
公开的IEnumerable<&CLIENTNAME GT; GetCorporateClientNames（）
{
返回_db.corporate_client_tbs.Select（O =>新建CLIENTNAME {ID = o.id，名称= o.company_name}）。AsEnumerable（）;
}
公开的IEnumerable<&CLIENTNAME GT; GetRetailClientNames（）
{
返回_db.retail_client_tbs.Select（O =>新建CLIENTNAME {ID = o.id，名称= o.name}）。AsEnumerable（）;
}
//定义返回类型
公共类CLIENTNAME
{
公众诠释ID {搞定;组; }
公共字符串名称{;组; }
}
}
现在的控制器我有以下内容：
公众的ActionResult指数（）
{
VAR _visits = _db.GetAllServiceVisits（）;
返回查看（_visits）;
}
这需要大约4秒当前存在的200奇数行加载视图
我要一个属性客户添加到包含客户端的名称访问模式。
中的客户端的名称将来自两个不同的表是从类型CLIENTNAME两个阵列中的一个取出之一。
这是第一次方法，它使用LINQ：
公众的ActionResult指数（）
{
私人ClientRepository _cr =新ClientRepository （）;
变种_retailclients = _cr.GetRetailClientNames（）ToArray的（）;
变种_corporateclients = _cr.GetCorporateClientNames（）ToArray的（）;
VAR _visits = _db.GetAllServiceVisits（）;
VAR _temp = _visits.Select（O =>新ServiceVisitViewModel
{
service_visit = O，
客户端=（o.client_type _corporateclients.Where（P => p.id == o.client_id）。首先（）名称：_retailclients.Where（p => p.id == o.client_id）。首先（）名）
}）。 ToArray的（）;
返回查看（_temp）;
}
这是第二种方法，使用普通的'醇C＃：
公众的ActionResult指数（）
{
私人ClientRepository _cr =新ClientRepository（）;
变种_retailclients = _cr.GetRetailClientNames（）ToArray的（）;
变种_corporateclients = _cr.GetCorporateClientNames（）ToArray的（）;
VAR _visits = _db.GetAllServiceVisits（）;
名单，LT; ServiceVisitViewModel> _temp =新的List< ServiceVisitViewModel>（）;
的foreach（在_visits service_visit_tb V）
{
_temp.Add（新ServiceVisitViewModel {service_visit = V，客户端=（v.client_type _corporateclients.Where（P => p.id = = v.client_id）。首先（）名称：_retailclients.Where（p => p.id == v.client_id）。首先（）名）}）;
}
返回查看（_temp）;
}
第二种方法是大约8 - 10倍的速度根据我的测试<。 / p>
我可以看到的唯一区别是。选择的语句。
有人可以告诉我，如果我做一些错误的第一种方法或替代，为什么第一个方法是让@＃$ ING慢
编辑：！？！
中的_db.GetAllServiceVisits（）的定义如下：
公开的IEnumerable< service_visit_tb> GetAllServiceVisits（）
{
VAR _visits = _db.service_visit_tbs;
返回_visits.AsEnumerable（）;
}
结束修改
第二个编辑：我已删除的日志中的每个条目此行
：
- 上下文：sqlProvider的（SQL2008）型号：AttributedMetaModel体形：4.0.30319.1
上下文日志如下：
//这个查询获取一个类型（对应于所有客户端_cr.GetRetailClientNames（））
选择[T0]。[ID]，[T0]。[名]
从[天才]。[retail_client_tb] AS [T0]
//这个查询获取两个类型（对应于_cr.GetCorporateClientNames（））
选择[T0]的所有客户端。[ID]，[T0]。[COMPANY_NAME] AS [名]
FROM [天才]。[corporate_client_tb] AS [T0]
//这是去抓取所有访问
选择[T0]。[ID]主查询（装载大约250条记录） [T0]。[client_type]，[T0]。[CLIENT_ID]，[T0]。[计算机类型]，[T0]。[设备ID]，[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]
从[天才[service_visit_tb] AS [T0]
//而接下来的查询不被手动我叫。我认为他们正在
//调用的时候，因为我的的Razor视图编译打电话链接表的名称值这样：
// @ item.service_visit.engineer_tb.name
选择[T0] [ID]，[T0] [类型]
FROM [天才] [visit_type_tb] AS [T0]
WHERE [T0] [ID] = @ P0
- @ P0：。输入INT（尺寸= -1; PREC = 0;规模= 0）[8]
选择[T0]。[ID]，[T0]。[状态]
从[天才]。[visit_status_tb] AS [T0] $ B $ 。b，其中[T0] [ID] = @ P0
- @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[1]
选择[ T0] [ID]，[T0] [名]
从[天才] [engineer_tb] AS [T0]
WHERE [T0] [ID] = @ P0
。。 - - @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[3]
选择[T0] [ID]，[T0] [类型] $ b $。 b从[天才] [visit_type_tb] AS [T0]
WHERE [T0] [ID] = @ P0
- @ P0：。输入INT（尺寸= -1; PREC = 0;规模= 0）[11]
选择[T0]。[ID]，[T0]。[名]
从[天才]。[engineer_tb] AS [T0]
。WHERE [T0] [ID] = @ P0
- @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[2]
选择[T0 ] [ID]，[T0] [类型]
从[天才] [visit_type_tb] AS [T0]
WHERE [T0] [ID] = @ P0
。 - @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[7]
选择[T0] [ID]，[T0] [类型]
。 FROM [天才] [visit_type_tb] AS [T0]
WHERE [T0] [ID] = @ P0
- @ P0：。输入INT（尺寸= -1; PREC = 0;规模= 0）[2]
选择[T0]。[ID]，[T0]。[类型]
从[天才]。[visit_type_tb] AS [T0] $ B $ 。b，其中[T0] [ID] = @ P0
- @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[6]
选择[ T0] [ID]，[T0] [类型]
从[天才] [visit_type_tb] AS [T0]
WHERE [T0] [ID] = @ P0
。。 - - @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[3]
选择[T0] [ID]，[T0] [名] $ b $。 b从[天才] [engineer_tb] AS [T0]
WHERE [T0] [ID] = @ P0
- @ P0：。输入INT（尺寸= -1; PREC = 0;规模= 0）[5]
选择[T0]。[ID]，[T0]。[名]
从[天才]。[engineer_tb] AS [T0]
。WHERE [T0] [ID] = @ P0
- @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[4]
选择[T0 ] [ID]，[T0] [状态]
从[天才] [visit_status_tb] AS [T0]
WHERE [T0] [ID] = @ P0
。 - @ P0：输入INT（尺寸= -1; PREC = 0;规模= 0）[8]
选择[T0] [ID]，[T0] [状态]
。 FROM [天才] [visit_status_tb] AS [T0]
WHERE [T0] [ID] = @ P0
- @ P0：。输入INT（尺寸= -1; PREC = 0;规模= 0）[2]
补遗问题：有没有更好的方法来拉动这个数据？我总是假设（没有检查）的外键基于数据访问LINQ方面给了我是尽善尽美，但看到这些额外的查询，我不那么肯定了！
将（在这里它的一个长周末在孟买，但我们的权利，通过工作）发布的速度执行的第二部分在今天晚些时候
结束修改
第三次修改
（我正在考虑从网络服务器到客户端的响应，因为所有的计算/读取/结合。在/ etc等应占）
结果
方法一：6.85秒（3表呼叫，然后不要使用C＃铸造成视图模型）
公开的IEnumerable< service_visit_tb> GetAllServiceVisits（）
{
VAR _visits = _db.service_visit_tbs;
_db.Log =新DebuggerWriter（）;
返回_visits.AsEnumerable（）;
}
公众的ActionResult指数（）
{
VAR _retailclients = _cr.GetRetailClientNames（）ToArray的（）;
变种_corporateclients = _cr.GetCorporateClientNames（）ToArray的（）;
VAR _visits = _db.GetAllServiceVisits（）;
名单，LT; ServiceVisitViewModel> _temp =新的List< ServiceVisitViewModel>（）;
的foreach（在_visits service_visit_tb V）
{
_temp.Add（新ServiceVisitViewModel {service_visit = V，客户端=（v.client_type _corporateclients.Where（P => p.id = = v.client_id）。首先（）名称：_retailclients.Where（p => p.id == v.client_id）。首先（）名）}）;
//}
返回查看（_temp）;
}
方法二：8.59秒。（从3个呼叫表，然后做铸造成使用LINQ视图模型）
公开的IEnumerable< service_visit_tb> GetAllServiceVisits（）
{
VAR _visits = _db.service_visit_tbs;
_db.Log =新DebuggerWriter（）;
返回_visits.AsEnumerable（）;
}
公众的ActionResult指数（）
{
VAR _retailclients = _cr.GetRetailClientNames（）ToArray的（）;
变种_corporateclients = _cr.GetCorporateClientNames（）ToArray的（）;
VAR _visits = _db.GetAllServiceVisits（）;
VAR _temp = _visits.Select（O =>新建ServiceVisitViewModel
{
service_visit = O，
客户端=（o.client_type _corporateclients.Where（P => 2 P 。.ID == o.client_id）。首先（）名称：_retailclients.Where（p => p.id == o.client_id）。首先（）名）
}）;
返回查看（_temp）;
}
方法三：5.76秒（在一切单LINQ查询 - 在数据库中执行）
公开的IEnumerable< ServiceVisitViewModel> GetAllServiceVisitsNew（）
{
VAR _visits = _db.service_visit_tbs.Select（O =>新建ServiceVisitViewModel
{
service_visit = O，
客户端=（o.client_type ？_db.corporate_client_tbs.Where（C => c.id == o.client_id）。首先（）COMPANY_NAME：_db.retail_client_tbs.Where（C => c.id == o.client_id）。首先（） 。名称）
}）;
_db.Log =新DebuggerWriter（）;
返回_visits;
}
公众的ActionResult指数（）
{
VAR _visits = _db.GetAllServiceVisitsNew（）;
返回查看（_visits（））;
}
想这决定它。感谢大家的帮助。我标志着作为乔恩正确的答案，因为他在做数据库方面的一切方法是什么带回家的咸肉。
非常感谢任何人，大家谁不嫌麻烦回复。
结束修改
< DIV CLASS =h2_lin>解决方案
的选择语句做了一个巨大的区别就在这里 - 因为它改变了什么在数据库中完成的。 （我假设 _db.GetAllServiceVisits（）返回的IQueryable< T> ）
您所获取的所有零售和企业客户的到内存中，因为你使用的已的ToArray 。你的选择通话将（我怀疑），那么可以将所有这些数据的回的到数据库中，使选择和其中，可以在那里进行。我想，如果你在SQL事件探查一下，这将是一个非常奇怪的查询。
如果你强迫的所有的做客户端，应当bassically相同的后一种方法。你可以很轻松地用：
VAR _visits = _db.GetAllServiceVisits（）了ToList（）;
...然而，这意味着你拉动的所有三从数据库表每次打这个页面。这听起来并不像一个很好的计划给我。
不过，它会更好，如果你能在数据库中的没有的尽取所有的零售和corportate客户到内存第一。
这的可以的是简单，只要改变你的资料库方法，以返回 IQueryable的< T> 而不是的IEnumerable< T> ，除去调用 AsEnumerable
Current project, broke head over this problem:
Client Repository:
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; }
}
}
Now in the Controller I have the following:
public ActionResult Index()
{
var _visits = _db.GetAllServiceVisits();
return View(_visits);
}
Which takes approximately 4 seconds to load the view with the 200 odd rows currently present.
I want to add a property "client" to the visit model which contains the name of the client. The name of the client will come from one of two different tables which is fetched from one of two arrays of type "ClientName".
This is the first approach, which used 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);
}
This is the second approach, using plain 'ol 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);
}
The second approach is about 8 - 10 times faster based on my tests.
The only difference I can see is the .Select statement.
Can someone please tell me if I have done something wrong in the first approach or in the alternative, why the first approach is so !@#$ing slow?!
Edit: The _db.GetAllServiceVisits() definition is as follows:
public IEnumerable<service_visit_tb> GetAllServiceVisits()
{
var _visits = _db.service_visit_tbs;
return _visits.AsEnumerable();
}
End Edit
Second Edit: I have removed this line from every entry in the log:
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
The Context Log is as follows:
// 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]
Addendum to the question: Is there a better way to pull this data? I always presumed (never checked) that the foreign key based data access that LINQ context gave me was as good as it gets, but seeing these additional queries, I'm not so sure anymore!
Will post speeds for the second part of execution later today (its a long weekend here in Mumbai but we are working right through)
End Edit
Third Edit
(I am considering response from webserver to client, since all computation / fetching / binding / etc etc should be accounted for.)
Method One : 6.85 seconds (Call from 3 tables and then do casting into View-Model using 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);
}
Method Two : 8.59 seconds (Call from 3 tables and then do casting into View-Model using 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);
}
Method Three : 5.76 seconds (Everything in a single LINQ query - executed on Database)
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());
}
Guess that decides it. Thanks to everyone for the help. I am marking Jon as correct answer since his approach of doing everything on database side is what brought home the bacon. Many thanks to anyone and everyone who took the trouble to reply.
End Edit
The Select statement makes a huge difference here - because it's changing what's being done at the database. (I'm assuming _db.GetAllServiceVisits() returns an IQueryable<T>.)
You're fetching all the retail and corporate clients into memory already because you're using ToArray. Your Select call will (I suspect) then be sending all that data back to the database so that the Select and Where can be performed there. I suspect if you look in SQL profiler, it'll be a pretty odd query.
If you force everything to be done client-side, it should be bassically the same as your latter approach. You can do that easily, with:
var _visits = _db.GetAllServiceVisits().ToList();
... however, that means you're pulling all of three tables from your database each time you hit this page. That doesn't sound like a good plan to me.
However, it would be better if you could do everything in the database without fetching all the retail and corportate clients into memory first.
That may be as simple as changing your repository methods to return IQueryable<T> instead of IEnumerable<T>, and removing the calls to AsEnumerable.
这篇关于使用LINQ。选择（）丢在新型太慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
