Mam instrukcję SQL, która wstawia wiersze do tabeli z indeksem klastrowym w kolumnie TRACKING_NUMBER.
NA PRZYKŁAD:
INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, COL_C
FROM STAGING_TABLE
Moje pytanie brzmi - czy pomaga w użyciu klauzuli ORDER BY w instrukcji SELECT dla kolumny indeksu klastrowanego, czy też jakiekolwiek zyski zostałyby zanegowane przez dodatkowe sortowanie wymagane dla klauzuli ORDER BY?
Odpowiedzi:
Ponieważ inne odpowiedzi już wskazują, że SQL Server może, ale nie musi, jawnie zapewnić, że wiersze są sortowane w indeksie klastrowym przed insert.
DMLRequestSortZależy to od tego, czy operator indeksu klastrowanego w planie ma ustawioną właściwość (która z kolei zależy od szacunkowej liczby wstawianych wierszy).
Jeśli okaże się, że SQL Server nie docenia tego z jakiegokolwiek powodu, możesz skorzystać z dodania wyrażenia jawnego ORDER BYdo SELECTzapytania, aby zminimalizować podziały stron i wynikającą z tego fragmentację INSERToperacji
Przykład:
use tempdb;
GO
CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))
CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))
GO
DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)
INSERT INTO @T(N)
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499
/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
/*Same operation using explicit sort*/
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N
SELECT avg_fragmentation_in_percent,
fragment_count,
page_count,
avg_page_space_used_in_percent,
record_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;
SELECT avg_fragmentation_in_percent,
fragment_count,
page_count,
avg_page_space_used_in_percent,
record_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;
Programy, które Tsą bardzo rozdrobnione
avg_fragmentation_in_percent fragment_count page_count avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536 92535 92535 67.1668272794663 250000
99.5 200 200 74.2868173956017 92535
0 1 1 32.0978502594514 200
Ale T2fragmentacja jest minimalna
avg_fragmentation_in_percent fragment_count page_count avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376 262 62500 99.456387447492 250000
2.1551724137931 232 232 43.2438349394613 62500
0 1 1 37.2374598468001 232
I odwrotnie, czasem możesz zmusić SQL Server do niedoceniania liczby wierszy, gdy wiesz, że dane są już wstępnie posortowane i chcesz uniknąć niepotrzebnego sortowania. Jednym z godnych uwagi przykładów jest wstawianie dużej liczby wierszy do tabeli za pomocą newsequentialidklastrowego klucza indeksu. W wersjach SQL Server wcześniejszych niż Denali SQL Server dodaje niepotrzebną i potencjalnie kosztowną operację sortowania . Można tego uniknąć
DECLARE @var INT =2147483647
INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar
SQL Server oszacuje następnie, że zostanie wstawionych 100 wierszy, niezależnie od rozmiaru, Barktóry jest poniżej progu, do którego sortowanie jest dodawane do planu. Jednak, jak wskazano w komentarzach poniżej, oznacza to, że wkładka niestety nie będzie mogła skorzystać z minimalnego logowania.
Jeśli optymalizator zdecyduje, że bardziej wydajne byłoby sortowanie danych przed wstawieniem, zrobi to gdzieś przed operatorem wstawiania. Jeśli wprowadzisz sortowanie jako część zapytania, optymalizator powinien zdać sobie sprawę z tego, że dane są już posortowane i pominąć robienie tego ponownie. Uwaga: wybrany plan wykonania może się różnić w zależności od liczby wierszy wstawianych z tabeli pomostowej.
Jeśli możesz uchwycić plany wykonania procesu z jawnym sortowaniem i bez niego, dołącz je do pytania w celu komentarza.
Edycja: 28.10.2011 17:00
@ Odpowiedź Gonsalu wydaje się wskazywać, że zawsze występuje operacja sortowania, tak nie jest. Wymagane skrypty demonstracyjne!
Ponieważ skrypty stały się dość duże, przeniosłem je do Gist . Aby ułatwić eksperymentowanie, skrypty używają trybu SQLCMD. Testy przeprowadzane są na 2K5SP3, dwurdzeniowy, 8 GB.
Testy wstawek obejmują trzy scenariusze:
Pierwsze uruchomienie, wstawienie 25 rzędów.
Wszystkie trzy plany wykonania są takie same, nigdzie w planie nie występuje sortowanie, a skanowanie indeksu klastrowego jest „uporządkowane = fałszywe”.
Drugi przebieg, wstawienie 26 wierszy.
Tym razem plany się różnią.
Jest więc punkt zwrotny, w którym optymalizator uważa, że jest to konieczne. Jak pokazuje @MartinSmith, wydaje się, że jest to oparte na szacunkowych wierszach do wstawienia. Na moim urządzeniu testowym 25 nie wymaga sortowania, 26 wymaga (2K5SP3, dwurdzeniowy, 8 GB)
Skrypt SQLCMD zawiera zmienne, które pozwalają zmieniać rozmiar wierszy w tabeli (zmieniając gęstość strony) i liczbę wierszy w dbo.MyTable przed dodatkowymi wstawieniami. Z moich testów nie ma żadnego wpływu na punkt krytyczny.
Jeśli któryś z czytelników jest tak skłonny, uruchom skrypty i dodaj swój punkt zwrotny jako komentarz. Chciałbym usłyszeć, czy różni się w zależności od zestawu testowego i / lub wersji.
Edycja: 28.10.2011 20:15
Powtórzone testy na tym samym urządzeniu, ale z 2K8R2. Tym razem punkt krytyczny wynosi 251 rzędów. Ponownie, zmiana gęstości strony i liczby istniejących wierszy nie ma wpływu.
ORDER BYKlauzula w SELECToświadczeniu jest zbędny.
Jest zbędny, ponieważ wiersze, które zostaną wstawione, jeśli trzeba je posortować , są i tak sortowane.
Utwórzmy przypadek testowy.
CREATE TABLE #Test (
id INTEGER NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id);
CREATE TABLE #Sequence (
number INTEGER NOT NULL
);
INSERT INTO #Sequence
SELECT number FROM master..spt_values WHERE name IS NULL;
Włączmy wyświetlanie tekstowe rzeczywistych planów zapytań, abyśmy mogli zobaczyć, jakie zadania są wykonywane przez procesor zapytań.
SET STATISTICS PROFILE ON;
GO
Teraz INSERTwstawmy 2 tys. Wierszy do tabeli bez ORDER BYklauzuli.
INSERT INTO #Test
SELECT number
FROM #Sequence
Rzeczywisty plan wykonania tego zapytania jest następujący.
INSERT INTO #Test SELECT number FROM #Sequence
|--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))
Jak widać, przed rzeczywistym wstawieniem jest operator sortowania.
Teraz wyczyśćmy tabelę i INSERT2k wierszy do tabeli z ORDER BYklauzulą.
TRUNCATE TABLE #Test;
GO
INSERT INTO #Test
SELECT number
FROM #Sequence
ORDER BY number
Rzeczywisty plan wykonania tego zapytania jest następujący.
INSERT INTO #Test SELECT number FROM #Sequence ORDER BY number
|--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
|--Top(ROWCOUNT est 0)
|--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))
Zauważ, że jest to ten sam plan wykonania, który został użyty dla INSERTinstrukcji bez ORDER BYklauzuli.
Teraz Sortoperacja nie zawsze jest wymagana, jak pokazał Mark Smith w innej odpowiedzi (jeśli liczba wstawianych wierszy jest niska), ale ORDER BYklauzula jest w tym przypadku zbędna, ponieważ nawet w przypadku jawnej operacji ORDER BYnie Sortjest generowana przez procesor zapytań.
Możesz zoptymalizować INSERTinstrukcję do tabeli z indeksem klastrowym, używając minimalnie zalogowanego INSERT, ale to nie wchodzi w zakres tego pytania.
Aktualizacja 02.11.2011: Jak wykazał Mark Smith , INSERTs do tabeli z indeksem klastrowym może nie zawsze wymagać sortowania - ORDER BYklauzula jest jednak również zbędna w tym przypadku.