Proper way to construct linq queries to achieve fastest performance?

c#, entity-framework, generic-list, linq

Solution

Here's why each one is performing as it is and why you're seeing what you're seeing:

Test 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` constructed only on the post-processed list.

Test 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();

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).

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` constructed only on the post-processed list.

Test 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();

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` constructed only on the post-processed list.

Test 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` constructed on the pre-processed list.

Test 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` constructed on the pre-processed list.

Test 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` constructed on the pre-processed list.

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` constructed on the pre-processed list.

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).

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.

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.

Problem

Original source