LINQ to objects vs for each - difference in execution timings

Question

Hi,

I am having this For each loop

foreach (Account acct in acctTest)
            {
                if (acct.AccountId == acctId)
                {
                    foreach (Customer cust in acct.CustomerColl)
                    {
                        if (cust.CustomerId == custId)
                        {
                            customerName = cust.CustomerName;
                            break;
                        }
                    }
                }
            }

Linq query which does similar stuff (think this can be improved)

customerName = (from acct in acctTest
                           where acct.AccountId == acctId
                           from cust in acct.CustomerColl
                           where cust.CustomerId == custId
                            select cust.CustomerName).ToString() ;

I execute the above two in a loop of 1000 times for 5 times, get the execution timings as below.

Elapsed time for For Each ::: 7377
Elapsed time for Linq2 ::: 15653
Elapsed time for For Each ::: 1576
Elapsed time for Linq2 ::: 1718
Elapsed time for For Each ::: 1569
Elapsed time for Linq2 ::: 1726
Elapsed time for For Each ::: 1569
Elapsed time for Linq2 ::: 5583
Elapsed time for For Each ::: 1570
Elapsed time for Linq2 ::: 1506

why is there a difference and inconsistency in execution timings? Also , is there a way the LINQ query can be rewritten for better performance?

Answer 1

The fourth execution looks like it hit garbage collection on the Linq side.

Other than that, LINQ doesn't know you're only trying to get a single instance of the customer name. It doesn't know the relationship that CustomId or AccountIds are unique, which is what your code is assuming. In short, the code isn't analogous :)

Also, in the first example, you might want to check in the outer loop if custerName != NULL so you can stop short ;)

Answer 2

Have you identified your usage of LINQ as a bottleneck in your application? If not, it's unlikely you will and chasing microseconds between traditional foreach and lambdas is burning your valuable time for no real benefit. You will probably spend more time on one database call or I/O operation than you do in all the LINQ expressions combined. Worry about those instead.

Answer 3

(from acct in acctTest
where acct.AccountId == acctID
select acct.CustomerColl)
  .Where(c => c.CustomerId == custId)
  .Select(cn => cn.CustomerName)
  .FirstOrDefault()

Answer 4

An obvious thing, looking at the code above is - In case of foreach, you are stopping the inner loop execution when a match is found for the customerID.

Whereas, it doesn't seem to be the case with LINQ.

Is the output of foreach and LINQ query same?
How about using a JOIN and using FirstOrDefault, as someone has suggested here?