Artykuł
07/22/2011

Optymalizacja wydajności instrukcji korespondencji seryjnej

W SQL Server 2008, można wykonywać wiele język edycji danych operacje (DML) w pojedynczej instrukcja przy użyciu instrukcja korespondencji seryjnej.Na przykład może być konieczne synchronizacji dwóch tabel przy wstawianiu, aktualizowania lub usuwania wierszy w jednej tabela, oparte na różnicach w drugiej tabela.Zazwyczaj jest to realizowane przez wykonanie procedura składowana lub wsadowy, zawierający poszczególne instrukcje INSERT, UPDATE i DELETE. Jednakże, oznacza to, że dane w obu i miejsce docelowe tabele są oceniane i przetwarzane wiele razy; co najmniej raz dla każdego instrukcja.

Za pomocą instrukcja korespondencji seryjnej, można zastąpić poszczególnych DML sprawozdania z pojedynczą instrukcją.Można poprawić wydajność kwerendy, ponieważ operacje są wykonywane w ramach pojedynczej instrukcja, w związku z tym, minimalizując liczbę razy w źródle danych i miejsce docelowe tabele są przetwarzane.Jednak wzrost wydajności są zależne od posiadania poprawne indeksy, sprzężenia i inne względy.W tym temacie przedstawiono najlepszymi sposobami mają pomóc osiągnąć optymalną wydajność podczas korzystania z scalania instrukcja.

Najważniejsze wskazówki dotyczące indeksu

Aby zwiększyć wydajność instrukcja korespondencji seryjnej, zaleca się następujące wytyczne indeksu:

Tworzenie indeksu kolumny łączyć w źródło tabela, która jest unikatowa i pokrycie.
Tworzenie unikatowego indeks klastrowany kolumny łączyć w tabela miejsce docelowe.

Indeksy te zapewniają klucze łączyć są unikatowe i sortowania danych w tabelach.Zwiększona wydajność kwerendy, ponieważ optymalizator kwerendy nie trzeba wykonywać dodatkowe sprawdzania poprawności przetwarzania do zlokalizowania i zaktualizować zduplikowane wiersze i sortowanie dodatkowe operacje nie są konieczne.

Na przykład, w następującej instrukcja seryjnej źródło tabela, dbo.Purchasesi tabela miejsce docelowe, dbo.FactBuyingHabits, są połączone w kolumnach ProductID i CustomerID.Aby zwiększyć wydajność tej instrukcja, należy utworzyć unikatowy lub klucz podstawowy indeksu (klastrowany lub nieklastrowany) na ProductID i CustomerID kolumn w dbo.Purchases tabela i indeksem klastrowanym dla ProductID i CustomerID kolumn w dbo.FactBuyingHabits tabela.Aby wyświetlić kod używany do tworzenia tych tabel, zobacz Wstawianie, aktualizowanie i usuwanie danych za pomocą korespondencji seryjnej.

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

Dołącz najważniejsze wskazówki

Aby zwiększyć wydajność instrukcja korespondencji seryjnej i zapewnić poprawne wyniki są uzyskiwane, zaleca następujące wytyczne łączyć:

Określ tylko warunki wyszukiwania w ON <merge_search_condition> klauzula, które określają kryteria dopasowywania danych w źródle i miejsce docelowe tabele.Określ tylko te kolumny z tabela miejsce docelowe, które są porównywane odpowiednich kolumn źródło tabela.Nie należy dołączać porównań inne wartości, takich jak stała.

Aby odfiltrować wiersze ze źródła lub miejsce docelowe tabel, użyj jednej z następujących metod.

Określ warunek wyszukiwania dla wiersza filtrowania w odpowiednich klauzula gdy.Na przykład WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Definiowanie widoku na źródło lub miejsce docelowe zwraca filtrowane wiersze i odwołania widoku jako źródło lub tabela miejsce docelowe.Jeśli widok jest zdefiniowana w tabela miejsce docelowe, wszelkie skargi muszą spełniać warunki uaktualniania widoków.Aby uzyskać więcej informacji na temat aktualizowania danych przy użyciu widoku, zobacz Modyfikowanie danych przy użyciu widoku.
Korzystać z <Typowe wyrażenie tabela> klauzula, aby odfiltrować wiersze ze źródła lub miejsce docelowe tabele.Ta metoda jest podobne do określania dodatkowe kryteria wyszukiwania w klauzula ON i może powodować nieprawidłowe wyniki.Zaleca się unikać stosowania tej metoda lub dokładnie przetestować przed jego wykonania.

Aby uzyskać więcej informacji, zobacz Wstawianie, aktualizowanie i usuwanie danych za pomocą korespondencji seryjnej.

Optymalizacja kwerend sprzężeń

Operacji łączyć w instrukcja korespondencji seryjnej jest zoptymalizowana w taki sam sposób jak łączyć w instrukcja SELECT.Oznacza to, kiedy SQL Server przetwarza sprzężeń, optymalizator kwerendy wybiera najbardziej efektywne metoda (z kilku możliwości) przetwarzania łączyć.Aby uzyskać więcej informacji na temat sprzężeń, zobacz Dołącz podstawy i Zaawansowane kwerendy dostrajania pojęcia. Gdy źródła i miejsce docelowe są podobne wielkości i wskazówek indeksu opisane wcześniej w "Indeks najlepsze praktyki" sekcji są stosowane do źródła i miejsce docelowe tabel, seryjna łączyć operator jest najbardziej efektywnego planu kwerend.Jest tak, ponieważ obie tabele są skanowane raz i nie ma potrzeby sortowania danych.Gdy źródło jest mniejszy niż tabela miejsce docelowe zagnieżdżonej pętli operator jest preferowane.

Można wymusić użycie określonego łączyć określając OPTION (<query_hint>) klauzula w instrukcja korespondencji seryjnej.Firma Microsoft zaleca, aby używać łączyć mieszania jako wskazówka dotycząca kwerendy dla korespondencji seryjnej sprawozdań ponieważ tego typu łączyć nie używa indeksów.Aby uzyskać więcej informacji na temat wskazówek dotyczących kwerendy, zobacz Wskazówki kwerendy (Transact-SQL).W następującym przykładzie określono zagnieżdżonej pętli łączyć w klauzula opcji.

USE AdventureWorks2008R2;
GO
BEGIN TRAN;
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) 
       FROM Sales.SalesOrderDetail AS sod
       JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
         AND soh.OrderDate BETWEEN '20030701' AND '20030731'
       GROUP BY ProductID) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*
OPTION (LOOP JOIN);
GO
ROLLBACK TRAN;

Parametryzacja najważniejsze wskazówki

Jeśli w instrukcja SELECT, INSERT, UPDATE lub DELETE jest wykonywane bez parametrów, SQL Server optymalizator kwerendy może wybrać parameterize instrukcja wewnętrznie.Oznacza to, że wartości literałów, które są zawarte w kwerendzie są zastępowane z parametrami. Na przykład, instrukcja INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), mogą być wdrożone wewnętrznie jako INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2).Ten proces nazywany parametryzacja proste, zwiększa zdolność aparat relacyjny do nowej instrukcji SQL z istniejących, uprzednio skompilowane wykonanie planów.Wydajność kwerendy może zostać podniesiona, ponieważ są redukowane częstotliwości kompilacji kwerendy i ponowne kompilacje.optymalizator kwerendy nie dotyczą procesu parametryzacja proste instrukcje korespondencji seryjnej.Dlatego instrukcja scalania, które zawierają wartości literałów nie mogą wykonywać również indywidualnych instrukcja INSERT, UPDATE lub DELETE ponieważ skompilowany nowy plan każdego czas wykonaniu instrukcja korespondencji seryjnej.

Aby poprawić wydajność kwerendy, zaleca się następujące wytyczne parametryzacja:

Wszystkie wartości literału w parameterize ON <merge_search_condition> klauzula i w klauzul podczas scalania instrukcja.Na przykład można dołączyć scalania instrukcja do procedura składowana, zastępując wartości literału odpowiednich parametrów wejściowych.
Jeśli instrukcja nie jest parameterize, utworzenia przewodnik planu typu szablonu i określić ZMUSZONY PARAMETRYZACJI kwerendy wskazówkę dotyczącą przewodnik planu.Aby uzyskać więcej informacji, zobacz Określanie zachowania parametryzacji kwerendy przy użyciu prowadnic Plan.
Instrukcje korespondencji seryjnej często wykonywane są bazy danych, należy rozważyć ustawienie opcji PARAMETRYZACJA na bazie WYMUSZONY.Ustawienie tej opcji, należy zachować ostrożność.Opcja PARAMETRYZACJA jest ustawienie poziom bazy danych i wpływa na sposób wszystkie kwerendy w bazie danych są przetwarzane.Aby uzyskać więcej informacji, zobacz Parametryzacja wymuszony.

Najważniejsze wskazówki klauzuli TOP

W instrukcja seryjnej klauzula TOP Określa numer lub procent wierszy, których dotyczy po źródło tabela oraz miejsce docelowe tabela są sprzężone i po wiersze, które nie kwalifikują się do insert, update lub delete akcja są usuwane.Dalsze klauzula TOP zmniejsza liczbę wierszy sprzężonych określona wartość i insert, update lub delete akcje są stosowane do pozostałych wierszy połączonych w sposób nieuporządkowane.Oznacza to, że jest nie zamówienia, w którym wiersze są rozdzielone między akcje zdefiniowane w klauzulach podczas.Na przykład określenie z góry (10) wpływa na wiersze 10; z tych wierszy 7 mogą być aktualizowane i 3 wstawiony lub 1 mogą zostać usunięte, 5 zaktualizowane i 4 dodaje itd.

Często wykonywać za pomocą klauzula TOP język edycji danych operacji (DML) w dużej tabela w partiach.Przy użyciu klauzula TOP w instrukcja korespondencji seryjnej w tym celu, jest ważne zrozumienie następujących.

Może wpłynąć na wydajność wejścia/wyjścia.

Instrukcja seryjnej wykonuje skanowanie pełne tabela zarówno źródła i miejsce docelowe tabele.Podzielenie operacji do partiaes zmniejsza liczbę operacji zapisu na partia; Jednakże każda partia zostanie skanowanie pełne tabela źródła i miejsce docelowe tabele.Wynikowy aktywności odczytu może wpłynąć na wydajność kwerendy.
Może wystąpić niepoprawnych wyniki.

Jest ważne, aby zapewnić, że wszystkie kolejne instancje miejsce docelowe może wystąpić nowe wiersze lub niepożądane zachowanie takich jak niepoprawnie Wstawianie zduplikowane wiersze w tabela tartget.Może się to zdarzyć, gdy źródło tabela zawiera wiersz, który nie był w partia miejsce docelowe, ale został w ogólnej tabela miejsce docelowe.

Ubezpieczenie poprawnych wyniki:
- Za pomocą klauzula ON określić źródło wierszy, które wpływają na istniejące wiersze miejsce docelowe i które są rzeczywiście nowych.
- KIEDY użyć dodatkowy warunek klauzula DOPASOWANE do ustalenia, jeśli wiersz miejsce docelowe został już zaktualizowany przez poprzedniej partia.
Ponieważ po zastosowaniu tych klauzul tylko stosowana jest klauzula TOP, wykonanie każdego wstawia wiersz rzeczywiście niedopasowanych lub aktualizacje istniejących wiersz.Poniższy przykład tworzy źródło i tabela miejsce docelowe i przedstawia poprawna metoda zmodyfikować obiekt miejsce docelowe operacji wsadowych przy użyciu klauzula TOP.
```
CREATE TABLE dbo.inventory(item_key int NOT NULL PRIMARY KEY, amount int, is_current bit);
GO
CREATE TABLE dbo.net_changes(item_key int NOT NULL PRIMARY KEY, amount int);
GO

MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key
WHEN MATCHED AND inventory.is_current = 0
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) VALUES(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
Poniższy przykład ilustruje niepoprawna metoda stosowania klauzula TOP.Sprawdzenie is_current określonej kolumna w warunku łączyć z źródło tabela.Oznacza to, że źródło wierszy w jednej partia będzie traktowany jako "nie dopasowane" w następnej partia, powstałe w operacji wstawiania niepożądane.
```
MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key AND inventory.is_current = 0
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
Poniższy przykład ilustruje również niepoprawnej metoda.Używając typowe wyrażenie tabela (CTE) aby ograniczyć liczbę wierszy odczytu dla instancji dowolnego źródło wierszy, które mogłyby mieć dopasowane miejsce docelowe wierszy innych niż wybrana przez TOP(1) jest traktowany jako "nie odpowiada", w operacji wstawiania niepożądane.Ponadto ta metoda jedynie ogranicza liczbę wierszy, które mogą być aktualizowane; Każda partia spróbuje wstawić wszystkie "niedopasowane" Źródło wierszy.
```
WITH target_batch AS (
  SELECT TOP(1) *
  FROM dbo.inventory
  WHERE is_current = 0
  )
MERGE target_batch
USING dbo.net_changes
ON target_batch.item_key = net_changes.item_key
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```

Najważniejsze wskazówki dotyczące obciążenia luzem

Instrukcja korespondencji seryjnej można skutecznie ładowanie zbiorcze dane z pliku źródła danych do tabela miejsce docelowe, określając klauzula OPENROWSET(BULK…) jako źródło tabela.Dzięki temu cały plik jest przetwarzany w jednej partia.

Aby zwiększyć wydajność procesu scalania luzem, zaleca się następujące wytyczne:

Tworzenie indeks klastrowany kolumny łączyć w tabela miejsce docelowe.
Określ sposób sortowania plik źródło danych za pomocą wskazówki zamówienia i unikatowe w klauzula OPENROWSET(BULK…).

Domyślnie operacja zbiorcza zakłada pliku danych są nieuporządkowane.Dlatego jest ważne, że źródło danych jest posortowany zgodnie z indeks klastrowany w tabela miejsce docelowe i wskazówka zamówienia jest używana do wskazania kolejności, tak aby optymalizator kwerendy można generować bardziej efektywnego planu kwerend.Wskazówki są sprawdzane w czasie wykonywania; Jeżeli strumień danych nie jest zgodna z określonym wskazówki, powstaje błąd.

Wytyczne te upewnij się, że sprzężenie kluczy są unikatowe i kolejność sortowania sortowania danych w źródło pliku odpowiada tabela miejsce docelowe.Zwiększona wydajność kwerendy, ponieważ sortowanie dodatkowe operacje nie są konieczne i kopie niepotrzebne dane nie są wymagane.W poniższym przykładzie użyto instrukcja korespondencji seryjnej do ładowanie zbiorcze danych z StockData.txt, plik prosty, do tabela miejsce docelowe dbo.Stock.Definiując ograniczenia na klucz podstawowy na StockName w tabela miejsce docelowe indeks klastrowany jest tworzony na podstawie kolumna używane do łączyć z źródło danych.Wskazówki zamówienia i unikatowe są stosowane do Stock kolumna w danych źródło, który mapuje do kolumna klucz indeks klastrowany w tabela miejsce docelowe.

Przed uruchomieniem w tym przykładzie należy utworzyć plik tekstowy o nazwie "StockData.txt" w folderze C:\SQLFiles\.Plik powinien mieć dwie kolumny danych oddzielone przecinkami.Na przykład można użyć następujących danych.

Alpine mountain bike,100

Brake set,22

Cushion,5

Następnie należy utworzyć plik formatu xml o nazwie "BulkloadFormatFile.xml" w folderze C:\SQLFiles\.Użyj następujących informacji.

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
    BULK 'C:\SQLFiles\StockData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000,
    ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO

Pomiary i diagnozowania SCALIĆ wydajności

Następujące funkcje są dostępne do pomiaru i diagnozowanie wydajności sprawozdań korespondencji seryjnej.

Użyj korespondencji seryjnej stmt licznika w sys.dm_exec_query_optimizer_info dynamicznego zarządzania numer optymalizacje kwerendy dla instrukcji korespondencji seryjnej.
Użyj merge_action_type atrybut w sys.dm_exec_plan_attributes funkcja dynamicznego zarządzania zwraca typ planu wykonania wyzwalacza jako wynik instrukcja korespondencji seryjnej.
Użyj śledzenia SQL zebrać dane dotyczące rozwiązywania problemów dla instrukcja korespondencji seryjnej w taki sam sposób, jak dla innych język edycji danych instrukcja (DML).Aby uzyskać więcej informacji, zobacz Wprowadzenie do śledzenia SQL.