正确的方法来构建LINQ查询，实现最快的性能？ [英] Proper way to construct linq queries to achieve fastest performance?
问题描述
类似的问题已经被问这里，但没有适合我的需要。结果
我做了测试案例，看看哪个更快。但我觉得我的LINQ代码仍然缓慢。怎样构建LINQ代码更快的性能？
Similar question have been asked here but none fits to my need.
I made test cases to see which is faster. But I feel my linq code is still slow. How do I construct the linq code for faster performance?
其他采用双 .Tolist（）可导致系统反应慢，当我测试了一下，表明其说。比任何其他的测试速度更快
Others said using double .Tolist() can cause slower operation, when I test it, it shows that it is faster than any other tests.
测试：
Preparation
---------------------------------------------------------------
return Properties of UserInfo(userinf, true){
UserID = userinf.UserID;
FirstName = userinf.user.FirstName;
MiddleName = userinf.user.MiddleName;
LastName = userinf.user.LastName;
LoginID = userinf.user.LoginID;
Birthday = userinf.Birthday;
}
skip = 0;
take = 100;
total table records = 304;
Linq to Entity Framework
提琴手：v2.4.0.0
Fiddler: v2.4.0.0
https://127.0.0.1/..../RetrieveUserInfo?skip=0&take=100
{
"client":{
"SessionID":"5433ab64-7e0d-444f-b886-a901ea9a0601"
},
"session":{
"SessionID":"35b75daa-25ad-45a4-9f99-0e69ec3b66a4"
}
}
//Test 1
//1) 00:00:15.3068755 -- Attempt1
//2) 00:00:13.8207905 -- Attempt2
//3) 00:00:16.2489294 -- Attempt3
var list = (from usr in dbase.userinfoes
select usr).OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
userlist = (from i in list
select new UserInfo(i, true)).ToList();
///Test 2
//1) 00:00:15.3908803
//2) 00:00:14.8818512
//3) 00:00:19.4761140
var list = (from usr in dbase.userinfoes.AsEnumerable().OrderBy(i => i.UserID).Skip(skip).Take(take).ToList()
select new UserInfo(usr, true)).ToList();
//Test 3
//1) 00:00:30.1937270
//2) 00:00:24.1003784
//3) 00:00:28.8806519
var list = dbase.userinfoes.OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
userlist = (from i in list select new UserInfo(i, true)).ToList();
//Test 4
//1) 00:00:57.2652754
//2) 00:00:54.4051118
//3) 00:00:55.3251644
var list = (from usr in dbase.userinfoes
select usr).ToList();
userlist = (from i in list
select new UserInfo(i, true)).OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
//Test 5
//1) 00:01:06.8378229
//2) 00:01:01.2845053
//3) 00:00:55.0721499
var list = from usr in dbase.userinfoes
select usr;
userlist = (from i in list.AsEnumerable()
select new UserInfo(i, true)).OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
// Test 6
// VERY LONG. It tooks all records first and construct UserInfo one by one before doing the skip and take
var list = (from usr in dbase.userinfoes.AsEnumerable()
select new UserInfo(usr, true)).OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
//Test 7
// VERY LONG. It tooks all records first and construct UserInfo one by one before doing the skip and take
var list = from usr in dbase.userinfoes.AsEnumerable()
select new UserInfo(usr);
有关更快的搜索合适的代码。由于 casperOne 为指出的顺序，跳过，并采取在服务器上的所有执行是来得更快。
Proper code for faster search. Thanks to casperOne for pointing out that the ordering, skip and take are all performed on the server is more faster.
下面是最终的代码：
var list = (from usr in dbase.userinfoes
.OrderBy(i => i.UserID)
.Skip(skip)
.Take(take)
.AsEnumerable()
select new UserInfo(usr, true)).ToList();
1) 00:00:10.9210513
2) 00:00:10.8270973
3) 00:00:10.8250151
感谢理查德·尼尔Ilagan 的最终代码。
推荐答案
这也是为什么每一个正在执行，因为它是为什么你看到你所看到的：
Here's why each one is performing as it is and why you're seeing what you're seeing:
测试1：
///Test 2
//1) 00:00:15.3068755 -- Attempt1
//2) 00:00:13.8207905 -- Attempt2
//3) 00:00:16.2489294 -- Attempt3
var list = (from usr in dbase.userinfoes
select usr).OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
userlist = (from i in list
select new UserInfo(i, true)).ToList();
这绝对是最快的。这是最快的，因为顺序，跳过，并采取在服务器上的所有执行。因为你可能有索引，该服务器是健壮的（强大），等它可以比如果您在客户端实现了整个集，然后进行的操作有更快的处理这些操作。
This is absolutely the fastest. It's the fastest because the ordering, skip and take are all performed on the server. Because you probably have indexes, the server is beefy (powerful), etc. it can handle these operations much faster than if you materialized the whole set on the client and then performed the operations there.
的UserInfo 只有经过后处理的名单上构建
UserInfo constructed only on the post-processed list.
测试2：
///Test 2
//1) 00:00:15.3908803
//2) 00:00:14.8818512
//3) 00:00:19.4761140
var list = (
from usr in dbase.userinfoes.AsEnumerable().
OrderBy(i => i.UserID).Skip(skip).Take(take).ToList()
select new UserInfo(usr, true)
).ToList();
这的应的影响性能相同的测试7;调用 AsEnumerable 迫使所有后续操作在内存中进行的（与调用的 排序依据 将需要你兑现所有实例他们定购之前）。
This should have a performance impact the same as test 7; the call to AsEnumerable is forcing all subsequent operations to be performed in memory (and the call to OrderBy is going to require you to materialize all of the instances before they're ordered).
这是一个有点如果一个异常。我很好奇，看看有什么发送到服务器的SQL，则（假设你使用SQL Server或者一些基于SQL的后端），以确保它是选择所有的记录了。
This is a bit if an anomaly. I'd be curious to see what the SQL sent to the server is (assuming you're using SQL server or some SQL-based back end), to make sure that it's selecting all the records back.
的UserInfo 只有经过后处理的名单上构建的。
UserInfo constructed only on the post-processed list.
测试3：
//Test 3
//1) 00:00:30.1937270
//2) 00:00:24.1003784
//3) 00:00:28.8806519
var list = dbase.userinfoes.OrderBy(i => i.UserID).
Skip(skip).Take(take).ToList();
userlist = (from i in list select new UserInfo(i, true)).ToList();
再次通过，订单跳过，并采取在服务器上正在发生。你两次物化列表（你有两个调用了ToList ），这是对的开销，我可以看到），唯一的解释。
Again, the order by, skip, and take are taking place on the server. You're materializing the list twice (you have two calls to ToList), which is the only explanation for the overhead I can see).
的UserInfo 只有经过后处理的名单上构建的。
UserInfo constructed only on the post-processed list.
测试4
//Test 4
//1) 00:00:57.2652754
//2) 00:00:54.4051118
//3) 00:00:55.3251644
var list = (from usr in dbase.userinfoes
select usr).ToList();
userlist = (from i in list select new UserInfo(i, true)).
OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
您现在正在物化在内存中的整个列表，所以现在有更多的开销。
You're materializing the entire list in memory now, so there's more overhead now.
的UserInfo 预处理的名单上构建的。
UserInfo constructed on the pre-processed list.
测试5：
//Test 5
//1) 00:01:06.8378229
//2) 00:01:01.2845053
//3) 00:00:55.0721499
var list = from usr in dbase.userinfoes
select usr;
userlist = (from i in list.AsEnumerable()
select new UserInfo(i, true)).
OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
同试验二，你在做客户端上的所有操作。
Same as test two, you're doing all the operations on the client side.
的UserInfo 预处理的名单上构建的。
UserInfo constructed on the pre-processed list.
测试6：
// Test 6
// VERY LONG. It tooks all records first and construct
// UserInfo one by one before doing the skip and take
var list = (from usr in dbase.userinfoes.AsEnumerable()
select new UserInfo(usr, true)).
OrderBy(i => i.UserID).Skip(skip).Take(take).ToList();
的UserInfo 预处理的名单上构建
再次做客户端上的所有操作。
Again, doing all the operations on the client side.
// Test 7
// VERY LONG. It tooks all records first and construct
// UserInfo one by one before doing the skip and take
var list = from usr in dbase.userinfoes.AsEnumerable()
select new UserInfo(usr);
再次做客户端上的所有操作。
Again, doing all the operations on the client side.
的UserInfo 预处理的名单上构建的。
UserInfo constructed on the pre-processed list.
有是的有一个的差异我注意到所有这些测试，而这也正是你呼吁的UserInfo 实例的构造。在那里的表现还是不错的地方，你把断构造的实例的UserInfo 尽可能晚的（执行后命令下，跳过操作），而当业绩是坏的，你前面构建的UserInfo 实例，这些操作发生之前（在通常有更多的呼叫到的UserInfo 构造函数）。
There is one difference I notice in all of these tests, and that's where you call the constructor for the UserInfo instance. In the places where the performance is good, you put off constructing the instance of UserInfo as late as possible (after you perform order, take, skip operations), whereas when the performance is bad, you're constructing the UserInfo instance up front, before these operations take place (when there is usually more calls to the UserInfo constructor).
这是说，它似乎是性能问题的可能的谎言的构造函数<$ C $内C>的UserInfo 类，而不是在LINQ。通常情况下，当你让 的IQueryable< T> 提供基础数据源上执行的操作，它的一般的速度比在客户端的内存做这些操作。
That said, it would seem that your performance issues could lie within the constructor for the UserInfo class, and not in LINQ. Generally, when you let the IQueryable<T> provider perform the operations on the underlying data source, it's generally faster than doing those operations in memory on the client.
虽然没有看到构造函数代码，这是不可能的说，但你的号码肯定认为问题出在那里，而不是在LINQ。
Without seeing the constructor code though, it's impossible to tell, but your numbers certainly suggest that the problem lies there, and not in LINQ.
这篇关于正确的方法来构建LINQ查询，实现最快的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
