Jak używać przetwarzania wsadowego w celu poprawy wydajności usługi Azure SQL Database i usługi Azure SQL Managed Instance

Artykuł
06/02/2023

Dotyczy: Azure SQL Database Azure SQL Managed Instance

Operacje wsadowe do usług Azure SQL Database i Azure SQL Managed Instance znacznie zwiększają wydajność i skalowalność aplikacji. Aby zrozumieć korzyści, pierwsza część tego artykułu obejmuje kilka przykładowych wyników testów, które porównują sekwencyjne i wsadowe żądania do bazy danych w usłudze Azure SQL Database lub Azure SQL Managed Instance. W pozostałej części artykułu przedstawiono techniki, scenariusze i zagadnienia ułatwiające pomyślne przetwarzanie wsadowe w aplikacjach platformy Azure.

Dlaczego przetwarzanie wsadowe jest ważne dla usług Azure SQL Database i Azure SQL Managed Instance?

Wsadowe wywołania do usługi zdalnej to dobrze znana strategia zwiększania wydajności i skalowalności. Istnieją stałe koszty przetwarzania dla wszelkich interakcji z usługą zdalną, takich jak serializacja, transfer sieciowy i deserializacja. Pakowanie wielu oddzielnych transakcji w jednej partii minimalizuje te koszty.

W tym artykule chcemy przeanalizować różne strategie i scenariusze dzielenia na partie. Chociaż te strategie są również ważne w przypadku aplikacji lokalnych korzystających z programu SQL Server, istnieje kilka powodów, dla których wyróżnia się użycie przetwarzania wsadowego dla usług Azure SQL Database i Azure SQL Managed Instance:

Istnieje potencjalnie większe opóźnienie sieci podczas uzyskiwania dostępu do usług Azure SQL Database i Azure SQL Managed Instance, zwłaszcza jeśli uzyskujesz dostęp do usługi Azure SQL Database lub Azure SQL Managed Instance spoza tego samego centrum danych platformy Microsoft Azure.
Cechy wielodostępne usług Azure SQL Database i Azure SQL Managed Instance oznaczają, że wydajność warstwy dostępu do danych jest skorelowana z ogólną skalowalnością bazy danych. W odpowiedzi na użycie przekraczające wstępnie zdefiniowane limity przydziału usługa Azure SQL Database i usługa Azure SQL Managed Instance mogą zmniejszyć przepływność lub reagować przy użyciu wyjątków ograniczania przepustowości. Efektywność, taka jak przetwarzanie wsadowe, umożliwia wykonywanie większej ilości pracy przed osiągnięciem tych limitów.
Przetwarzanie wsadowe jest również skuteczne w przypadku architektur korzystających z wielu baz danych (fragmentowania). Wydajność interakcji z każdą jednostką bazy danych jest nadal kluczowym czynnikiem ogólnej skalowalności.

Jedną z zalet korzystania z usługi Azure SQL Database lub usługi Azure SQL Managed Instance jest to, że nie trzeba zarządzać serwerami hostujących bazę danych. Jednak ta infrastruktura zarządzana oznacza również, że trzeba inaczej myśleć o optymalizacji bazy danych. Nie można już patrzeć, aby ulepszyć sprzęt bazy danych lub infrastrukturę sieci. Platforma Microsoft Azure kontroluje te środowiska. Głównym obszarem, który można kontrolować, jest sposób interakcji aplikacji z usługami Azure SQL Database i Azure SQL Managed Instance. Przetwarzanie wsadowe jest jedną z tych optymalizacji.

W pierwszej części tego artykułu przedstawiono różne techniki dzielenia na partie dla aplikacji platformy .NET korzystających z usługi Azure SQL Database lub Azure SQL Managed Instance. W dwóch ostatnich sekcjach omówiono wskazówki i scenariusze dzielenia na partie.

Strategie dzielenia na partie

Uwaga dotycząca wyników chronometrażu w tym artykule

Uwaga

Wyniki nie są testami porównawczymi, ale mają pokazywać względną wydajność. Chronometraż jest oparty na średnio co najmniej 10 przebiegach testów. Operacje są wstawiane do pustej tabeli. Te testy zostały zmierzone przed V12 i niekoniecznie odpowiadają przepływności, którą można napotkać w bazie danych w wersji 12 przy użyciu nowych warstw usług DTU lub warstw usług rdzeni wirtualnych. Względna korzyść z techniki dzielenia na partie powinna być podobna.

Transakcje

Wydaje się dziwne, aby rozpocząć przegląd dzielenia na partie, omawiając transakcje. Jednak użycie transakcji po stronie klienta ma subtelny efekt dzielenia na partie po stronie serwera, który poprawia wydajność. Transakcje można dodawać tylko za pomocą kilku wierszy kodu, dzięki czemu zapewniają szybki sposób poprawy wydajności operacji sekwencyjnych.

Rozważ następujący kod w języku C#, który zawiera sekwencję operacji wstawiania i aktualizowania prostej tabeli.

List<string> dbOperations = new List<string>();
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 1");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 2");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 3");
dbOperations.Add("insert MyTable values ('new value',1)");
dbOperations.Add("insert MyTable values ('new value',2)");
dbOperations.Add("insert MyTable values ('new value',3)");

Poniższy kod ADO.NET sekwencyjnie wykonuje te operacje.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();

    foreach(string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn);
        cmd.ExecuteNonQuery();
    }
}

Najlepszym sposobem optymalizacji tego kodu jest zaimplementowanie jakiejś formy przetwarzania wsadowego po stronie klienta tych wywołań. Istnieje jednak prosty sposób zwiększenia wydajności tego kodu, po prostu opakowując sekwencję wywołań w transakcji. Oto ten sam kod, który używa transakcji.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();

    foreach (string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
        cmd.ExecuteNonQuery();
    }

    transaction.Commit();
}

Transakcje są rzeczywiście używane w obu tych przykładach. W pierwszym przykładzie każde wywołanie poszczególnych wywołań jest transakcją niejawną. W drugim przykładzie transakcja jawna opakowuje wszystkie wywołania. Zgodnie z dokumentacją dziennika transakcji z wyprzedzeniem zapisu rekordy dziennika są opróżniane na dysk po zatwierdzeniu transakcji. Dlatego przez uwzględnienie większej liczby wywołań w transakcji zapis w dzienniku transakcji może opóźnić do momentu zatwierdzenia transakcji. W efekcie włączasz przetwarzanie wsadowe dla zapisów w dzienniku transakcji serwera.

W poniższej tabeli przedstawiono niektóre wyniki testów ad hoc. Testy wykonały te same sekwencyjne operacje wstawiania z transakcjami i bez ich. Aby uzyskać większą perspektywę, pierwszy zestaw testów przebiegał zdalnie z laptopa do bazy danych na platformie Microsoft Azure. Drugi zestaw testów został uruchomiony z usługi w chmurze i bazy danych, która znajdowała się w tym samym centrum danych platformy Microsoft Azure (Zachodnie stany USA). W poniższej tabeli przedstawiono czas trwania w milisekundach sekwencyjnych wstawień z transakcjami i bez tych transakcji.

Lokalnie na platformę Azure:

Operacje	Brak transakcji (ms)	Transakcja (ms)
1	130	402
10	1208	1226
100	12662	10395
1000	128852	102917

Azure-Azure (to samo centrum danych):

Operacje	Brak transakcji (ms)	Transakcja (ms)
1	21	26
10	220	56
100	2145	341
1000	21479	2756

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

Na podstawie poprzednich wyników testu zawijanie pojedynczej operacji w transakcji rzeczywiście zmniejsza wydajność. Jednak w miarę zwiększania liczby operacji w ramach jednej transakcji poprawa wydajności staje się bardziej oznaczona. Różnica w wydajności jest również bardziej zauważalna, gdy wszystkie operacje występują w centrum danych platformy Microsoft Azure. Zwiększone opóźnienie korzystania z usługi Azure SQL Database lub azure SQL Managed Instance spoza centrum danych platformy Microsoft Azure przyćmiewa wzrost wydajności korzystania z transakcji.

Chociaż użycie transakcji może zwiększyć wydajność, nadal obserwuj najlepsze rozwiązania dotyczące transakcji i połączeń. Zachowaj możliwie najkrótszą transakcję i zamknij połączenie z bazą danych po zakończeniu pracy. Instrukcja using w poprzednim przykładzie zapewnia, że połączenie jest zamknięte po zakończeniu kolejnego bloku kodu.

W poprzednim przykładzie pokazano, że można dodać transakcję lokalną do dowolnego kodu ADO.NET z dwoma wierszami. Transakcje oferują szybki sposób poprawy wydajności kodu, który sprawia, że sekwencyjne operacje wstawiania, aktualizowania i usuwania. Jednak w celu uzyskania najszybszej wydajności rozważ dalsze zmianę kodu w celu skorzystania z przetwarzania wsadowego po stronie klienta, takiego jak parametry z wartością tabeli.

Aby uzyskać więcej informacji na temat transakcji w ADO.NET, zobacz Transakcje lokalne w ADO.NET.

Parametry z wartościami przechowywanymi w tabeli

Parametry wartości tabeli obsługują typy tabel zdefiniowane przez użytkownika jako parametry w instrukcjach Języka Transact-SQL, procedurach składowanych i funkcjach. Ta technika przetwarzania wsadowego po stronie klienta umożliwia wysyłanie wielu wierszy danych w parametrze wartości tabeli. Aby użyć parametrów wartości tabeli, najpierw zdefiniuj typ tabeli. Poniższa instrukcja Języka Transact-SQL tworzy typ tabeli o nazwie MyTableType.

    CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

W kodzie utworzysz tabelę DataTable o dokładnie takich samych nazwach i typach tabeli. Przekaż tę tabelę DataTable w parametrze w zapytaniu tekstowym lub wywołaniu procedury składowanej. W poniższym przykładzie przedstawiono tę technikę:

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
        });

    cmd.ExecuteNonQuery();
}

W poprzednim przykładzie obiekt SqlCommand wstawia wiersze z parametru wartości tabeli, @TestTvp. Wcześniej utworzony obiekt DataTable jest przypisywany do tego parametru za pomocą metody SqlCommand.Parameters.Add . Dzielenie wsadów w jednym wywołaniu znacznie zwiększa wydajność operacji wstawiania sekwencyjnego.

Aby ulepszyć poprzedni przykład, użyj procedury składowanej zamiast polecenia opartego na tekście. Następujące polecenie języka Transact-SQL tworzy procedurę składowaną, która przyjmuje parametr table-valued SimpleTestTableType .

CREATE PROCEDURE [dbo].[sp_InsertRows]
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num)
SELECT mytext, num FROM @TestTvp
END
GO

Następnie zmień deklarację obiektu SqlCommand w poprzednim przykładzie kodu na następujący.

SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;

W większości przypadków parametry wartości tabeli mają równoważną lub lepszą wydajność niż inne techniki dzielenia na partie. Parametry wartości tabeli są często preferowane, ponieważ są bardziej elastyczne niż inne opcje. Na przykład inne techniki, takie jak kopiowanie zbiorcze SQL, zezwalają tylko na wstawianie nowych wierszy. Jednak w przypadku parametrów o wartości tabeli można użyć logiki w procedurze składowanej, aby określić, które wiersze są aktualizacjami i które są wstawiane. Typ tabeli można również zmodyfikować tak, aby zawierał kolumnę "Operacja", która wskazuje, czy określony wiersz powinien zostać wstawiony, zaktualizowany lub usunięty.

W poniższej tabeli przedstawiono wyniki testu ad hoc dotyczące używania parametrów wartości tabeli w milisekundach.

Operacje	Lokalnie na platformę Azure (ms)	To samo centrum danych platformy Azure (ms)
1	124	32
10	131	25
100	338	51
1000	2615	382
10 000	23830	3586

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

Przyrost wydajności z przetwarzania wsadowego jest natychmiast widoczny. W poprzednim teście sekwencyjnym 1000 operacji trwało 129 sekund poza centrum danych i 21 sekund z centrum danych. Jednak w przypadku parametrów o wartości tabeli operacje 1000 zajmują tylko 2,6 sekundy poza centrum danych i 0,4 sekundy w centrum danych.

Aby uzyskać więcej informacji na temat parametrów wartości tabeli, zobacz Parametry wartości tabeli.

Kopiowanie zbiorcze SQL

Kopiowanie zbiorcze SQL to inny sposób wstawiania dużych ilości danych do docelowej bazy danych. Aplikacje .NET mogą używać klasy SqlBulkCopy do wykonywania operacji wstawiania zbiorczego. Narzędzie SqlBulkCopy jest podobne do narzędzia wiersza polecenia, Bcp.exe lub instrukcji Języka Transact-SQL, BULK INSERT. Poniższy przykład kodu przedstawia sposób zbiorczego kopiowania wierszy w źródłowej tabeli DataTable, tabeli do tabeli docelowej MyTable.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "MyTable";
        bulkCopy.ColumnMappings.Add("mytext", "mytext");
        bulkCopy.ColumnMappings.Add("num", "num");
        bulkCopy.WriteToServer(table);
    }
}

Istnieje kilka przypadków, w których kopiowanie zbiorcze jest preferowane dla parametrów wartości tabeli. Zapoznaj się z tabelą porównawczą parametrów tabela-wartośćd a operacjami BULK INSERT w artykule Table-Valued Parameters (Parametry z wartością tabeli).

Poniższe wyniki testu ad hoc pokazują wydajność dzielenia na partie za pomocą narzędzia SqlBulkCopy w milisekundach.

Operacje	Lokalnie na platformę Azure (ms)	To samo centrum danych platformy Azure (ms)
1	433	57
10	441	32
100	636	53
1000	2535	341
10 000	21605	2737

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

W mniejszych rozmiarach partii użyj parametrów o wartości tabeli przewyższa klasę SqlBulkCopy . Jednak narzędzie SqlBulkCopy wykonało 12–31% szybciej niż parametry w tabeli dla testów 1000 i 10 000 wierszy. Podobnie jak w przypadku parametrów wartości tabeli, narzędzie SqlBulkCopy jest dobrym rozwiązaniem w przypadku wsadowych wstawiania, zwłaszcza w porównaniu z wydajnością operacji niesadowanych.

Aby uzyskać więcej informacji na temat kopiowania zbiorczego w ADO.NET, zobacz Operacje kopiowania zbiorczego.

Instrukcje INSERT sparametryzowane w wielu wierszach

Jedną z alternatyw dla małych partii jest skonstruowanie dużej sparametryzowanej instrukcji INSERT, która wstawia wiele wierszy. Poniższy przykład kodu pokazuje tę technikę.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
        "VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)";

    SqlCommand cmd = new SqlCommand(insertCommand, connection);

    for (int i = 1; i <= 10; i += 2)
    {
        cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
        cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
    }

    cmd.ExecuteNonQuery();
}

W tym przykładzie przedstawiono podstawową koncepcję. Bardziej realistyczny scenariusz przeprowadziłby pętlę przez wymagane jednostki, aby skonstruować ciąg zapytania i parametry polecenia jednocześnie. W sumie 2100 parametrów zapytania ogranicza się do całkowitej liczby wierszy, które można przetworzyć w ten sposób.

Poniższe wyniki testu ad hoc pokazują wydajność tego typu instrukcji insert w milisekundach.

Operacje	Parametry wartości tabeli (ms)	Instrukcja INSERT (ms)
1	32	20
10	30	25
100	33	51

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

Takie podejście może być nieco szybsze w przypadku partii, które są mniejsze niż 100 wierszy. Mimo że poprawa jest niewielka, ta technika jest kolejną opcją, która może działać dobrze w konkretnym scenariuszu aplikacji.

DataAdapter

Klasa DataAdapter umożliwia modyfikowanie obiektu DataSet , a następnie przesyłanie zmian w operacjach INSERT, UPDATE i DELETE. Jeśli w ten sposób używasz elementu DataAdapter , należy pamiętać, że dla każdej odrębnej operacji są wykonywane oddzielne wywołania. Aby zwiększyć wydajność, użyj właściwości UpdateBatchSize do liczby operacji, które powinny być wsadowe naraz. Aby uzyskać więcej informacji, zobacz Wykonywanie operacji wsadowych przy użyciu elementów DataAdapters.

Entity Framework

Program Entity Framework Core obsługuje przetwarzanie wsadowe.

XML

W celu zapewnienia kompletności uważamy, że ważne jest, aby mówić o kodzie XML jako strategii dzielenia na partie. Jednak użycie kodu XML nie ma żadnych zalet w porównaniu z innymi metodami i kilkoma wadami. Podejście jest podobne do parametrów wartości tabeli, ale plik XML lub ciąg jest przekazywany do procedury składowanej zamiast tabeli zdefiniowanej przez użytkownika. Procedura składowana analizuje polecenia w procedurze składowanej.

Istnieje kilka wad tego podejścia:

Praca z kodem XML może być uciążliwa i podatna na błędy.
Analizowanie kodu XML w bazie danych może być intensywnie obciążające procesor CPU.
W większości przypadków ta metoda jest wolniejsza niż parametry wartości tabeli.

Z tych powodów użycie kodu XML dla zapytań wsadowych nie jest zalecane.

Zagadnienia dotyczące przetwarzania wsadowego

Poniższe sekcje zawierają więcej wskazówek dotyczących używania przetwarzania wsadowego w usłudze Azure SQL Database i aplikacjach usługi Azure SQL Managed Instance.

Kompromisy

W zależności od architektury przetwarzanie wsadowe może obejmować kompromis między wydajnością a odpornością. Rozważmy na przykład scenariusz, w którym rola nieoczekiwanie spada. Jeśli utracisz jeden wiersz danych, wpływ jest mniejszy niż wpływ utraty dużej partii niesubsubskrywanych wierszy. Istnieje większe ryzyko podczas buforowania wierszy przed wysłaniem ich do bazy danych w określonym przedziale czasu.

Z powodu tego kompromisu oceń typ operacji wsadowych. Usługa Batch bardziej agresywnie (większe partie i dłuższe okna czasowe) z danymi, które są mniej krytyczne.

Rozmiar partii

W naszych testach zwykle nie było żadnej przewagi przed podziałem dużych partii na mniejsze fragmenty. W rzeczywistości ta podział często powodowała niższą wydajność niż przesyłanie pojedynczej dużej partii. Rozważmy na przykład scenariusz, w którym chcesz wstawić 1000 wierszy. W poniższej tabeli pokazano, jak długo trwa użycie parametrów z wartością tabeli w celu wstawienia 1000 wierszy w przypadku dzielenia na mniejsze partie.

Rozmiar partii	Iteracje	Parametry wartości tabeli (ms)
1000	1	347
500	2	355
100	10	465
50	20	630

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

Widać, że najlepszą wydajnością dla 1000 wierszy jest przesłanie ich wszystkich jednocześnie. W innych testach (nie pokazano tutaj), nastąpił niewielki wzrost wydajności, aby przerwać partię 10000 wierszy w dwóch partiach 5000. Jednak schemat tabeli dla tych testów jest stosunkowo prosty, dlatego należy wykonać testy na określonych danych i rozmiarach partii, aby zweryfikować te wyniki.

Innym czynnikiem, który należy wziąć pod uwagę, jest to, że jeśli łączna partia stanie się zbyt duża, usługa Azure SQL Database lub usługa Azure SQL Managed Instance może ograniczyć przepustowość i odrzucić zatwierdzenie partii. Aby uzyskać najlepsze wyniki, przetestuj konkretny scenariusz, aby określić, czy istnieje idealny rozmiar partii. Umożliwia skonfigurowanie rozmiaru partii w czasie wykonywania w celu umożliwienia szybkich korekt na podstawie wydajności lub błędów.

Na koniec zrównoważ rozmiar partii z ryzykiem związanym z dzieleniem na partie. Jeśli występują błędy przejściowe lub rola ulegnie awarii, rozważ konsekwencje ponawiania próby wykonania operacji lub utraty danych w partii.

Przetwarzanie równoległe

Co zrobić, jeśli zastosowano podejście do zmniejszenia rozmiaru partii, ale użyto wielu wątków do wykonania pracy? Ponownie nasze testy wykazały, że kilka mniejszych wielowątkowych partii zwykle występowało gorzej niż jedna większa partia. Poniższy test próbuje wstawić 1000 wierszy w co najmniej jednej równoległej partii. W tym teście pokazano, jak bardziej równoczesne partie rzeczywiście zmniejszyły wydajność.

Rozmiar partii [Iteracje]	Dwa wątki (ms)	Cztery wątki (ms)	Sześć wątków (ms)
1000 [1]	277	315	266
500 [2]	548	278	256
250 [4]	405	329	265
100 [10]	488	439	391

Uwaga

Wyniki nie są testami porównawczymi. Zapoznaj się z notatką dotyczącą wyników chronometrażu w tym artykule.

Istnieje kilka potencjalnych powodów obniżenia wydajności ze względu na równoległość:

Istnieje wiele równoczesnych wywołań sieciowych zamiast jednego.
Wiele operacji względem pojedynczej tabeli może spowodować rywalizację i zablokowanie.
Istnieją obciążenia związane z wielowątkowymi.
Koszt otwierania wielu połączeń przewyższa korzyści wynikające z przetwarzania równoległego.

Jeśli celujesz w różne tabele lub bazy danych, możesz zobaczyć pewien wzrost wydajności w tej strategii. Fragmentowanie bazy danych lub federacje byłoby scenariuszem dla tego podejścia. Fragmentowanie używa wielu baz danych i kieruje różne dane do każdej bazy danych. Jeśli każda mała partia przejdzie do innej bazy danych, wykonywanie operacji równolegle może być bardziej wydajne. Jednak wzrost wydajności nie jest wystarczająco znaczący, aby użyć jako podstawy decyzji o użyciu fragmentowania bazy danych w rozwiązaniu.

W niektórych projektach równoległe wykonywanie mniejszych partii może spowodować zwiększenie przepływności żądań w systemie pod obciążeniem. W takim przypadku, mimo że proces pojedynczej większej partii jest szybszy, przetwarzanie wielu partii równolegle może być bardziej wydajne.

Jeśli używasz wykonywania równoległego, rozważ kontrolowanie maksymalnej liczby wątków roboczych. Mniejsza liczba może spowodować zmniejszenie rywalizacji i krótszy czas wykonywania. Należy również rozważyć dodatkowe obciążenie, które umieszcza w docelowej bazie danych zarówno w połączeniach, jak i transakcjach.

Typowe wskazówki dotyczące wydajności bazy danych mają również wpływ na partie. Na przykład wydajność wstawiania jest zmniejszana dla tabel, które mają duży klucz podstawowy lub wiele indeksów nieklastrowanych.

Jeśli parametry wartości tabeli używają procedury składowanej, możesz użyć polecenia SET NOCOUNT ON na początku procedury. Ta instrukcja pomija zwracanie liczby wierszy, których dotyczy problem w procedurze. Jednak w naszych testach użycie polecenia SET NOCOUNT ON nie miało wpływu ani nie zmniejszyło wydajności. Procedura składowana testu była prosta z pojedynczym poleceniem INSERT z parametru o wartości tabeli. Istnieje możliwość, że bardziej złożone procedury składowane skorzystają z tego oświadczenia. Nie zakładaj jednak, że dodanie funkcji SET NOCOUNT ON do procedury składowanej automatycznie poprawia wydajność. Aby zrozumieć efekt, przetestuj procedurę składowaną za pomocą instrukcji SET NOCOUNT ON i bez tej instrukcji.

Scenariusze przetwarzania wsadowego

W poniższych sekcjach opisano sposób używania parametrów wartości tabeli w trzech scenariuszach aplikacji. W pierwszym scenariuszu pokazano, jak buforowanie i przetwarzanie wsadowe może współdziałać ze sobą. Drugi scenariusz poprawia wydajność, wykonując operacje master-detail w jednym wywołaniu procedury składowanej. W ostatnim scenariuszu pokazano, jak używać parametrów wartości tabeli w operacji "UPSERT".

Buforowanie

Chociaż istnieją pewne scenariusze, które są oczywistym kandydatem do dzielenia na partie, istnieje wiele scenariuszy, które mogą wykorzystać przetwarzanie wsadowe przez przetwarzanie opóźnione. Jednak opóźnione przetwarzanie wiąże się również z większym ryzykiem utraty danych w przypadku nieoczekiwanego niepowodzenia. Ważne jest, aby zrozumieć to ryzyko i wziąć pod uwagę konsekwencje.

Rozważmy na przykład aplikację internetową, która śledzi historię nawigacji każdego użytkownika. Na każdym żądaniu strony aplikacja może wykonać wywołanie bazy danych w celu zarejestrowania widoku strony użytkownika. Jednak wydajność i skalowalność można osiągnąć przez buforowanie działań nawigacji użytkowników, a następnie wysyłanie tych danych do bazy danych w partiach. Aktualizację bazy danych można wyzwolić przez upływający czas i/lub rozmiar buforu. Na przykład reguła może określić, że partia powinna zostać przetworzona po 20 sekundach lub gdy bufor osiągnie 1000 elementów.

Poniższy przykład kodu używa reaktywnych rozszerzeń — Rx do przetwarzania zdarzeń buforowanych zgłaszanych przez klasę monitorowania. Po osiągnięciu buforu lub przekroczeniu limitu czasu partia danych użytkownika jest wysyłana do bazy danych z parametrem o wartości tabeli.

Poniższa klasa NavHistoryData modeluje szczegóły nawigacji użytkownika. Zawiera on podstawowe informacje, takie jak identyfikator użytkownika, dostęp do adresu URL i czas dostępu.

public class NavHistoryData
{
    public NavHistoryData(int userId, string url, DateTime accessTime)
    { UserId = userId; URL = url; AccessTime = accessTime; }
    public int UserId { get; set; }
    public string URL { get; set; }
    public DateTime AccessTime { get; set; }
}

Klasa NavHistoryDataMonitor jest odpowiedzialna za buforowanie danych nawigacji użytkownika do bazy danych. Zawiera metodę RecordUserNavigationEntry, która odpowiada przez wywołanie zdarzenia OnAdded . Poniższy kod przedstawia logikę konstruktora, która używa języka Rx do utworzenia widocznej kolekcji na podstawie zdarzenia. Następnie subskrybuje tę obserwowaną kolekcję za pomocą metody Buffer. Przeciążenie określa, że bufor powinien być wysyłany co 20 sekund lub 1000 wpisów.

public NavHistoryDataMonitor()
{
    var observableData =
        Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

    observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}

Procedura obsługi konwertuje wszystkie buforowane elementy na typ wartości tabeli, a następnie przekazuje ten typ do procedury składowanej, która przetwarza partię. Poniższy kod przedstawia pełną definicję klas NavHistoryDataEventArgs i NavHistoryDataMonitor.

public class NavHistoryDataEventArgs : System.EventArgs
{
    public NavHistoryDataEventArgs(NavHistoryData data) { Data = data; }
    public NavHistoryData Data { get; set; }
}

public class NavHistoryDataMonitor
{
    public event EventHandler<NavHistoryDataEventArgs> OnAdded;

    public NavHistoryDataMonitor()
    {
        var observableData =
            Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

        observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
    }

    public class NavHistoryDataEventArgs : System.EventArgs
    {
        if (OnAdded != null)
            OnAdded(this, new NavHistoryDataEventArgs(data));
    }

    protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
    {
        DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
        navHistoryBatch.Columns.Add("UserId", typeof(int));
        navHistoryBatch.Columns.Add("URL", typeof(string));
        navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
        foreach (EventPattern<NavHistoryDataEventArgs> item in items)
        {
            NavHistoryData data = item.EventArgs.Data;
            navHistoryBatch.Rows.Add(data.UserId, data.URL, data.AccessTime);
        }

        using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(
                new SqlParameter()
                {
                    ParameterName = "@NavHistoryBatch",
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "NavigationHistoryTableType",
                    Value = navHistoryBatch,
                });

            cmd.ExecuteNonQuery();
        }
    }
}

Aby użyć tej klasy buforowania, aplikacja tworzy statyczny obiekt NavHistoryDataMonitor. Za każdym razem, gdy użytkownik uzyskuje dostęp do strony, aplikacja wywołuje metodę NavHistoryDataMonitor.RecordUserNavigationEntry. Logika buforowania kontynuuje wysyłanie tych wpisów do bazy danych w partiach.

Szczegóły wzorca

Parametry wartości tabeli są przydatne w przypadku prostych scenariuszy INSERT. Jednak wstawianie wsadowe obejmujące więcej niż jedną tabelę może być trudniejsze. Dobrym przykładem jest scenariusz "master/detail". Tabela główna identyfikuje jednostkę podstawową. Co najmniej jedna tabela szczegółów przechowuje więcej danych dotyczących jednostki. W tym scenariuszu relacje kluczy obcych wymuszają relację szczegółów z unikatową jednostką główną. Rozważ uproszczoną wersję tabeli PurchaseOrder i skojarzoną z nią tabelę OrderDetail. Poniższy kod Transact-SQL tworzy tabelę PurchaseOrder z czterema kolumnami: OrderID, OrderDate, CustomerID i Status.

CREATE TABLE [dbo].[PurchaseOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_PurchaseOrder]
PRIMARY KEY CLUSTERED ( [OrderID] ASC ))

Każde zamówienie zawiera co najmniej jeden zakup produktu. Te informacje są przechwytywane w tabeli PurchaseOrderDetail. Poniższy kod Transact-SQL tworzy tabelę PurchaseOrderDetail z pięcioma kolumnami: OrderID, OrderDetailID, ProductID, UnitPrice i OrderQty.

CREATE TABLE [dbo].[PurchaseOrderDetail](
[OrderID] [int] NOT NULL,
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
CONSTRAINT [PrimaryKey_PurchaseOrderDetail] PRIMARY KEY CLUSTERED
( [OrderID] ASC, [OrderDetailID] ASC ))

Kolumna OrderID w tabeli PurchaseOrderDetail musi odwoływać się do zamówienia z tabeli PurchaseOrder. Poniższa definicja klucza obcego wymusza to ograniczenie.

ALTER TABLE [dbo].[PurchaseOrderDetail]  WITH CHECK ADD
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID])

Aby można było używać parametrów wartości tabeli, musisz mieć jeden typ tabeli zdefiniowanej przez użytkownika dla każdej tabeli docelowej.

CREATE TYPE PurchaseOrderTableType AS TABLE
( OrderID INT,
    OrderDate DATETIME,
    CustomerID INT,
    Status NVARCHAR(50) );
GO

CREATE TYPE PurchaseOrderDetailTableType AS TABLE
( OrderID INT,
    ProductID INT,
    UnitPrice MONEY,
    OrderQty SMALLINT );
GO

Następnie zdefiniuj procedurę składowaną, która akceptuje tabele tych typów. Ta procedura umożliwia aplikacji lokalne tworzenie partii zestawu zamówień i szczegółów zamówienia w jednym wywołaniu. Poniższy kod Transact-SQL zawiera kompletną deklarację procedury składowanej dla tego przykładu zamówienia zakupu.

CREATE PROCEDURE sp_InsertOrdersBatch (
@orders as PurchaseOrderTableType READONLY,
@details as PurchaseOrderDetailTableType READONLY )
AS
SET NOCOUNT ON;

-- Table that connects the order identifiers in the @orders
-- table with the actual order identifiers in the PurchaseOrder table
DECLARE @IdentityLink AS TABLE (
SubmittedKey int,
ActualKey int,
RowNumber int identity(1,1)
);

-- Add new orders to the PurchaseOrder table, storing the actual
-- order identifiers in the @IdentityLink table
INSERT INTO PurchaseOrder ([OrderDate], [CustomerID], [Status])
OUTPUT inserted.OrderID INTO @IdentityLink (ActualKey)
SELECT [OrderDate], [CustomerID], [Status] FROM @orders ORDER BY OrderID;

-- Match the passed-in order identifiers with the actual identifiers
-- and complete the @IdentityLink table for use with inserting the details
WITH OrderedRows As (
SELECT OrderID, ROW_NUMBER () OVER (ORDER BY OrderID) As RowNumber
FROM @orders
)
UPDATE @IdentityLink SET SubmittedKey = M.OrderID
FROM @IdentityLink L JOIN OrderedRows M ON L.RowNumber = M.RowNumber;

-- Insert the order details into the PurchaseOrderDetail table,
-- using the actual order identifiers of the master table, PurchaseOrder
INSERT INTO PurchaseOrderDetail (
[OrderID],
[ProductID],
[UnitPrice],
[OrderQty] )
SELECT L.ActualKey, D.ProductID, D.UnitPrice, D.OrderQty
FROM @details D
JOIN @IdentityLink L ON L.SubmittedKey = D.OrderID;
GO

W tym przykładzie lokalnie zdefiniowana @IdentityLink tabela przechowuje rzeczywiste wartości OrderID z nowo wstawionych wierszy. Te identyfikatory zamówień różnią się od tymczasowych wartości OrderID w @orders parametrach i @details z wartościami tabeli. Z tego powodu @IdentityLink tabela łączy wartości OrderID z parametru @orders z rzeczywistymi wartościami OrderID dla nowych wierszy w tabeli PurchaseOrder. Po wykonaniu @IdentityLink tego kroku tabela może ułatwić wstawianie szczegółów zamówienia z rzeczywistym identyfikatorem OrderID, który spełnia ograniczenie klucza obcego.

Ta procedura składowana może być używana z kodu lub z innych wywołań języka Transact-SQL. Zobacz sekcję parametry wartości tabeli tego artykułu, aby zapoznać się z przykładem kodu. W poniższym języku Transact-SQL pokazano, jak wywołać sp_InsertOrdersBatch.

declare @orders as PurchaseOrderTableType
declare @details as PurchaseOrderDetailTableType

INSERT @orders
([OrderID], [OrderDate], [CustomerID], [Status])
VALUES(1, '1/1/2013', 1125, 'Complete'),
(2, '1/13/2013', 348, 'Processing'),
(3, '1/12/2013', 2504, 'Shipped')

INSERT @details
([OrderID], [ProductID], [UnitPrice], [OrderQty])
VALUES(1, 10, $11.50, 1),
(1, 12, $1.58, 1),
(2, 23, $2.57, 2),
(3, 4, $10.00, 1)

exec sp_InsertOrdersBatch @orders, @details

To rozwiązanie umożliwia każdej partii używanie zestawu wartości OrderID, które zaczynają się od 1. Te tymczasowe wartości OrderID opisują relacje w partii, ale rzeczywiste wartości OrderID są określane w czasie operacji wstawiania. W poprzednim przykładzie można wielokrotnie uruchamiać te same instrukcje i generować unikatowe zamówienia w bazie danych. Z tego powodu rozważ dodanie większej liczby kodu lub logiki bazy danych, które uniemożliwia zduplikowane zamówienia podczas korzystania z tej techniki dzielenia na partie.

W tym przykładzie pokazano, że jeszcze bardziej złożone operacje bazy danych, takie jak operacje master-detail, mogą być wsadowe przy użyciu parametrów wartości tabeli.

UPSERT

Inny scenariusz dzielenia na partie obejmuje jednoczesne aktualizowanie istniejących wierszy i wstawianie nowych wierszy. Ta operacja jest czasami nazywana operacją "UPSERT" (update + insert). Zamiast wykonywać oddzielne wywołania metody INSERT i UPDATE, instrukcja MERGE może być odpowiednim zamiennikiem. Instrukcja MERGE może wykonywać operacje wstawiania i aktualizowania w jednym wywołaniu. Mechanika blokowania instrukcji MERGE różni się od oddzielnych instrukcji INSERT i UPDATE. Przetestuj określone obciążenia przed wdrożeniem w środowisku produkcyjnym.

Parametry wartości tabeli mogą być używane z instrukcją MERGE do wykonywania aktualizacji i wstawiania. Rozważmy na przykład uproszczoną tabelę Employee zawierającą następujące kolumny: EmployeeID, FirstName, LastName, SocialSecurityNumber:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[SocialSecurityNumber] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_Employee] PRIMARY KEY CLUSTERED
([EmployeeID] ASC ))

W tym przykładzie można użyć faktu, że SocialSecurityNumber jest unikatowy do wykonania scalania wielu pracowników. Najpierw utwórz typ tabeli zdefiniowanej przez użytkownika:

CREATE TYPE EmployeeTableType AS TABLE
( Employee_ID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    SocialSecurityNumber NVARCHAR(50) );
GO

Następnie utwórz procedurę składowaną lub napisz kod, który używa instrukcji MERGE do wykonania aktualizacji i wstawienia. W poniższym przykładzie użyto instrukcji MERGE dla parametru o wartości tabeli , @employeestypu EmployeeTableType. @employees Zawartość tabeli nie jest tutaj wyświetlana.

MERGE Employee AS target
USING (SELECT [FirstName], [LastName], [SocialSecurityNumber] FROM @employees)
AS source ([FirstName], [LastName], [SocialSecurityNumber])
ON (target.[SocialSecurityNumber] = source.[SocialSecurityNumber])
WHEN MATCHED THEN
UPDATE SET
target.FirstName = source.FirstName,
target.LastName = source.LastName
WHEN NOT MATCHED THEN
    INSERT ([FirstName], [LastName], [SocialSecurityNumber])
    VALUES (source.[FirstName], source.[LastName], source.[SocialSecurityNumber]);

Aby uzyskać więcej informacji, zobacz dokumentację i przykłady instrukcji MERGE. Mimo że tę samą pracę można wykonać w wieloetapowym wywołaniu procedury składowanej z oddzielnymi operacjami INSERT i UPDATE, instrukcja MERGE jest wydajniejsza. Kod bazy danych może również tworzyć wywołania języka Transact-SQL, które używają instrukcji MERGE bezpośrednio bez konieczności stosowania dwóch wywołań bazy danych dla instrukcji INSERT i UPDATE.

Podsumowanie zaleceń

Poniższa lista zawiera podsumowanie zaleceń dotyczących dzielenia na partie omówionych w tym artykule:

Użyj buforowania i dzielenia na partie, aby zwiększyć wydajność i skalowalność aplikacji usługi Azure SQL Database i azure SQL Managed Instance.
Omówienie kompromisów między dzieleniem na partie/buforowaniem i odpornością. Podczas awarii roli ryzyko utraty nieprzetworzonej partii danych krytycznych dla działania firmy może przeważyć nad korzyścią z wydajności przetwarzania wsadowego.
Spróbuj zachować wszystkie wywołania bazy danych w jednym centrum danych, aby zmniejszyć opóźnienie.
Jeśli wybierzesz pojedynczą technikę przetwarzania wsadowego, parametry z wartością tabeli oferują najlepszą wydajność i elastyczność.
Aby uzyskać najszybszą wydajność wstawiania, postępuj zgodnie z tymi ogólnymi wytycznymi, ale przetestuj swój scenariusz:
- W przypadku < 100 wierszy użyj jednego sparametryzowanego polecenia INSERT.
- W przypadku < 1000 wierszy użyj parametrów wartości tabeli.
- W przypadku >= 1000 wierszy użyj narzędzia SqlBulkCopy.
W przypadku operacji aktualizacji i usuwania należy użyć parametrów wartości tabeli z logiką procedury składowanej, która określa poprawną operację w każdym wierszu w parametrze tabeli.
Wskazówki dotyczące rozmiaru partii:
- Użyj największych rozmiarów partii, które mają sens dla aplikacji i wymagań biznesowych.
- Zrównoważyć wzrost wydajności dużych partii z ryzykiem tymczasowych lub katastroficznych awarii. Jaka jest konsekwencja ponawiania prób lub utraty danych w partii?
- Przetestuj największy rozmiar partii, aby sprawdzić, czy usługa Azure SQL Database lub Azure SQL Managed Instance nie odrzuca jej.
- Utwórz ustawienia konfiguracji kontrolujące przetwarzanie wsadowe, takie jak rozmiar partii lub przedział czasu buforowania. Te ustawienia zapewniają elastyczność. Zachowanie przetwarzania wsadowego w środowisku produkcyjnym można zmienić bez ponownego wdrażania usługi w chmurze.
Unikaj równoległego wykonywania partii, które działają w jednej tabeli w jednej bazie danych. Jeśli zdecydujesz się podzielić jedną partię między wiele wątków roboczych, uruchom testy w celu określenia idealnej liczby wątków. Po nieokreślonym progu więcej wątków zmniejszy wydajność, a nie zwiększy jej.
Rozważ buforowanie rozmiaru i czasu w celu zaimplementowania przetwarzania wsadowego w celu uzyskania większej liczby scenariuszy.

Następne kroki

W tym artykule skoncentrowano się na tym, jak techniki projektowania i kodowania bazy danych związane z dzieleniem na partie mogą zwiększyć wydajność i skalowalność aplikacji. Ale jest to tylko jeden czynnik w ogólnej strategii. Aby uzyskać więcej sposobów poprawy wydajności i skalowalności, zobacz Wskazówki dotyczące wydajności bazy danych oraz Zagadnienia dotyczące cen i wydajności dla elastycznej puli.

Udostępnij za pośrednictwem

Jak używać przetwarzania wsadowego w celu poprawy wydajności usługi Azure SQL Database i usługi Azure SQL Managed Instance

Dlaczego przetwarzanie wsadowe jest ważne dla usług Azure SQL Database i Azure SQL Managed Instance?

Strategie dzielenia na partie

Uwaga dotycząca wyników chronometrażu w tym artykule

Transakcje

Parametry z wartościami przechowywanymi w tabeli

Kopiowanie zbiorcze SQL

Instrukcje INSERT sparametryzowane w wielu wierszach

DataAdapter

Entity Framework

XML

Zagadnienia dotyczące przetwarzania wsadowego

Kompromisy

Rozmiar partii

Przetwarzanie równoległe

Scenariusze przetwarzania wsadowego

Buforowanie

Szczegóły wzorca

UPSERT

Podsumowanie zaleceń

Następne kroki

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

Jak używać przetwarzania wsadowego w celu poprawy wydajności usługi Azure SQL Database i usługi Azure SQL Managed Instance

Dlaczego przetwarzanie wsadowe jest ważne dla usług Azure SQL Database i Azure SQL Managed Instance?

Strategie dzielenia na partie

Uwaga dotycząca wyników chronometrażu w tym artykule

Transakcje

Parametry z wartościami przechowywanymi w tabeli

Kopiowanie zbiorcze SQL

Instrukcje INSERT sparametryzowane w wielu wierszach

DataAdapter

Entity Framework

XML

Zagadnienia dotyczące przetwarzania wsadowego

Kompromisy

Rozmiar partii

Przetwarzanie równoległe

Powiązane czynniki wydajności

Scenariusze przetwarzania wsadowego

Buforowanie

Szczegóły wzorca

UPSERT

Podsumowanie zaleceń

Następne kroki

Opinia

Dodatkowe zasoby