Który z nich jest bardziej wydajny: wybierz z połączonego serwera lub wstaw do połączonego serwera?

32

Załóżmy, że muszę eksportować dane z jednego serwera na drugi (przez połączone serwery). Które stwierdzenie będzie bardziej wydajne?

Wykonanie na serwerze źródłowym:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Lub wykonywanie na serwerze docelowym:

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Który będzie szybszy i zużyje w sumie mniej zasobów (zarówno serwer źródłowy, jak i docelowy)? Oba serwery to SQL Server 2005.

— Guillermo Gutiérrez
źródło

29

Załóżmy, że muszę eksportować dane z jednego serwera na inny.

Najlepiej jest użyć

JEŚLI chcesz, aby wszystkie dane korzystały z funkcji Kopia zapasowa / przywracanie; BCP OUT i BCP IN lub SSIS
JEŚLI chcesz podzbioru danych (tylko niektóre tabele) użyj SSIS lub BCP OUT i BCP IN

Aby przenieść dane, w zależności od ilości / rozmiaru danych i n / w przepustowości, serwer połączony zabije wydajność.

Wykonanie na serwerze źródłowym Czy wykonanie na serwerze docelowym - Który będzie szybszy i zużyje mniej zasobów (łącznie na serwerze źródłowym i docelowym)?

- Wykonywanie na serwerze źródłowym:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Nazywa się to PUSHING Data podczas wykonywania zapytania na serwerze źródłowym i wypychania danych na serwer docelowy. To będzie kosztowna operacja.

--- wykonywanie na serwerze docelowym

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Nazywa się to PULLING Data podczas wykonywania zapytania na serwerze docelowym i pobierania danych z serwera źródłowego. Będzie to o wiele szybsze i mniej zasobochłonne w porównaniu do poprzedniego (w zależności od ilości pobieranych danych).

W przypadku metody pull, za pomocą SQL Profiler zobaczysz, że pojedyncza instrukcja SQL jest wykonywana na połączonym serwerze (serwerze źródłowym), a zestaw wyników jest przenoszony z serwera źródłowego na serwer docelowy, co stanowi ogromny wzrost wydajności w porównaniu z PUSH metoda.

Inną kwestią, na którą należy zwrócić uwagę, jest:

Pomiędzy połączonym serwerem (w 4-częściowej konwencji nazewnictwa użyto servername.databasename.schema.tablename aka Distributed Queries) i OPENQUERY, na ogół OPENQUERY będzie szybki. Czemu ?

W przypadku serwera połączonego - Optymalizator zapytań tworzy plan wykonania, analizując nomenklaturę zapytań i dzieląc ją na zapytania zdalne i lokalne. Zapytania lokalne są wykonywane lokalnie, a dane do zapytań zdalnych są gromadzone ze zdalnych serwerów, lokalnie szorowane, łączone razem i prezentowane użytkownikowi końcowemu jako pojedynczy zestaw rekordów.

Dla OPENQUERY - Wykonuje określone zapytanie tranzytowe na określonym połączonym serwerze. SQL Server wysyła zapytania tranzytowe jako nieprzetłumaczone ciągi zapytań do źródła danych OLE DB. W związku z tym SQL nie zastosuje żadnej logiki w zapytaniu i nie będzie próbował oszacować, co zrobiłoby to zapytanie, po prostu przekaże określone zapytanie tak, jak jest w docelowym połączonym serwerze. Otwarte zapytania są przydatne, gdy nie odwołujesz się do wielu serwerów w jednym zapytaniu. Ogólnie jest szybki, ponieważ SQL nie dzieli go na wiele operacji i nie wykonuje żadnych lokalnych działań na otrzymanych danych wyjściowych.

Doskonałe odniesienia do czytania:

— Kin Shah
źródło

8

Jak mierzysz wydajność? Który będzie szybszy? Który zużyje mniej zasobów na celu? w źródle? Ile wierszy i jakie typy danych są kolumnami w tych wierszach? Czy na pewno możesz wykonać TVF za pośrednictwem połączonego serwera (czy docelowy SQL 2008 lub nowszy?) ? W jaki sposób zapewniasz migrację tych danych 1: 1, jeśli pobierasz z TVF?

Bez tych pytań ...

Aktualizacja 1

Wygląda na to, że szukasz ETL (Extract-Transform-Load). Polecam SSIS (SQL Server Integration Services), za pomocą którego możesz pobrać dane ze źródła, zastosować potrzebne transformacje, a następnie załadować je do celu. Wygląda na to, że byłby to dość prosty pakiet (w zależności od transformacji).

Konwencjonalna wiedza głosi, że podejście z połączonym serwerem przejdzie do łącza, pobierze dane do serwera lokalnego, a następnie zastosuje dowolną logikę (filtry, sprzężenia itp.) Na serwerze lokalnym. Pobieranie danych na połączonym serwerze wiąże się z pewnym nakładem, ale większość przetwarzania będzie obsługiwana lokalnie.

Metoda OPENQUERY umieści przetwarzanie na serwerze zdalnym, a serwer „odfiltruje wyniki”.

Wygląda na to, że nawet gdybyś mógł uruchomić TVF przez połączony serwer, dostaniesz najgorsze z obu światów, przetwarzając zdalnie i przetwarzając lokalnie (zakładając, że masz dodatkową logikę do zastosowania na zestawie).

W zależności od tego, jak zdecydujesz się iść naprzód, przyjrzę się OPENQUERYrównież sposobowi zbiorczego importu / eksportu danych.

Powiedziawszy to wszystko ...

Jeśli zarówno źródło, jak i cel w SQL Server (a celem nie jest wersja niższa), dlaczego nie wykonać kopii zapasowej i przywrócić dane? To byłaby prawdziwa migracja danych. Oto kod dla ciebie.

BACKUP DATABASE <DatabaseName, sysname, DatabaseName>
TO DISK=N'<backup_location, varchar, BackupLocation>.bak'
WITH INIT, FORMAT, COMPRESSION, COPY_ONLY

RESTORE DATABASE <NewDatabaseName, sysname, NewDatabaseName>
FROM DISK = N'<backup_location, varchar, BackupLocation>\
    <DatabaseName, sysname, DatabaseName>.bak'
WITH 
    MOVE '<DataFileName, sysname, DataFileName>' TO '<DataMDFPath, nvarchar(600), DataMDFPath>',
    MOVE '<LogFilePath, sysname, LogFilePath>' TO '<LogLDFPath, nvarchar(600), LogLDFPath>',
    REPLACE;

Możesz odnieść się do tej odpowiedzi na temat korzystania z szablonów w SSMS.

— swasheck
źródło