0

I would like to do an left outer join to get some data. However, despite some index created on useful column, I get really poor performance using Entity Framework and C# LINQ join. I tried an another way using multiple and consecutive FROM with a WHERE clause, and saw a huge boost in performance.

I wanted to know why SQL Server does not pick the faster type of join? Did I do something wrong with my LINQ query join to get so bad performance?

Here the first version that take more than 3s:

from calendarEvent in calendarEvents
join participants in ctx.CalendarEventParticipants on calendarEvent.Id equals participants.CalendarEventId into calendarEventWithParticipants
from subCalendarEventWithParticipant in calendarEventWithParticipants.DefaultIfEmpty()
select new { calendarEvent , subCalendarEventWithParticipant };

and the version that takes 200ms:

from calendarEvent in calendarEvents
from participants in ctx.CalendarEventParticipants.Where(cep => cep.CalendarEventId == calendarEvent.Id).DefaultIfEmpty()
select new { calendarEvent , participants };

I have an useful index on CalendarEventParticipants.calendarEventId.

My main concern is that the query with the join should be in theory much faster than the second one with a where. Using SSMS, I can see that the query do not JOIN before doing a NESTED LOOPS (Left Outer join) that cost me so much on this query.

Thanks

SqlBeginner2018
  • 1
  • 1
  • 2
    Please try to imagine what it takes for readers to answer a question. You don't tell which SQL is generated so we don't know what you mean by "the faster type of join". Also, it's relevant to know which ORM (+ version!) you work with. And usually class definitions and mappings are also relevant, because required and non-required associations behave differently in joins. – Gert Arnold Aug 31 '18 at 11:42
  • What is a `hashjoin`? – NetMage Aug 31 '18 at 15:59
  • @GertArnold : Hello ! this is C# code so no SQL displayed here. I can indeed dump the query sent to SQL SERVER but it's really difficult to understand. We use Entity Framework. – SqlBeginner2018 Sep 03 '18 at 02:56
  • 1
    @NetMage: Hello ! Please refer to https://stackoverflow.com/a/3038742/10299666 – SqlBeginner2018 Sep 03 '18 at 02:59
  • 1
    You don't seem to be aware that the C# code is nothing but a vehicle to generating SQL. Whatever is written about LINQ in C#, that is, LINQ-to-objects, non of it applies here. If anything is to be optimized, it's the SQL code. That's why the SQL and the mappings are at least as relevant as LINQ queries. – Gert Arnold Sep 03 '18 at 07:20
  • That applies to LINQ to Objects and as stated is for `Enumerable.Join` but you are using LINQ to SQL and `Queryable.Join` which involves SQL Server and has nothing to do with hashing. – NetMage Sep 04 '18 at 23:00
  • We won't have a problem understanding the SQL generated by the query. Post the SQL for both queries. – NetMage Sep 04 '18 at 23:01

1 Answers1

0

First of all your query is not clear. The way you are comparing CalendarEventId with calendarEvent.Id

from calendarEvent in calendarEvents

from participants in ctx.CalendarEventParticipants.Where(cep => cep.CalendarEventId == calendarEvent.Id).DefaultIfEmpty()
select new { calendarEvents, participants };

Second, the above query looks like a nested query which is always a performance eater. Another query which is taking less time is using join, which gives you joint targeted set to work on. Performance depends on indexes, size and other factors. But if the table is big then nested query is not the right choice. Instead use joins.

from calendarEvent in calendarEvents
join participants in ctx.CalendarEventParticipants on calendarEvent.Id equals participants.CalendarEventId into calendarEventWithParticipants

from subCalendarEventWithParticipant in calendarEventWithParticipants.DefaultIfEmpty()
select new { calendarEvents, subCalendarEventWithParticipant };
ManishM
  • 583
  • 5
  • 7
  • My main issue is that the query using join took 3 seconds to run compare to the one with the where clause. I updated my post to add more details. – SqlBeginner2018 Sep 03 '18 at 03:02