How to know the number of times a subquery executes?

sun21170

I have the following LINQ code, which runs as expected.

However, I need to know the number of times the subquery colors.Max (c2 => c2.Length) runs. Is it possible to know this from LINQPad and if yes, then how would I look it up in the execution plan or somewhere else? I'm using Premium LINQPad 4.59.00.

I looked up the IL pad after query execution, but cannot determine how many times the subquery is executed.

string[] colors = { "green", "brown", "blue", "red" };

var query =
  from c in colors
  where c.Length == colors.Max (c2 => c2.Length)
  select c;

query.Dump();

The IL tab shows the following output

IL_0000:  newobj      UserQuery+<>c__DisplayClass2..ctor
IL_0005:  stloc.1     // CS$<>8__locals3
IL_0006:  nop         
IL_0007:  ldloc.1     // CS$<>8__locals3
IL_0008:  ldc.i4.4    
IL_0009:  newarr      System.String
IL_000E:  stloc.2     // CS$0$0000
IL_000F:  ldloc.2     // CS$0$0000
IL_0010:  ldc.i4.0    
IL_0011:  ldstr       "green"
IL_0016:  stelem.ref  
IL_0017:  ldloc.2     // CS$0$0000
IL_0018:  ldc.i4.1    
IL_0019:  ldstr       "brown"
IL_001E:  stelem.ref  
IL_001F:  ldloc.2     // CS$0$0000
IL_0020:  ldc.i4.2    
IL_0021:  ldstr       "blue"
IL_0026:  stelem.ref  
IL_0027:  ldloc.2     // CS$0$0000
IL_0028:  ldc.i4.3    
IL_0029:  ldstr       "red"
IL_002E:  stelem.ref  
IL_002F:  ldloc.2     // CS$0$0000
IL_0030:  stfld       UserQuery+<>c__DisplayClass2.colors
IL_0035:  ldloc.1     // CS$<>8__locals3
IL_0036:  ldfld       UserQuery+<>c__DisplayClass2.colors
IL_003B:  ldloc.1     // CS$<>8__locals3
IL_003C:  ldftn       UserQuery+<>c__DisplayClass2.<RunUserAuthoredQuery>b__0
IL_0042:  newobj      System.Func<System.String,System.Boolean>..ctor
IL_0047:  call        System.Linq.Enumerable.Where
IL_004C:  stloc.0     // query
IL_004D:  ldloc.0     // query
IL_004E:  call        LINQPad.Extensions.Dump
IL_0053:  pop         
IL_0054:  nop         
IL_0055:  ret         

<>c__DisplayClass2.<RunUserAuthoredQuery>b__0:
IL_0000:  ldarg.1     
IL_0001:  callvirt    System.String.get_Length
IL_0006:  ldarg.0     
IL_0007:  ldfld       UserQuery+<>c__DisplayClass2.colors
IL_000C:  ldsfld      UserQuery+<>c__DisplayClass2.CS$<>9__CachedAnonymousMethodDelegate4
IL_0011:  brtrue.s    IL_0026
IL_0013:  ldnull      
IL_0014:  ldftn       UserQuery+<>c__DisplayClass2.<RunUserAuthoredQuery>b__1
IL_001A:  newobj      System.Func<System.String,System.Int32>..ctor
IL_001F:  stsfld      UserQuery+<>c__DisplayClass2.CS$<>9__CachedAnonymousMethodDelegate4
IL_0024:  br.s        IL_0026
IL_0026:  ldsfld      UserQuery+<>c__DisplayClass2.CS$<>9__CachedAnonymousMethodDelegate4
IL_002B:  call        System.Linq.Enumerable.Max
IL_0030:  ceq         
IL_0032:  stloc.0     // CS$1$0000
IL_0033:  br.s        IL_0035
IL_0035:  ldloc.0     // CS$1$0000
IL_0036:  ret         

<>c__DisplayClass2.<RunUserAuthoredQuery>b__1:
IL_0000:  ldarg.0     
IL_0001:  callvirt    System.String.get_Length
IL_0006:  stloc.0     // CS$1$0000
IL_0007:  br.s        IL_0009
IL_0009:  ldloc.0     // CS$1$0000
IL_000A:  ret         

<>c__DisplayClass2..ctor:
IL_0000:  ldarg.0     
IL_0001:  call        System.Object..ctor
IL_0006:  ret        

sun21170

Even when debugging the above code, I could not determine the number of times subquery executes.

sun21170

I found a certain way to determine the number of executions of the subquery by using debug in a special manner.

I went to the main menu of Debug > Step All Threads Into and then kept pressing the F11 key. This way it stepped through each execution of the LINQ query and I could easily see that the subquery was getting executed once for each row in outer query i.e. for each color.

I didn't have to set any break points for doing this.

JoeAlbahari

Here's another way:

string[] colors = { "green", "brown", "blue", "red" };

var query =
  from c in colors
  where c.Length == colors.Max(c2 => c2.Length).Dump ("subquery!")
  select c;

query.Dump();

sgmoore

If you want to extend this to count various methods you can define an extension method.

For example, if you want to count the number of comparisons as well as the number of times max and length are executed, you could do something like.

void Main()
{
    string[] colors = { "green", "brown", "blue", "red" };

    var query =
      from c in colors
      where (c.Length.Count("Length") == colors.Max(c2 => c2.Length.Count("Length")).Count("Max")).Count("Comparisions")
      select new { Color = c, Length = c.Length.Count("Length")} ;

    query.Dump();
    Counter.ShowResults(); 
}

public static class Counter
{
    public static System.Collections.Concurrent.ConcurrentDictionary <string, int> Results = new();

    public static T Count<T>(this T input, string key)
    {
        Results.AddOrUpdate(key, 1, (key, count) => count + 1);  
        return input;
    }

    public static void ShowResults()
    {
        Counter.Results.OrderByDescending(a=>a.Value).Dump();
    }
}

gives

It does make your code a little messy, but works.

sun21170

@sgmoore said:
If you want to extend this to count various methods you can define an extension method.

For example, if you want to count the number of comparisons as well as the number of times max and length are executed, you could do something like.

void Main()
{
  string[] colors = { "green", "brown", "blue", "red" };
 
  var query =
    from c in colors
    where (c.Length.Count("Length") == colors.Max(c2 => c2.Length.Count("Length")).Count("Max")).Count("Comparisions")
    select new { Color = c, Length = c.Length.Count("Length")} ;
  
  query.Dump();
  Counter.ShowResults(); 
}

public static class Counter
{
  public static System.Collections.Concurrent.ConcurrentDictionary <string, int> Results = new();

  public static T Count<T>(this T input, string key)
  {
      Results.AddOrUpdate(key, 1, (key, count) => count + 1);  
      return input;
  }
  
  public static void ShowResults()
  {
      Counter.Results.OrderByDescending(a=>a.Value).Dump();
  }
}

gives

It does make your code a little messy, but works.

Will the Counter class work for all LINQ queries or only for this specific one? I am still trying to understand what exactly is being counted and the data structure of dumped results.

sun21170

@JoeAlbahari said:
Here's another way:

string[] colors = { "green", "brown", "blue", "red" };

var query =
  from c in colors
  where c.Length == colors.Max(c2 => c2.Length).Dump ("subquery!")
  select c;

query.Dump();

I tried applying a similar approach to LINQ code below based on Northwind database connection i.e. calling Dump method on OrderDetails.Max(o1=>o1.UnitPrice), but it gave an exception at the last line of code that said NotSupportedException: Method 'System.Decimal Dump[Decimal](System.Decimal, System.String)' has no supported translation to SQL..

var query = 
          from o in OrderDetails
          where o.UnitPrice == OrderDetails.Max(o1=>o1.UnitPrice).Dump("subquery!")
          select o;

query.Dump();

sgmoore

@sun21170 said:

Will the Counter class work for all LINQ queries or only for this specific one? I am still trying to understand what exactly is being counted and the data structure of dumped results.

It is simply counting the number of times the count extension method is being called, so if your query or code does not include any calls to that method, it obviously will not do anything.

JoeAlbahari

In the case of the northwind database, the query gets translated to SQL and then executed on the database server. It's not possible then to answer the question "How many times does the subquery execute", because that's an implementation detail of SQL Server.

sun21170

@JoeAlbahari said:
In the case of the northwind database, the query gets translated to SQL and then executed on the database server. It's not possible then to answer the question "How many times does the subquery execute", because that's an implementation detail of SQL Server.

I get it. For this, I should be looking at the execution plan in SSMS for the SQL query generated by the LINQ code.

Specifically, I need to be looking at the quantity Number of executions for the aggregate node in the plan diagram ( hover over the node to see this quantity in the resulting tooltip popup).