LINQ - Using where or join - Performance difference ?

Question

Hi

Based on this question: http://stackoverflow.com/questions/3013034/what-is-difference-between-where-and-join-in-linq

My question is following:

Is there a performance difference in the following two statements:

from order in myDB.OrdersSet
    from person in myDB.PersonSet
    from product in myDB.ProductSet
    where order.Persons_Id==person.Id && order.Products_Id==product.Id
    select new { order.Id, person.Name, person.SurName,  product.Model,UrunAdı=product.Name };

and

from order in myDB.OrdersSet
    join person in myDB.PersonSet on order.Persons_Id equals person.Id
    join product in myDB.ProductSet on order.Products_Id equals product.Id
    select new { order.Id, person.Name, person.SurName,  product.Model,UrunAdı=product.Name };

I would always use the second one just because it´s more clear.

My question is now, is the first one slower than the second one? Does it build a cartesic product and filters it afterwards with the where clauses ?

Thank you.

Answer 1

It entirely depends on the provider you're using.

With LINQ to Objects, it will absolutely build the Cartesian product and filter afterwards.

For out-of-process query providers such as LINQ to SQL, it depends on whether it's smart enough to realise that it can translate it into a SQL join. Even if LINQ to SQL doesn't, it's likely that the query engine actually performing the query will do so - you'd have to check with the relevant query plan tool for your database to see what's actually going to happen.

---

Side-note: multiple "from" clauses don't always result in a Cartesian product - the contents of one "from" can depend on the current element of earlier ones, e.g.

from file in files
from line in ReadLines(file)
...

Answer 2

My question is now, is the first one slower than the second one? Does it build a cartesic product and filters it afterwards with the where clauses ?

If the collections are in memory, then yes. There is no query optimizer for LinqToObjects - it simply does what the programmer asks in the order that is asked.

If the collections are in a database (which is suspected due to the myDB variable), then no. The query is translated into sql and sent off to the database where there is a query optimizer. This optimizer will generate an execution plan. Since both queries are asking for the same logical result, it is reasonable to expect the same efficient plan will be generated for both. The only ways to be certain are to

• inspect the execution plans
• or measure the IO (SET STATISTICS IO ON).

---

Is there a performance difference

If you find yourself in a scenario where you have to ask, you should cultivate tools with which to measure and discover the truth for yourself. Measure - not ask.