Zapytanie SQL Server powolne po paginacji

14

Widzę dziwne zachowanie w przypadku następującego zapytania T-SQL w programie SQL Server 2012:

SELECT Id 
FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name

Samo wykonanie tego zapytania daje mi około 1300 wyników w mniej niż dwie sekundy (włączony jest indeks pełnotekstowy Name)

Jednak po zmianie zapytania na to:

SELECT Id 
FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name
OFFSET 0 rows
FETCH NEXT 10 ROWS ONLY

Daje mi 10 wyników dłużej niż 20 sekund.

Następujące zapytanie jest jeszcze gorsze:

SELECT Id 
FROM ( 
    SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Id 
    FROM dbo.Person
    WHERE CONTAINS(Name, '"John" AND "Smith"') ) AS RowConstrainedResult 
WHERE RowNum >= 0 AND RowNum < 11 
ORDER BY RowNum

Wykonanie zajmuje ponad 1,5 minuty!

Jakieś pomysły?

Powolny plan

Powolny

Szybki plan

Szybki

sql-server sql-server-2012

— vrs
źródło

Co się stanie, jeśli zmienisz swoje drugie zapytanie na SELECT TOP 10 * .... ORDER BY Name?

— Lamak

W jakich kolumnach tworzony jest indeks IX_PersonSearch ...? Dostajesz kluczowy odnośnik, ponieważ wybierasz * z tabeli, a użyty indeks nie zawiera wszystkich kolumn wyjściowych. Myślę, że powinieneś wybrać tylko potrzebne kolumny, a następnie dołączyć je do indeksu nieklastrowanego jako uwzględnione kolumny, a nie kolumny indeksowe.

— Marcel N.

Czy możesz opublikować swoje indeksy na stole (utworzyć skrypt)?

3

Identyfikator jest zawsze zawarty w każdym indeksie nieklastrowanym. W ten sposób SQL Server może kluczować wyszukiwania (według identyfikatora).

— usr

1

To, o czym zapomniałem wspomnieć: kiedy wykonuję to samo zapytanie z LIKE zamiast ZAWIERA, jest również szybkie. (Paginowany czy nie)

7

Ponieważ po prostu chcesz TOP 10uporządkować według nazwy, myślę, że szybsze jest opracowanie indeksu namew kolejności i sprawdzenie, czy każdy wiersz pasuje do CONTAINS(Name, '"John" AND "Smith"') )predykatu.

Przypuszczalnie potrzeba 10 dodatkowych wierszy, aby znaleźć 10 wymaganych meczów, niż się spodziewa, a ten problem liczności jest złożony z liczbą kluczowych wyszukiwań.

Szybkie hack, aby zatrzymać go przy użyciu tego planu byłoby zmienić ORDER BY, aby ORDER BY Name + ''chociaż przy użyciu CONTAINSTABLEw połączeniu ze FORCE ORDERpowinna również współpracować.

— Martin Smith
źródło

3

To wygląda jak klasyczne niedorzeczność selektywności. Nie jestem pewien, co można z tym zrobić, ponieważ „sterownik” zapytania jest wyszukiwaniem pełnotekstowym, którego nie można rozszerzyć o statystyki.

Spróbuj przepisać where containspredykat na inner join containstable( CONTAINSTABLE ) i zastosuj wskazówki dotyczące kolejności łączenia, aby wymusić kształt planu.

To nie jest idealne rozwiązanie, ponieważ ma problemy z konserwacją, ale nie widzę innego sposobu.

— usr
źródło

Dzięki za odpowiedź, próbowałem. Ten sam wynik: Gdy nie używa się paginacji, zapytanie jest naprawdę szybkie. Podczas paginacji nagle staje się on naprawdę powolny: /

Ok, czy możesz opublikować plan jako obraz i zapytanie? Domyślam się, że nie udało nam się jeszcze wygenerować pożądanego kształtu.

— usr

3

Udało mi się rozwiązać problem:

Jak powiedziałem w pytaniu, były indeksy dla wszystkich kolumn + statystyki dla każdej kolumny. (Z powodu starszych zapytań typu LIKE) Usunąłem wszystkie indizes i statystyki, dodałem wyszukiwanie pełnotekstowe i voila, zapytanie stało się naprawdę szybkie.

Wygląda na to, że indeksy doprowadziły do innego planu wykonania.

Dziękuję wszystkim bardzo za pomoc!

— vrs
źródło

1

Cóż, całkowite usunięcie indeksu jest jednym ze sposobów zapobiegania jego użyciu, tak myślę!

— Martin Smith,