Dlaczego operator Contains() tak drastycznie obniża wydajność struktury encji?

Jeśli znajdziesz problem z wydajnością, który blokuje dla Ciebie, nie próbuj spędzać wieków na rozwiązywaniu go, ponieważ najprawdopodobniej nie odniesiesz sukcesu i będziesz musiał komunikować się z MS bezpośrednio (jeśli masz wsparcie premium) i to trwa wieki.

Użyj obejścia i obejścia w przypadku problemów z wydajnością, a EF oznacza direct SQL. Nie ma w tym nic złego. Globalny pomysł, że używanie EF = nie używanie już SQL jest kłamstwem. Masz SQL Server 2008 R2 więc:

• Utwórz procedura składowana akceptująca parametr table valued do przekazania identyfikatorów
• niech procedura składowana zwróci wiele zestawów wyników, aby emulować Include logikę w optymalny sposób
• jeśli potrzebujesz skomplikowanego budowania zapytań użyj dynamicznego SQL wewnątrz procedury składowanej
• Użyj SqlDataReader, Aby uzyskać wyniki i skonstruować swoje byty
• dołącz je do kontekstu i pracuj z nimi tak, jakby zostały załadowane z EF

Jeśli wydajność jest dla Ciebie krytyczna, nie znajdziesz lepszego rozwiązania. Ta procedura nie może być mapowana i wykonywana przez EF, ponieważ aktualna wersja nie obsługuje ani parametrów wartości tabeli, ani wielu zestawów wyników.

Udało nam się rozwiązać problem EF Contains dodając tabelę pośrednią i łącząc ją z zapytania LINQ, które wymagało użycia klauzuli Contains. Udało nam się uzyskać niesamowite rezultaty dzięki takiemu podejściu. Mamy duży model EF i ponieważ "Contains" nie jest dozwolone podczas wstępnej kompilacji zapytań EF, otrzymaliśmy bardzo słabą wydajność dla zapytań, które używają klauzuli "Contains".

Przegląd:

• Tworzenie tabeli w SQL Server-na przykład HelperForContainsOfIntType z HelperID z kolumn typu danych Guid i ReferenceID z kolumn typu danych int. Tworzenie różnych tabel z ReferenceID różnych typów danych w razie potrzeby.

• Utwórz Entity / EntitySet dla HelperForContainsOfIntType i innych tego typu tabel w modelu EF. Utwórz różne Entity / EntitySet dla różnych typów danych w razie potrzeby.

• Tworzenie metody pomocniczej w kodzie. NET, która pobiera dane wejściowe IEnumerable<int> i zwraca Guid. Metoda ta generuje nowy Guid i wstawia wartości z IEnumerable<int> do HelperForContainsOfIntType wraz z wygenerowanym Guid. Następnie Metoda zwraca nowo wygenerowaną Guid do wywołującego. Aby szybko wstawić do tabeli HelperForContainsOfIntType, Utwórz procedurę przechowywaną, która pobiera listę wartości i dokonuje wstawiania. Patrz parametry wartości tabeli w SQL Server 2008 (ADO.NET) . Utwórz różne helpery dla różnych typów danych lub utwórz ogólną metodę helpera do obsługi różnych typów danych.

• Utwórz skompilowane zapytanie EF, które jest podobne do czegoś takiego jak poniżej:

  static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
      CompiledQuery.Compile(
          (MyEntities db, Guid containsHelperID) =>
              from cust in db.Customers
              join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
              select cust 
      );
  

• Wywołanie metody pomocniczej z wartościami do użycia w klauzuli Contains i uzyskanie Guid do użycia w zapytaniu. Na przykład:

  var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
  var result = _selectCustomers(_dbContext, containsHelperID).ToList();
  

Edytowanie mojej oryginalnej odpowiedzi - Istnieje możliwe obejście, w zależności od złożoności Twoich podmiotów. Jeśli znasz sql generowany przez EF w celu wypełnienia encji, możesz go wykonać bezpośrednio za pomocą DbContext.Baza danych.SqlQuery . W EF 4, myślę, że można użyć ObjectContext.ExecuteStoreQuery, ale nie próbowałem.

Na przykład, używając kodu z mojej oryginalnej Odpowiedzi poniżej do wygenerowania instrukcji sql przy użyciu StringBuilder, byłem w stanie zrobić po

var rows = db.Database.SqlQuery<Main>(sql).ToArray();

A łączny czas poszedł z około 26 sekund do 0,5 sekundy.

update

Po dłuższym namyśle zdałem sobie sprawę, że jeśli używasz join do filtrowania wyników, EF nie musi budować tak długiej listy identyfikatorów. Może to być skomplikowane w zależności od liczby jednoczesnych zapytań, ale wierzę, że można użyć identyfikatorów użytkowników lub identyfikatorów sesji żeby ich odizolować.

Aby to przetestować, stworzyłem Target tabelę z tym samym schematem co Main. Następnie użyłem StringBuilder do stworzenia INSERT poleceń wypełniających tabelę Target w partiach po 1000, ponieważ jest to większość SQL Server zaakceptuje w jednym INSERT. Bezpośrednie wykonywanie poleceń sql było znacznie szybsze niż przechodzenie przez EF (około 0,3 sekundy vs.2,5 sekundy), i uważam, że byłoby ok, ponieważ schemat tabeli nie powinien się zmieniać.

Na koniec wybranie za pomocą join spowodowało znacznie prostsze zapytanie i wykonane w mniej niż 0,5 sekundy.

ExecuteStoreCommand("DELETE Target");

var ids = Main.Select(a => a.Id).ToArray();
var sb = new StringBuilder();

for (int i = 0; i < 10; i++)
{
    sb.Append("INSERT INTO Target(Id) VALUES (");
    for (int j = 1; j <= 1000; j++)
    {
        if (j > 1)
        {
            sb.Append(",(");
        }
        sb.Append(i * 1000 + j);
        sb.Append(")");
    }
    ExecuteStoreCommand(sb.ToString());
    sb.Clear();
}

var rows = (from m in Main
            join t in Target on m.Id equals t.Id
            select m).ToArray();

rows.Length.Dump();

SELECT 
[Extent1].[Id] AS [Id]
FROM  [dbo].[Main] AS [Extent1]
INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

(oryginalna odpowiedź)

To nie jest odpowiedź, ale chciałem podzielić się kilkoma dodatkowymi informacjami i jest to zdecydowanie za długie, aby zmieścić się w komentarzu. Udało mi się odtworzyć Twoje wyniki i mam kilka innych rzeczy do dodania:

SQL Profiler pokazuje opóźnienie pomiędzy wykonaniem pierwszego zapytania (Main.Select) a drugim Main.Where zapytanie, więc podejrzewałem, że problem polegał na wygenerowaniu i wysłaniu zapytania o takim rozmiarze(48,980 bajtów).

Jednak dynamiczne zbudowanie tego samego polecenia sql w T-SQL zajmuje mniej niż 1 sekundę, a pobranie ids z instrukcji Main.Select, zbudowanie tego samego polecenia sql i wykonanie go za pomocą SqlCommand Zajęło 0,112 sekundy, wliczając w to czas na zapisanie zawartości do konsoli.

W tym momencie podejrzewam, że EF robi jakąś analizę/przetwarzanie dla każdego z 10.000 ids jak buduje zapytanie. Szkoda, że nie mogę udzielić ostatecznej odpowiedzi i rozwiązania :(.

Oto kod, który próbowałem w SSMS i LINQPad (proszę nie krytykować zbyt surowo, spieszę się, próbując wyjść z pracy): {]}

declare @sql nvarchar(max)

set @sql = 'SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Main] AS [Extent1]
WHERE [Extent1].[Id] IN ('

declare @count int = 0
while @count < 10000
begin
    if @count > 0 set @sql = @sql + ','
    set @count = @count + 1
    set @sql = @sql + cast(@count as nvarchar)
end
set @sql = @sql + ')'

exec(@sql)

var ids = Mains.Select(a => a.Id).ToArray();

var sb = new StringBuilder();
sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (");
for(int i = 0; i < ids.Length; i++)
{
    if (i > 0) 
        sb.Append(",");     
    sb.Append(ids[i].ToString());
}
sb.Append(")");

using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true"))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = sb.ToString();
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0));
        }
    }
}

Nie jestem zaznajomiony z Entity Framework, ale czy perf jest lepszy, jeśli wykonasz następujące czynności?

Zamiast tego:

var ids = Main.Select(a => a.Id).ToArray();
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

Co powiesz na to (zakładając, że ID jest int):

var ids = new HashSet<int>(Main.Select(a => a.Id));
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

Została ustalona na encji Framework 6 alfa 2: http://entityframework.codeplex.com/SourceControl/changeset/a7b70f69e551

Http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx

Cacheable alternatywa dla Contains?

To mnie ugryzło, więc dodałem moje dwa pensy do sugestii funkcji Entity Framework link.

Problem jest zdecydowanie podczas generowania SQL. Mam klienta na danych who generowanie zapytania było 4 sekundy, ale wykonanie było 0.1 sekundy.

Zauważyłem, że podczas używania dynamicznego LINQ i ORs generowanie sql trwało tak długo, ale wygenerowało coś, co może być buforowane . Więc kiedy wykonanie go ponownie trwało 0,2 sekundy.

Zauważ, że SQL in był nadal generowany.

Po prostu coś innego do rozważenia, jeśli możesz znieść początkowy hit, liczba tablic nie zmienia się zbyt wiele i uruchom zapytanie dużo. (Testowane w LINQ Pad)

Problem dotyczy generowania SQL przez Entity Framework. Nie może buforować zapytania, jeśli jeden z parametrów jest listą.

Aby uzyskać EF do buforowania zapytania można przekonwertować listę na ciąg znaków i zrobić .Zawiera na sznurku.

Więc na przykład ten kod będzie działał znacznie szybciej, ponieważ EF może buforować zapytanie:

var ids = Main.Select(a => a.Id).ToArray();
var idsString = "|" + String.Join("|", ids) + "|";
var rows = Main
.Where (a => idsString.Contains("|" + a.Id + "|"))
.ToArray();

Gdy to zapytanie zostanie wygenerowane, prawdopodobnie zostanie wygenerowane za pomocą Like zamiast In, więc przyspieszy to Twój C# , ale może potencjalnie spowolnić twój SQL. W moim przypadku nie zauważyłem żadnego spadku wydajności w moim wykonaniu SQL, A C# działał znacznie szybciej.