Dlaczego mój ORDER BY sortuje dwie tabele przed WYJĄTKIEM (powoli), a nie po (szybko)?

12

Układanka optymalizatora zapytań SQL Server 2008 R2

Mamy dwie tabele, każda zawierająca 9 milionów wierszy. 70 000 wierszy jest różnych, pozostałe są takie same.

To jest szybkie, 13 sekund,

select * from bigtable1
except select * from similar_bigtable2

To sortuje dane wyjściowe i jest również szybkie, również 13 sekund,

select * into #q from bigtable1
except select * from similar_bigtable2
select * from #q order by sort_column

Chociaż jest to niezwykle powolne:

;with q as (
    select * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

Nawet „sztuczka”, której czasem używam, aby zasugerować programowi SQL Server, że musi wstępnie obliczyć określoną część zapytania, zanim przejdzie dalej, nie działa i powoduje również wolne zapytanie:

;with q as (
    select top 100 percent * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

Patrząc na plany zapytań, nie jest trudno znaleźć przyczynę:

Plan zapytań Plan zapytań z ORDER BY

SQL Server umieszcza dwa rodzaje 9 milionów wierszy przed hashatchem, podczas gdy wolałbym, aby dodał tylko jeden rodzaj 70 000 wierszy po hashatchu.

Więc pytanie: jak mogę polecić optymalizatorowi zapytań, aby to zrobił?

sql-server sql-server-2008

— thomaspaulb
źródło

3

Nie sortuje przed haszowaniem, sortuje, a następnie wykonuje scalenie (nie łączenie mieszające). Może istnieje wskazówka, aby wymusić łączenie mieszające (lub zapobiec łączeniu scalając)?

— Thilo,

3

Wygląda na to, że optymalizator zapytań SQL Server ustalił, że sortowanie danych jest korzystne, więc może użyć znacznie szybszego łączenia przez scalanie (które działa tylko w przypadku posortowanych danych) zamiast znacznie wolniejszego

— łączenia według mieszania

9

Czy próbowałeś alternatywy EXCEPT(np. OUTER JOIN)? Zdaję sobie sprawę, że składnia jest mniej wygodna, ale możesz lepiej grać ze wskazówkami dotyczącymi indeksowania / dołączania (lub nie musisz). Alternatywą, której teraz używasz (najpierw wstawianie do tabeli temperatur) jest obejście w ostateczności, ale w niektórych przypadkach jest to jedyny sposób, aby zmusić optymalizator do całkowitego oddzielenia dwóch części zapytania w pożądany sposób.

— Aaron Bertrand

1

Główna różnica między tymi dwoma planami zapytań polega na różnicy między dopasowaniem mieszania i połączeniem scalającym. Dopasowywanie skrótów jest bardziej wydajne i jak widać, zapytanie działa szybciej w opcji 1 (bez użycia CTE).

CTE jest doskonałym narzędziem, ale wydaje się, że nie jest wydajne w dwóch przypadkach, skomplikowanych predykatach lub nieunikalnym kluczu nadrzędnym / podrzędnym. W twoim przypadku nie ma unikalnego klucza, a serwer SQL musi najpierw posortować zestawy danych, aby spełnić twoje wymagania. Spójrz na poniższy link, który mówi ci więcej na ten temat: http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx

Wygląda więc na to, że musisz zaakceptować jego powolność lub przepisać logikę pętlą WHILE, która może być bardziej wydajna.

— Niebo
źródło

0

Spróbuj tego, jeszcze lepiej?

select * from
(
    select * from bigtable1
    except 
    select * from similar_bigtable2
) t
order by sort_column

— Gordon Bell
źródło

0

To nie jest idealne rozwiązanie, ale jeśli nie jesteś w stanie zbudować tsql w celu wygenerowania wydajnego planu, możesz ustawić przewodnik po planach, aby wymusić żądany plan. Może to oznaczać, że jeśli dostępny będzie bardziej wydajny plan, SQL go nie rozważy, ale jest to opcja.

— cfradenburg
źródło