Dlaczego LINQ JOIN jest o wiele szybszy niż połączenie z WHERE?


99

Niedawno zaktualizowałem do VS 2010 i bawię się z LINQ to Dataset. Mam zestaw danych o silnym typie dla autoryzacji, który znajduje się w HttpCache aplikacji sieci Web ASP.NET.

Chciałem więc wiedzieć, jaki właściwie jest najszybszy sposób sprawdzenia, czy użytkownik jest upoważniony do zrobienia czegoś. Oto mój model danych i kilka innych informacji, jeśli ktoś jest zainteresowany.

Sprawdziłem 3 sposoby:

  1. bezpośrednia baza danych
  2. Zapytanie LINQ z warunkami Where jako „Join” - Składnia
  3. Zapytań LINQ z Dołącz - Składnia

Oto wyniki z 1000 wywołań dla każdej funkcji:

1. iteracja:

  1. 4,2841519 sek.
  2. 115,7796925, sek.
  3. 2,024749 sek.

2. iteracja:

  1. 3,1954857, sek.
  2. 84,97047 sek.
  3. 1,5783397, sek.

3. iteracja:

  1. 2,7922143, sek.
  2. 97,8713267 sek.
  3. 1,8432163, sek.

Średni:

  1. Baza danych: 3,4239506333 sek.
  2. Gdzie: 99,5404964 sek.
  3. Dołącz: 1,815435 sek.

Dlaczego wersja Join jest o wiele szybsza niż składnia where, co czyni ją bezużyteczną, chociaż jako nowicjusz LINQ wydaje się być najbardziej czytelna. A może przegapiłem coś w moich zapytaniach?

Oto zapytania LINQ, pomijam bazę danych:

Gdzie :

Public Function hasAccessDS_Where(ByVal accessRule As String) As Boolean
    Dim userID As Guid = DirectCast(Membership.GetUser.ProviderUserKey, Guid)
    Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule, _
                roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule, _
                role In Authorization.dsAuth.aspnet_Roles, _
                userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                Where accRule.idAccessRule = roleAccRule.fiAccessRule _
                And roleAccRule.fiRole = role.RoleId _
                And userRole.RoleId = role.RoleId _
                And userRole.UserId = userID And accRule.RuleName.Contains(accessRule)
                Select accRule.idAccessRule
    Return query.Any
End Function

Przystąp:

Public Function hasAccessDS_Join(ByVal accessRule As String) As Boolean
    Dim userID As Guid = DirectCast(Membership.GetUser.ProviderUserKey, Guid)
    Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule _
                Join roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule _
                On accRule.idAccessRule Equals roleAccRule.fiAccessRule _
                Join role In Authorization.dsAuth.aspnet_Roles _
                On role.RoleId Equals roleAccRule.fiRole _
                Join userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                On userRole.RoleId Equals role.RoleId _
                Where userRole.UserId = userID And accRule.RuleName.Contains(accessRule)
                Select accRule.idAccessRule
    Return query.Any
End Function

Z góry dziękuję.


Edycja : po pewnych ulepszeniach obu zapytań w celu uzyskania bardziej znaczących wartości wydajności, przewaga JOIN jest nawet wielokrotnie większa niż wcześniej:

Dołącz :

Public Overloads Shared Function hasAccessDS_Join(ByVal userID As Guid, ByVal idAccessRule As Int32) As Boolean
    Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule _
                   Join roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule _
                   On accRule.idAccessRule Equals roleAccRule.fiAccessRule _
                   Join role In Authorization.dsAuth.aspnet_Roles _
                   On role.RoleId Equals roleAccRule.fiRole _
                   Join userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                   On userRole.RoleId Equals role.RoleId _
                   Where accRule.idAccessRule = idAccessRule And userRole.UserId = userID
             Select role.RoleId
    Return query.Any
End Function

Gdzie :

Public Overloads Shared Function hasAccessDS_Where(ByVal userID As Guid, ByVal idAccessRule As Int32) As Boolean
    Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule, _
           roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule, _
           role In Authorization.dsAuth.aspnet_Roles, _
           userRole In Authorization.dsAuth.aspnet_UsersInRoles _
           Where accRule.idAccessRule = roleAccRule.fiAccessRule _
           And roleAccRule.fiRole = role.RoleId _
           And userRole.RoleId = role.RoleId _
           And accRule.idAccessRule = idAccessRule And userRole.UserId = userID
           Select role.RoleId
    Return query.Any
End Function

Wynik dla 1000 połączeń (na szybszym komputerze)

  1. Dołącz | 2. Gdzie

1. iteracja:

  1. 0,0713669 sek.
  2. 12,7395299 sek.

2. iteracja:

  1. 0,0492458 sek.
  2. 12,3885925 sek.

3. iteracja:

  1. 0,0501982, sek.
  2. 13,3474216 sek.

Średni:

  1. Dołącz: 0,0569367 sek.
  2. Gdzie: 12,8251813 sek.

Dołącz jest 225 razy szybciej

Wniosek: unikaj WHERE do określania relacji i używaj JOIN, gdy tylko jest to możliwe (zdecydowanie w LINQ to DataSet i Linq-To-Objectsogólnie).


Dla innych, którzy to czytają i używają LinqToSQL i uważają, że zmiana wszystkich Twoich WHEREs na JOINs może być dobra, przeczytaj komentarz Thomasa Levesque, w którym mówi: „istnieje taka optymalizacja, gdy używasz Linq do SQL lub Linq to Entities, ponieważ wygenerowane zapytanie SQL jest traktowane jako łączenie przez DBMS. Ale w tym przypadku używasz Linq do DataSet, nie ma tłumaczenia na SQL ”. Innymi słowy, nie przejmuj się zmienianiem czegokolwiek, gdy używasz linqtosql jako tłumaczenia WHERE's na złączenia.
JonH,

@JonH: nie zaszkodzi używać w Joinjakikolwiek sposób, po co polegać na optymalizatorze, skoro można napisać zoptymalizowany kod od początku? Sprawia również, że twoje intencje są jaśniejsze. Więc te same powody, dla których powinieneś preferować JOIN w sql .
Tim Schmelter,

Czy mam rację, zakładając, że tak nie byłoby w przypadku EntityFramework?
Mafii,

Odpowiedzi:


76
  1. Twoje pierwsze podejście (zapytanie SQL w bazie danych) jest dość wydajne, ponieważ baza danych wie, jak wykonać sprzężenie. Ale tak naprawdę nie ma sensu porównywać tego z innymi podejściami, ponieważ działają one bezpośrednio w pamięci (Linq do DataSet)

  2. Zapytanie z wieloma tabelami i Wherewarunkiem w rzeczywistości wykonuje iloczyn kartezjański wszystkich tabel, a następnie filtruje wiersze spełniające warunek. Oznacza to, że Wherewarunek jest oceniany dla każdej kombinacji wierszy (n1 * n2 * n3 * n4)

  3. JoinOperator wykonuje wiersze z pierwszych tabelach, po czym wykonuje tylko wiersze kluczem dopasowania z drugiej tablicy, po czym tylko rzędy z kluczem dopasowania z trzeciej tablicy obszaru, i tak dalej. Jest to znacznie wydajniejsze, ponieważ nie wymaga wykonywania tylu operacji


4
Dziękuję za wyjaśnienie tła. Podejście db nie było częścią tego pytania, ale było dla mnie interesujące zobaczyć, czy podejście oparte na pamięci jest naprawdę szybsze. whereZałożyłem, że .net zoptymalizuje -query w jakiś sposób, tak jak dbms. Właściwie JOINbył nawet 225 razy szybszy niż WHERE(ostatnia edycja).
Tim Schmelter,

19

Jest Jointo znacznie szybsze, ponieważ metoda wie, jak połączyć tabele, aby zredukować wynik do odpowiednich kombinacji. Kiedy używasz Wheredo określenia relacji, musi ona stworzyć każdą możliwą kombinację, a następnie przetestować warunek, aby zobaczyć, które kombinacje są odpowiednie.

JoinSposób można skonfigurować do korzystania z tabeli mieszania jako wskaźnik do quicky zip dwie tabele razem, podczas gdy Wheremetoda biegnie po tych wszystkich kombinacjach są już utworzone, więc nie można stosować żadnych sztuczek, aby zmniejszyć kombinacje wcześniej.


Dziękuję Ci. Czy nie ma niejawnych optymalizacji z kompilatora / środowiska uruchomieniowego, jak w DBMS? Nie powinno być niemożliwe zobaczenie, że relacja gdzie w rzeczywistości jest złączeniem.
Tim Schmelter,

1
Dobry RDBMS powinien rzeczywiście zauważyć, że warunek WHERE jest testem równości w dwóch UNIQUE kolumnach i traktować go jako JOIN.
Simon Richter,

6
@Tim Schelter, taka optymalizacja występuje, gdy używasz Linq do SQL lub Linq do Entities, ponieważ wygenerowane zapytanie SQL jest traktowane jako złączenie przez DBMS. Ale w tym przypadku używasz Linq do DataSet, nie ma tłumaczenia na SQL
Thomas Levesque,

@Tim: LINQ to DataSets faktycznie używa LINQ to Objects. W rezultacie prawdziwe sprzężenia można przechwytywać tylko za pomocą joinsłowa kluczowego, ponieważ nie ma analizy zapytania w czasie wykonywania, aby uzyskać cokolwiek analogicznego do planu wykonania. Zauważysz również, że sprzężenia oparte na LINQ mogą obsługiwać tylko jednokolumnowe equijoins.
Adam Robinson,

2
@Adam, to nie do końca prawda: możesz robić equijoins za pomocą wielu kluczy, używając typów anonimowych:... on new { f1.Key1, f1.Key2 } equals new { f2.Key1, f2.Key2 }
Thomas Levesque,

7

to, co naprawdę musisz wiedzieć, to sql, który został utworzony dla dwóch instrukcji. Można się do niego dostać na kilka sposobów, ale najprościej jest skorzystać z LinqPada. Nad wynikami zapytania znajduje się kilka przycisków, które zmienią się w sql. To da ci o wiele więcej informacji niż cokolwiek innego.

Ale świetne informacje, które tam udostępniłeś.


1
Dziękuję za podpowiedź LinqPad. Właściwie moje dwa zapytania są linQ do zestawu danych w zapytaniach pamięci, więc zakładam, że nie ma wygenerowanego kodu SQL. Normalnie byłoby to zoptymalizowane przez dbms.
Tim Schmelter,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.