INSERT jednorzędowy… WYBIERZ znacznie wolniej niż osobny WYBÓR

Biorąc pod uwagę następującą tabelę sterty z 400 wierszami ponumerowanymi od 1 do 400:

DROP TABLE IF EXISTS dbo.N;
GO
SELECT 
    SV.number
INTO dbo.N 
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

oraz następujące ustawienia:

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Następująca SELECTinstrukcja kończy się w ciągu około 6 sekund ( demo , plan ):

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

_{Uwaga: @ OPTIMIZE FORKlauzula ma na celu stworzenie rozsądnego rozmiaru repozytorium, które uchwyci istotne szczegóły prawdziwego problemu, w tym niedoszacowanie liczności, które może powstać z różnych powodów.}

Po zapisaniu danych wyjściowych z jednego wiersza w tabeli zajmuje to 19 sekund ( demo , plan ):

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

Plany wykonania wydają się identyczne oprócz wstawienia jednego wiersza.

Wydaje się, że cały dodatkowy czas pochłania użycie procesora.

Dlaczego INSERToświadczenie jest o wiele wolniejsze?

— Paul White przywraca Monikę
źródło

SQL Server wybiera skanowanie tabel stosów po wewnętrznej stronie sprzężeń pętli za pomocą blokad na poziomie wiersza. Pełny skan normalnie wybrałby blokowanie na poziomie strony, ale kombinacja wielkości tabeli i predykatu oznacza, że silnik pamięci wybiera blokady wierszy, ponieważ wydaje się to najtańszą strategią.

Niedopasowanie kardynalności celowo wprowadzone za pomocą OPTIMIZE FORśrodków, które są skanowane wiele razy więcej, niż się spodziewa optymalizator, i nie wprowadza szpuli, jak zwykle.

Ta kombinacja czynników oznacza, że wydajność jest bardzo wrażliwa na liczbę blokad wymaganych w czasie wykonywania.

Te SELECTkorzyści oświadczenie optymalizacji, które pozwala row-level wspólne zamki być pomijane (biorąc tylko intent-wspólne blokad na poziomie strony), gdy nie ma niebezpieczeństwa odczytu danych niewykorzystane, i nie ma żadnych danych off-row.

INSERT...SELECTOświadczenie nie korzysta z tej optymalizacji, więc miliony RID zamki są podejmowane i wydany na sekundę w drugim przypadku, wraz z zamiarem współdzielony zamków poziomie strony.

Ogromna ilość działań blokujących stanowi dodatkowy procesor i czas, który upłynął.

Najbardziej naturalnym obejściem jest zapewnienie, że optymalizator (i silnik pamięci masowej) otrzymają przyzwoite oszacowania liczności, aby mogli dokonywać dobrych wyborów.

Jeśli nie jest to praktyczne w prawdziwym przypadku użycia, instrukcje INSERTi SELECTmożna rozdzielić, a wynik będzie SELECTprzechowywany w zmiennej. Umożliwi SELECTto skorzystanie z optymalizacji pomijania blokad.

Zmianę poziomu izolacji można również uruchomić, nie przyjmując współdzielonych blokad lub zapewniając, że eskalacja blokady odbywa się szybko.

W końcowym punkcie zainteresowania zapytanie może zostać uruchomione nawet szybciej niż zoptymalizowany SELECTprzypadek poprzez wymuszenie użycia buforów przy użyciu nieudokumentowanej flagi śledzenia 8691.

— Paul White przywraca Monikę
źródło