Why is LINQ JOIN so much faster than linking with WHERE?

by

  1. Your first approach (SQL query in the DB) is quite efficient because the DB knows how to perform a join. But it doesn’t really make sense to compare it with the other approaches, since they work directly in memory (Linq to DataSet)

  2. The query with multiple tables and a Where condition actually performs a cartesian product of all the tables, then filters the rows that satisfy the condition. This means the Where condition is evaluated for each combination of rows (n1 * n2 * n3 * n4)

  3. The Join operator takes the rows from the first tables, then takes only the rows with a matching key from the second table, then only the rows with a matching key from the third table, and so on. This is much more efficient, because it doesn’t need to perform as many operations

More Related Contents:

  1. Which method performs better: .Any() vs .Count() > 0?
  2. Is it better to call ToList() or ToArray() in LINQ queries?
  3. .NET LINQ query syntax vs method chain
  4. Why use LINQ Join on a simple one-many relationship?
  5. LINQ, can’t join to string
  6. What is the difference between IQueryable and IEnumerable?
  7. HashSet vs. List performance
  8. LINQ to SQL Left Outer Join
  9. Case insensitive string compare in LINQ-to-SQL
  10. “Or” equivalent in Linq Where() lambda expression
  11. How do I get the MAX row with a GROUP BY in LINQ query?
  12. LINQ: Not Any vs All Don’t
  13. LINQ Single vs First
  14. HashSet versus Dictionary w.r.t searching time to find if an item exists
  15. Why does a Linq Cast operation fail when I have an implicit cast defined?
  16. Why is LINQ .Where(predicate).First() faster than .First(predicate)?
  17. Ignore namespaces in LINQ to XML
  18. LINQ to Entities does not recognize the method Int32 get_Item(Int32)
  19. Can you get a Func (or similar) from a MethodInfo object?
  20. Does LINQ work with IEnumerable?
  21. How to use “contains” or “like” in a dynamic linq query?
  22. System.IO.FileSystemWatcher to monitor a network-server folder – Performance considerations
  23. Optimum file buffer read size?
  24. How do I improve ASP.NET MVC application performance?
  25. Which is faster: clear collection or instantiate new
  26. IIS: web applications warmup
  27. Linq to Objects: does GroupBy preserve order of elements?
  28. The model backing the ‘ApplicationDbContext’ context has changed since the database was created
  29. Performance: XDocument versus XmlDocument
  30. WPF slow to start on x64 in .NET Framework 4.0

Leave a Comment