Błąd SQL Server 8632 z powodu ponad 100 000 wpisów w klauzuli WHERE

Mój problem (lub przynajmniej komunikat o błędzie) jest bardzo podobny do procesora zapytań, w którym zabrakło zasobów wewnętrznych - bardzo długie zapytanie SQL .

Mój klient pracuje z zapytaniem Select SQL, zawierającym klauzulę where zawierającą dokładnie 100 000 wpisów.

Zapytanie kończy się niepowodzeniem z błędem 8632 i komunikatem o błędzie

Błąd wewnętrzny: Osiągnięto limit usług wyrażeń. Wyszukaj potencjalnie złożone wyrażenia w zapytaniu i spróbuj je uprościć).

Uważam za bardzo dziwne, że ten komunikat o błędzie jest generowany dokładnie przy 100 000 wpisów, więc zastanawiam się, czy jest to wartość konfigurowalna. Czy tak jest w przypadku, a jeśli tak, jak mogę zwiększyć tę wartość do wyższej?

W MSDN istnieje propozycja ponownego przepisania zapytania, ale chciałbym tego uniknąć.

Tymczasem dowiedziałem się, że lista wpisów, o których mówię, zawiera liczby naturalne, niektóre z nich wydają się sekwencyjne (coś w rodzaju (1,2,3,6,7,8,9,10,12, 13,15,16,17,18,19,20).

To sprawia, że ​​klauzula where w SQL wygląda jak:

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

Mógłbym to przekształcić w:

where (entry between 1 and 3) OR
      (entry between 6 and 10) OR
      (entry between 12 and 13) OR
      (entry between 15 and 20)

Czy można to skrócić:

where entry in (1,...,3,6,...,10,12,13,15,...,20)

... czy coś podobnego? (Wiem, że to długa szansa, ale dzięki temu aktualizacje oprogramowania byłyby łatwiejsze i bardziej czytelne)

Dla twojej informacji: dane w klauzuli where są wynikiem obliczeń wykonanych w innej tabeli: najpierw wpisy tej tabeli są odczytywane i filtrowane na początku, a następnie przeprowadzane jest dodatkowe przetwarzanie (co jest niemożliwe przy użyciu SQL), wynikiem tego dodatkowego przetwarzania jest więcej filtrowania, a wynik jest wykorzystywany w klauzuli where. Ponieważ napisanie pełnego filtrowania w SQL nie było możliwe, użyto wspomnianej metody. Oczywiście treść klauzuli where może zmieniać się przy każdym przetwarzaniu, stąd potrzeba dynamicznego rozwiązania.

Aby wyszukać więcej niż 100 000 wartości, umieść je zamiast tego w tabeli tymczasowej, po jednym wierszu na szukaną wartość. Następnie dołącz zapytanie do tej tabeli tymczasowej w celu filtrowania.

Coś z ponad 100 000 wartości nie jest parametrem - to tabela. Zamiast zastanawiać się nad podniesieniem limitu, weź pod uwagę dziesięcioprocentową zasadę Swarta : jeśli zbliżasz się do 10% limitu SQL Server, prawdopodobnie będziesz miał zły czas.

Jeśli i tak zamierzasz zmienić aplikację, zastanów się

(a) używając TVP dla całego zestawu wartości - utworzyłbyś DataTablew C # i przekazałeś go do procedury składowanej za pomocą StructuredType, jak pokazałem tutaj . (Mamy nadzieję, że 100 000 wpisów nie jest normalne, ponieważ skalowalność może być problemem bez względu na stosowane podejście).

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

lub

(b) używając TVP do przejścia w górnych i dolnych granicach zakresów i pisania nieco innego sprzężenia (dzięki @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON  t.entry >= tvp.LowerBound 
      AND t.entry <= tvp.UpperBound;
  END
  GO

Nie, nie można go konfigurować i nie można go zwiększyć do wyższej.

W wspomnianym artykule MSDN i innych artykułach zaproponowano obejście tego problemu. Wspomniałem tutaj o dwóch, ale możesz szukać więcej.

• Komunikat o błędzie podczas uruchamiania zapytania w SQL Server 2005: „Błąd wewnętrzny: Osiągnięto limit usług wyrażania” przez Microsoft.
• Ograniczenie klauzuli „IN” w Sql Server

Tylko moje 2 ¢ dotyczące skrócenia warunku zapytania: -

Jeśli jesteś w stanie z góry ustalić wszystkie możliwe wartości entry, czy wykonalne byłoby uzupełnienie zapytania?

Przed

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

Po

where entry not in (4,5,11,14)

Trudno dowiedzieć się, co próbujesz osiągnąć bez faktycznego wyświetlenia zapytania, ale zaczynamy, zakładając, że twoje zapytanie wymaga tylko dwóch tabel, jednej z danymi, drugiej z wartościami, których chcesz uniknąć:

• Korzystanie where entry in (<list of 100.000 values>)jest oburzająco okropne, zarówno z punktu widzenia wydajności, jak i konserwacji.
• Używanie where entry in (select whatever from table)jest zaledwie tak okropnie okropne jak poprzednie. Jednak w zależności od specyfiki obu tabel i silnika SQL może on działać OK pomimo raka rogówki. (Może być OK w Oracle, nigdy nie będzie w MySQL, nie pamiętam o MSSQL).
• Używanie PLSQL do osiągnięcia tego jest po prostu przerażające.

• Moim zdaniem (w ogóle nie znając zapytania) powinieneś przepisać zapytanie w następujący sposób:

  • Jeśli te 100 000 wartości są zawsze takie same, nie zależąc od reszty zapytania, należy przesłać te wartości do tabeli (wartości_poprawione_tabeli) i użyć

    SELECT * -- whatever you might need
    FROM
       table_a A
       LEFT JOIN table_fixed_values ON A.entry=B.entry
    WHERE B.entry IS NOT NULL

  • Jeśli te 100 000 wartości nie są takie same, musi istnieć jakiś rodzaj logiki, aby podnieść te 100 000 wartości, logikę, którą powinieneś osadzić w ONpoprzednim zapytaniu.