Slow LINQ query for .ToArray()

Question

I am using following query

foreach (var callDetailsForNode_ReArrange in callDetailsForNodes_ReArrange)
{
    var test = from r1 in dtRowForNode.AsEnumerable()
               join r2 in dtFileRowForNode.AsEnumerable()
               on r1.Field<int>("Lng_Upload_Id") equals r2.Field<int>("Lng_Upload_Id")
               where ((r1.Field<string>("Txt_Called_Number") == callDetailsForNode_ReArrange.caller2.ToString()) || r1.Field<string>("Txt_Calling_Number") == callDetailsForNode_ReArrange.caller2.ToString())
               select r2.Field<string>("Txt_File_Name");

    var d = test.Distinct();
}

Upto here this query run in no time. But as I added

string[] str =d.ToArray();
strFileName = string.Join(",", str);

It takes almost 4-5 seconds to run. What makes it so slow on adding .ToArray() ?

Read the first answer to [this question](http://stackoverflow.com/q/215548/601179) — gdoron, May 10 '13 at 12:01
Nope. It just adds the fact that you only want each element once to the queryable. — George Duckett, May 10 '13 at 12:05
Bear in mind that there might be more statements after Distinct() all of which could get translated into actual SQL (for instance). — Jacek Gorgoń, May 10 '13 at 12:14

Marc Gravell · Accepted Answer · 2013-05-10T12:06:35.310

Upto here this query run in no time.

Up to here, it hasn't actually done anything, except build a deferred-execution model that represents the pending query. It doesn't start iterating until you call MoveNext() on the iterator, i.e. via foreach, in your case via .ToArray().

So: it takes time because it is doing work.

Consider:

static IEnumerable<int> GetData()
{
    Console.WriteLine("a");
    yield return 0;
    Console.WriteLine("b");
    yield return 1;
    Console.WriteLine("c");
    yield return 2;
    Console.WriteLine("d");
}
static void Main()
{
    Console.WriteLine("start");
    var data = GetData();
    Console.WriteLine("got data");
    foreach (var item in data)
        Console.WriteLine(item);
    Console.WriteLine("end");
}

This outputs:

start
got data
a
0
b
1
c
2
d
end

Note how the work doesn't all happen at once - it is both deferred (a comes after got data) and spooling (we don't get a,...,d,0,...2).

Related: this is roughly how Distinct() works, from comments:

public static IEnumerable<T> Distinct<T>(this IEnumerable<T> source) {
    var seen = new HashSet<T>();
    foreach(var item in source) {
        if(seen.Add(item)) yield return item;
    }
}

...

and a new Join operation:

public static string Join(this IEnumerable<string> source, string separator) {
    using(var iter = source.GetEnumerator()) {
        if(!iter.MoveNext()) return "";
        var sb = new StringBuilder(iter.Current);
        while(iter.MoveNext())
            sb.Append(separator).Append(iter.Current);
        return sb.ToString();
    }
}

and use:

string s = d.Join(",");

I didn't know that even the first line in the method `Console.WriteLine("a");` will be printed not before the `foreach`. So i would have thought `a` came before `got data`. Linq is still surprising sometimes. — Tim Schmelter, May 10 '13 at 12:23

Matthew Watson · Answer 2 · 2013-05-10T12:13:17.833

12

Because the query DOES NOTHING until you iterate over it, which .ToArray() does.

One thing to note is that the right-hand-side of a join (in your example, r2 in dtFileRowForNode.AsEnumerable()) will be fully enumerated AS SOON as the query begins to be iterated, even if only the first element of the result is being accessed - but not until then.

So if you did:

d.First()

the r2 in dtFileRowForNode.AsEnumerable() sequence would be fully iterated (and buffered in memory), but only the first element of r1 in dtRowForNode.AsEnumerable() would be evaluated.

For this reason, if one of your sequences in the join is much larger than the other, it's more efficient (memory-wise) to put the big sequence on the left of the join. The entire sequence on the right of the join will be buffered in memory.

(I should point out that only applies to Linq-to-objects. Linq-to-SQL will run those queries in the database, so it handles the buffering.)

edited May 10 '13 at 12:13

answered May 10 '13 at 11:59

Matthew Watson

104,400
10
158
276

^^ what he said. When you define the query, you are doing just that - **defining** the query. You are not running it to get the results. When you call `.ToArray()`, the query executes and all of the required processing occurs (which takes time). Then you have an array of results. – theyetiman May 10 '13 at 12:01
Is there any way i can get the distinct values in comma seprated string efficiently – Rajeev Kumar May 10 '13 at 12:01
Does .Distinct() does not run the query ?? – Rajeev Kumar May 10 '13 at 12:04
@RajeevKumar how about `string s = string.Join(",", d);` ? – Marc Gravell May 10 '13 at 12:04
@RajeevKumar indeed, `.Distinct()` does not run the query; it sets up an iterator block that *will* do it - I'll implement `Distinct()` and add it to my answer for info – Marc Gravell May 10 '13 at 12:05
@MarcGravell I am using .netFramework 3.5 . It giving me compile time error – Rajeev Kumar May 10 '13 at 12:05
@RajeevKumar then write a new `Join` :p (I'll add that too) – Marc Gravell May 10 '13 at 12:07
I have only 3 records in `dtFileRowForNode` – Rajeev Kumar May 10 '13 at 12:10
@RajeevKumar Then you already have the join in the optimal order, at least as far as minimizing the amount of in-memory buffering goes. :) – Matthew Watson May 10 '13 at 12:10

score 2 · Answer 3 · answered May 10 '13 at 12:02

You need to read on deffered evaluation of linq statements. Query is not completed unless you explicitely call for results - like iterating in foreach, calling ToArray, ToList, Sum, First or one of other methods that evaluate query.

So it is your query that takes so much time to complete, not ToArray call.

Slow LINQ query for .ToArray()

3 Answers3

Linked

Related