Sdílet prostřednictvím

Použití dávkování ke zlepšení výkonu aplikace Azure SQL Database a azure SQL Managed Instance

  • Článek

Platí pro: Azure SQL Database Azure SQL Managed Instance

Dávkové operace do služby Azure SQL Database a spravované instance Azure SQL výrazně vylepšují výkon a škálovatelnost vašich aplikací. Aby bylo možné porozumět výhodám, první část tohoto článku obsahuje několik ukázkových výsledků testů, které porovnávají sekvenční a dávkové požadavky na databázi ve službě Azure SQL Database nebo ve službě Azure SQL Managed Instance. Zbývající část článku ukazuje techniky, scénáře a důležité informace, které vám pomůžou úspěšně používat dávkování ve vašich aplikacích Azure.

Proč je dávkování důležité pro Azure SQL Database a Azure SQL Managed Instance?

Dávkování volání do vzdálené služby je dobře známá strategie pro zvýšení výkonu a škálovatelnosti. Existují pevné náklady na zpracování pro všechny interakce se vzdálenou službou, jako je serializace, síťový přenos a deserializace. Balení mnoha samostatných transakcí do jedné dávky minimalizuje tyto náklady.

V tomto článku chceme prozkoumat různé strategie dávkování a scénáře. I když jsou tyto strategie také důležité pro místní aplikace, které používají SQL Server, existuje několik důvodů, proč zvýraznit použití dávek pro Azure SQL Database a Azure SQL Managed Instance:

  • Při přístupu ke službě Azure SQL Database a Azure SQL Managed Instance je potenciálně vyšší latence sítě, zejména pokud přistupujete ke službě Azure SQL Database nebo azure SQL Managed Instance mimo stejné datové centrum Microsoft Azure.
  • Víceklientní charakteristiky služby Azure SQL Database a Azure SQL Managed Instance znamenají, že efektivita vrstvy přístupu k datům koreluje s celkovou škálovatelností databáze. V reakci na využití nad rámec předdefinovaných kvót může Azure SQL Database a Azure SQL Managed Instance snížit propustnost nebo reagovat na výjimky omezování. Efektivita, jako je dávkování, umožňují před dosažením těchto limitů dělat více práce.
  • Dávkování je také efektivní pro architektury, které používají více databází (horizontální dělení). Efektivita interakce s každou jednotkou databáze je stále klíčovým faktorem celkové škálovatelnosti.

Jednou z výhod používání služby Azure SQL Database nebo Azure SQL Managed Instance je, že nemusíte spravovat servery, které hostují databázi. Tato spravovaná infrastruktura ale také znamená, že se musíte zamyslet nad optimalizacemi databáze. Už se nemůžete podívat, jak zlepšit hardware databáze nebo síťovou infrastrukturu. Microsoft Azure řídí tato prostředí. Hlavní oblastí, kterou můžete řídit, je způsob interakce aplikace se službou Azure SQL Database a službou Azure SQL Managed Instance. Dávkování je jednou z těchto optimalizací.

První část tohoto článku popisuje různé dávkové techniky pro aplikace .NET, které používají Azure SQL Database nebo Azure SQL Managed Instance. Poslední dvě části se týkají pokynů pro dávkování a scénářů.

Strategie dávkování

Poznámka k výsledkům časování v tomto článku

Poznámka:

Výsledky nejsou srovnávací testy, ale jsou určené k zobrazení relativního výkonu. Časování vychází z průměru alespoň 10 testovacích běhů. Operace se vloží do prázdné tabulky. Tyto testy byly měřeny před V12 a nemusí nutně odpovídat propustnosti, kterou můžete mít v databázi V12 pomocí nových úrovní služby DTU nebo úrovní služby virtuálních jader. Relativní výhoda techniky dávkování by měla být podobná.

Transakce

Zdá se, že začít kontrolu dávkování tím, že probírá transakce. Použití transakcí na straně klienta má ale malý dávkový efekt na straně serveru, který zlepšuje výkon. A transakce lze přidat pouze s několika řádky kódu, takže poskytují rychlý způsob, jak zlepšit výkon sekvenčních operací.

Podívejte se na následující kód jazyka C#, který obsahuje posloupnost operací vložení a aktualizace v jednoduché tabulce.

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)");

Následující ADO.NET kód tyto operace provede postupně.

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

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

Nejlepším způsobem, jak tento kód optimalizovat, je implementovat určitou formu dávkování těchto volání na straně klienta. Existuje však jednoduchý způsob, jak zvýšit výkon tohoto kódu jednoduše zabalením posloupnosti volání v transakci. Tady je stejný kód, který používá transakci.

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();
}

Transakce se ve skutečnosti používají v obou těchto příkladech. V prvním příkladu je každé individuální volání implicitní transakce. V druhém příkladu explicitní transakce zabalí všechna volání. Podle dokumentace k transakčnímu protokolu s předstihem pro zápis se záznamy protokolu vyprázdní na disk při potvrzení transakce. Takže zahrnutím více volání do transakce může zápis do transakčního protokolu zpozdit, dokud transakce nebude potvrzena. V důsledku toho povolíte dávkování pro zápisy do transakčního protokolu serveru.

Následující tabulka uvádí některé výsledky ad hoc testování. Testy provedly stejné sekvenční vložení s transakcemi i bez transakcí. Pro větší perspektivu první sada testů běžela vzdáleně z přenosného počítače do databáze v Microsoft Azure. Druhá sada testů se spustila z cloudové služby a databáze, která se nachází ve stejném datacentru Microsoft Azure (USA – západ). Následující tabulka uvádí dobu trvání v milisekundách sekvenčních vkládání s transakcemi a bez transakcí.

Místní prostředí do Azure:

Operace Žádná transakce (ms) Transakce (ms)
0 130 402
10 1208 1226
100 12662 10395
1000 128852 102917

Azure do Azure (stejné datové centrum):

Operace Žádná transakce (ms) Transakce (ms)
0 21 26
10 220 56
100 2145 341
1000 21479 2756

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

Na základě předchozích výsledků testu se zabalení jedné operace do transakce ve skutečnosti snižuje výkon. Když ale zvýšíte počet operací v rámci jedné transakce, bude zlepšení výkonu více označeno. Rozdíl v výkonu je také patrnější, když všechny operace probíhají v datacentru Microsoft Azure. Vyšší latence používání služby Azure SQL Database nebo Azure SQL Managed Instance mimo datacentrum Microsoft Azure zastíní zvýšení výkonu při používání transakcí.

I když využití transakcí může zvýšit výkon, pokračujte v sledování osvědčených postupů pro transakce a připojení. Ponechte transakci co nejkratší a po dokončení práce zavřete připojení k databázi. Příkaz using v předchozím příkladu zaručuje, že se připojení po dokončení následného bloku kódu zavře.

Předchozí příklad ukazuje, že můžete přidat místní transakci do libovolného ADO.NET kódu se dvěma řádky. Transakce nabízejí rychlý způsob, jak zlepšit výkon kódu, který provádí sekvenční operace vložení, aktualizace a odstranění. U nejrychlejšího výkonu ale zvažte další změnu kódu, abyste mohli využít dávkování na straně klienta, jako jsou parametry s hodnotou tabulky.

Další informace o transakcích v ADO.NET naleznete v tématu Místní transakce v ADO.NET.

Parametry vracející tabulku

Parametry s hodnotou tabulky podporují uživatelem definované typy tabulek jako parametry v příkazech Jazyka Transact-SQL, uložených procedurách a funkcích. Tato technika dávkování na straně klienta umožňuje odesílat více řádků dat v rámci parametru s hodnotou tabulky. Pokud chcete použít parametry s hodnotou tabulky, nejprve definujte typ tabulky. Následující příkaz Jazyka Transact-SQL vytvoří typ tabulky s názvem MyTableType.

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

V kódu vytvoříte tabulku DataTable se stejnými názvy a typy typu tabulky. Tuto tabulku DataTable předejte v parametru ve volání textového dotazu nebo uložené procedury. Následující příklad ukazuje tuto techniku:

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();
}

V předchozím příkladu objekt SqlCommand vloží řádky z parametru s hodnotou tabulky @TestTvp. Dříve vytvořený objekt DataTable je přiřazen k tomuto parametru pomocí SqlCommand.Parameters.Add metoda. Dávkování vkládání do jednoho volání výrazně zvyšuje výkon při sekvenčních vloženích.

Pokud chcete předchozí příklad ještě vylepšit, použijte místo textového příkazu uloženou proceduru. Následující příkaz Transact-SQL vytvoří uloženou proceduru, která přebírá SimpleTestTableType table-valued parametr.

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

Potom změňte deklaraci objektu SqlCommand v předchozím příkladu kódu na následující.

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

Ve většině případů mají parametry s hodnotou tabulky ekvivalentní nebo lepší výkon než jiné techniky dávkování. Parametry hodnot tabulky jsou často vhodnější, protože jsou flexibilnější než jiné možnosti. Například jiné techniky, jako je hromadné kopírování SQL, umožňují pouze vložení nových řádků. Pomocí parametrů s hodnotou tabulky ale můžete pomocí logiky v uložené proceduře určit, které řádky se aktualizují a které se vloží. Typ tabulky lze také upravit tak, aby obsahoval sloupec Operace, který označuje, zda má být zadaný řádek vložen, aktualizován nebo odstraněn.

Následující tabulka ukazuje výsledky ad hoc testu pro použití parametrů s hodnotou tabulky v milisekundách.

Operace Místní nasazení do Azure (ms) Stejné datové centrum Azure (ms)
0 124 32
10 131 25
100 338 51
1000 2615 382
10000 23830 3586

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

Výkon při dávkování je okamžitě zjevný. V předchozím sekvenčním testu trvalo 1000 operací 129 sekund mimo datacentrum a 21 sekund z datacentra. U parametrů s hodnotou tabulky ale operace 1000 zabírají pouze 2,6 sekundy mimo datacentrum a 0,4 sekundy v rámci datacentra.

Další informace o parametrech hodnot tabulky naleznete v tématu Parametry hodnot tabulky.

Hromadné kopírování SQL

Hromadné kopírování SQL je dalším způsobem, jak vložit velké objemy dat do cílové databáze. Aplikace .NET mohou k provádění hromadných operací vložení použít třídu SqlBulkCopy . SqlBulkCopy je podobný funkci jako nástroj příkazového řádku, Bcp.exe nebo příkaz Transact-SQL, BULK INSERT. Následující příklad kódu ukazuje, jak hromadně zkopírovat řádky ve zdrojové tabulce DataTable, table, do cílové tabulky 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);
    }
}

V některých případech se upřednostňují hromadné kopírování před parametry hodnot tabulky. Podívejte se na srovnávací tabulku parametrů hodnot tabulky a operace BULK INSERT v článku Parametry hodnot tabulky.

Následující výsledky ad hoc testu ukazují výkon dávkování s SqlBulkCopy v milisekundách.

Operace Místní nasazení do Azure (ms) Stejné datové centrum Azure (ms)
0 433 57
10 441 32
100 636 53
1000 2535 341
10000 21605 2737

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

V menších velikostech dávek parametry s hodnotou tabulky převýšily třídu SqlBulkCopy . SqlBulkCopy však pro testy 1 000 a 10 000 řádků provedl 12–31 % rychleji než parametry hodnot tabulky. Stejně jako parametry s hodnotami tabulky je SqlBulkCopy dobrou volbou pro dávkové vkládání, zejména v porovnání s výkonem nesádkových operací.

Další informace o hromadném kopírování v ADO.NET naleznete v tématu Operace hromadného kopírování.

Příkazy INSERT s parametrizovanými více řádky

Jednou z alternativ pro malé dávky je vytvoření velkého parametrizovaného příkazu INSERT, který vloží více řádků. Následující příklad kódu ukazuje tuto techniku.

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();
}

Tento příklad je určen k zobrazení základního konceptu. Realističtější scénář by prošel požadovanými entitami pro vytvoření řetězce dotazu a parametrů příkazů současně. Jste omezeni na celkem 2100 parametrů dotazu, takže tím omezíte celkový počet řádků, které lze tímto způsobem zpracovat.

Následující výsledky ad hoc testu ukazují výkon tohoto typu příkazu insert v milisekundách.

Operace Parametry hodnot tabulky (ms) Single-statement INSERT (ms)
0 32 20
10 30 25
100 33 51

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

Tento přístup může být pro dávky menší než 100 řádků o něco rychlejší. I když je zlepšení malé, je tato technika další možností, která by mohla dobře fungovat ve vašem konkrétním scénáři aplikace.

DataAdapter

DataAdapter třídy umožňuje upravit objekt DataSet a pak odeslat změny jako OPERACE INSERT, UPDATE a DELETE. Pokud tímto způsobem používáte DataAdapter , je důležité si uvědomit, že pro každou samostatnou operaci se provádějí samostatná volání. Pokud chcete zvýšit výkon, použijte Vlastnost UpdateBatchSize na počet operací, které by se měly dávkovat najednou. Další informace naleznete v tématu Provádění dávkových operací pomocí objektů DataAdapter.

Entity Framework

Entity Framework Core podporuje dávkování.

XML

Pro úplnost se domníváme, že je důležité mluvit o XML jako strategii dávkování. Použití XML však nemá žádné výhody oproti jiným metodám a několika nevýhodám. Přístup je podobný parametrům s hodnotou tabulky, ale soubor NEBO řetězec XML se předá uložené proceduře místo uživatelem definované tabulky. Uložená procedura analyzuje příkazy v uložené proceduře.

Tento přístup má několik nevýhod:

  • Práce s XML může být těžkopádná a náchylná k chybám.
  • Analýza XML v databázi může být náročná na procesor.
  • Ve většině případů je tato metoda pomalejší než parametry s hodnotou tabulky.

Z těchto důvodů se nedoporučuje používat XML pro dávkové dotazy.

Aspekty dávkování

Následující části obsahují další pokyny pro použití dávkování v aplikacích Azure SQL Database a Azure SQL Managed Instance.

Kompromisy

V závislosti na vaší architektuře může dávkování zahrnovat kompromis mezi výkonem a odolností. Představte si například scénář, ve kterém se vaše role neočekávaně vypne. Pokud ztratíte jeden řádek dat, dopad je menší než dopad ztráty velké dávky neodslaných řádků. Při ukládání řádků do vyrovnávací paměti před jejich odesláním do databáze v zadaném časovém intervalu je větší riziko.

Vzhledem k tomuto kompromisu vyhodnoťte typ operací, které dávkováte. Dávky agresivněji (větší dávky a delší časové intervaly) s daty, která jsou méně kritická.

Velikost dávky

V našich testech nebylo obvykle výhodou rozdělení velkých dávek na menší bloky. Ve skutečnosti toto dílčí dělení často vedlo k pomalejšímu výkonu než odeslání jedné velké dávky. Představte si například scénář, ve kterém chcete vložit 1000 řádků. Následující tabulka ukazuje, jak dlouho trvá použití parametrů hodnot tabulky k vložení 1 000 řádků při rozdělení do menších dávek.

Velikost dávky Iterace Parametry hodnot tabulky (ms)
1 000 0 347
500 2 355
100 10 465
50 20 630

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

Vidíte, že nejlepší výkon pro 1 000 řádků je odeslat všechny najednou. V jiných testech (zde se nezobrazuje) došlo k malému zvýšení výkonu, které přeruší dávku 1 0000 řádků do dvou dávek 5 000. Schéma tabulky pro tyto testy je ale relativně jednoduché, proto byste měli provést testy na konkrétních datech a velikostech dávek, abyste tyto závěry ověřili.

Dalším faktorem, který je potřeba vzít v úvahu, je to, že pokud se celková dávka změní na příliš velkou, může dojít k omezení služby Azure SQL Database nebo azure SQL Managed Instance a odmítnutí potvrzení dávky. Pokud chcete dosáhnout nejlepších výsledků, otestujte konkrétní scénář a zjistěte, jestli existuje ideální velikost dávky. Nakonfigurujte velikost dávky za běhu a povolte tak rychlé úpravy na základě výkonu nebo chyb.

Nakonec vyvažte velikost dávky s riziky spojenými s dávkováním. Pokud dojde k přechodným chybám nebo dojde k selhání role, zvažte důsledky opakování operace nebo ztráty dat v dávce.

Paralelní zpracování

Co když jste se rozhodli zmenšit velikost dávky, ale k provedení práce jste použili více vláken? Naše testy opět ukázaly, že několik menších vícevláknových dávek obvykle fungovalo hůře než jedna větší dávka. Následující test se pokusí vložit 1 000 řádků do jedné nebo více paralelních dávek. Tento test ukazuje, jak více souběžných dávek skutečně snížil výkon.

Velikost dávky [Iterace] Dvě vlákna (ms) Čtyři vlákna (ms) Šest vláken (ms)
1000 [1] 277 315 266
500 [2] 548 278 256
250 [4] 405 329 265
100 [10] 488 439 391

Poznámka:

Výsledky nejsou srovnávací testy. Podívejte se na poznámku o výsledcích časování v tomto článku.

Z důvodu snížení výkonu z důvodu paralelismu existuje několik možných důvodů:

  • Existuje více souběžných síťových volání místo jednoho.
  • Několik operací s jednou tabulkou může vést k kolizím a blokováním.
  • S multithreadingem jsou spojené režijní náklady.
  • Náklady na otevření více připojení převáží výhodu paralelního zpracování.

Pokud cílíte na různé tabulky nebo databáze, je možné u této strategie zobrazit určité zvýšení výkonu. Tento přístup by byl scénář horizontálního dělení databáze nebo federace. Horizontální dělení používá více databází a směruje různá data do každé databáze. Pokud každá malá dávka přejde do jiné databáze, může být provádění operací paralelnější. Zvýšení výkonu ale není dostatečně významné, aby se jako základ pro rozhodnutí o použití horizontálního dělení databáze ve vašem řešení nepoužít.

V některých návrzích může paralelní spouštění menších dávek vést ke zlepšení propustnosti požadavků v systému, který je zatížený. V tomto případě, i když je rychlejší zpracovat jednu větší dávku, zpracování více dávek paralelně může být efektivnější.

Pokud používáte paralelní spouštění, zvažte řízení maximálního počtu pracovních vláken. Menší číslo může mít za následek menší kolize a rychlejší dobu provádění. Zvažte také další zatížení, které se umístí do cílové databáze jak v připojeních, tak v transakcích.

Typické pokyny k výkonu databáze mají vliv také na dávkování. Například u tabulek s velkým primárním klíčem nebo mnoha neclusterovanými indexy se snižuje výkon vkládání.

Pokud parametry s hodnotou tabulky používají uloženou proceduru, můžete použít příkaz SET NOCOUNT ON na začátku procedury. Tento příkaz potlačí vrácení počtu ovlivněných řádků v postupu. V našich testech však použití funkce SET NOCOUNT ON nemělo žádný vliv nebo snížil výkon. Testovací uložená procedura byla jednoduchá pomocí jediného příkazu INSERT z parametru s hodnotou tabulky. Je možné, že složitější uložené procedury by z tohoto prohlášení mohly těžit. Nepředpokládáme ale, že přidání funkce SET NOCOUNT ON do uložené procedury automaticky zvyšuje výkon. Abyste pochopili účinek, otestujte uloženou proceduru pomocí příkazu SET NOCOUNT ON a bez příkazu SET NOCOUNT ON .

Scénáře dávkování

Následující části popisují, jak používat parametry s hodnotou tabulky ve třech scénářích aplikace. První scénář ukazuje, jak může spolupráce ukládání do vyrovnávací paměti a dávkování fungovat. Druhý scénář zlepšuje výkon prováděním operací s hlavními podrobnostmi v jednom volání uložené procedury. Konečný scénář ukazuje, jak použít parametry s hodnotou tabulky v operaci UPSERT.

Vyrovnávání

I když existují některé scénáře, které jsou zřejmé jako kandidáty na dávkování, existuje mnoho scénářů, které by mohly využít dávkování zpožděným zpracováním. Zpožděné zpracování ale také přináší větší riziko ztráty dat v případě neočekávaného selhání. Je důležité porozumět tomuto riziku a zvážit důsledky.

Představte si například webovou aplikaci, která sleduje historii navigace jednotlivých uživatelů. V každé žádosti o stránku může aplikace volat databázi, která zaznamená zobrazení stránky uživatele. Vyšší výkon a škálovatelnost je ale možné dosáhnout uložením navigačních aktivit uživatelů do vyrovnávací paměti a následným odesláním těchto dat do databáze v dávkách. Aktualizaci databáze můžete aktivovat uplynulým časem nebo velikostí vyrovnávací paměti. Pravidlo může například určit, že dávka by měla být zpracována po 20 sekundách nebo když vyrovnávací paměť dosáhne 1 000 položek.

Následující příklad kódu používá reaktivní rozšíření – Rx ke zpracování událostí vyrovnávací paměti vyvolaných monitorovací třídou. Po vyplnění vyrovnávací paměti nebo dosažení časového limitu se dávka uživatelských dat odešle do databáze s parametrem s hodnotou tabulky.

Následující třída NavHistoryData modeluje podrobnosti navigace uživatele. Obsahuje základní informace, jako je identifikátor uživatele, přístupová adresa URL a doba přístupu.

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; }
}

Třída NavHistoryDataMonitor je zodpovědná za ukládání uživatelských navigačních dat do databáze. Obsahuje metodu RecordUserNavigationEntry, která reaguje vyvoláním události OnAdded . Následující kód ukazuje logiku konstruktoru, která používá Rx k vytvoření pozorovatelné kolekce na základě události. Pak se přihlásí k odběru této pozorovatelné kolekce metodou Buffer. Přetížení určuje, že vyrovnávací paměť by měla být odeslána každých 20 sekund nebo 1000 položek.

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

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

Obslužná rutina převede všechny položky uložené do vyrovnávací paměti na typ s hodnotou tabulky a potom tento typ předá uložené proceduře, která zpracovává dávku. Následující kód ukazuje úplnou definici pro navHistoryDataEventArgs i NavHistoryDataMonitor třídy.

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);
    }

Obslužná rutina převede všechny položky uložené do vyrovnávací paměti na typ s hodnotou tabulky a potom tento typ předá uložené proceduře, která zpracovává dávku. Následující kód ukazuje úplnou definici pro navHistoryDataEventArgs i NavHistoryDataMonitor třídy.

    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();
        }
    }
}

Chcete-li použít tuto třídu ukládání do vyrovnávací paměti, aplikace vytvoří statický NavHistoryDataMonitor objektu. Pokaždé, když uživatel přistupuje na stránku, aplikace volá NavHistoryDataMonitor.RecordUserNavigationEntry metoda. Logika ukládání do vyrovnávací paměti se postará o odesílání těchto položek do databáze v dávkách.

Podrobnosti předlohy

Parametry hodnotné tabulkou jsou užitečné pro jednoduché scénáře INSERT. Dávkové vkládání, které zahrnují více než jednu tabulku, ale může být náročnější. Dobrým příkladem je scénář "master/detail". Hlavní tabulka identifikuje primární entitu. Jedna nebo více tabulek podrobností ukládá více dat o entitě. V tomto scénáři relace cizího klíče vynucuje vztah podrobností s jedinečnou hlavní entitou. Představte si zjednodušenou verzi tabulky PurchaseOrder a její přidružené tabulky OrderDetail. Následující transact-SQL vytvoří tabulku PurchaseOrder se čtyřmi sloupci: OrderID, OrderDate, CustomerID a 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ždá objednávka obsahuje jeden nebo více nákupů produktů. Tyto informace jsou zaznamenány v tabulce PurchaseOrderDetail. Následující transact-SQL vytvoří tabulku PurchaseOrderDetail s pěti sloupci: OrderID, OrderDetailID, ProductID, UnitPrice a 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 ))

Sloupec OrderID v tabulce PurchaseOrderDetail musí odkazovat na objednávku z tabulky PurchaseOrder. Následující definice cizího klíče vynucuje toto omezení.

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

Pokud chcete použít parametry s hodnotou tabulky, musíte mít pro každou cílovou tabulku jeden typ tabulky definovaný uživatelem.

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

Pak definujte uloženou proceduru, která přijímá tabulky těchto typů. Tento postup umožňuje aplikaci místně dávkovat sadu objednávek a podrobností objednávek v jednom volání. Následující transact-SQL poskytuje úplnou deklaraci uložené procedury pro tento příklad nákupní objednávky.

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

V tomto příkladu místně definovaná @IdentityLink tabulka ukládá skutečné hodnoty OrderID z nově vložených řádků. Tyto identifikátory objednávek se liší od dočasných hodnot OrderID v parametrech @orders s @details hodnotou tabulky. Z tohoto důvodu @IdentityLink tabulka pak propojí hodnoty OrderID z parametru @orders se skutečnými hodnotami OrderID pro nové řádky v tabulce PurchaseOrder. Po tomto kroku @IdentityLink může tabulka usnadnit vložení podrobností objednávky se skutečným ID objednávky, které splňuje omezení cizího klíče.

Tuto uloženou proceduru lze použít z kódu nebo z jiných volání jazyka Transact-SQL. Příklad kódu najdete v oddílu parametrů s hodnotami tabulky tohoto dokumentu. Následující transact-SQL ukazuje, jak volat 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

Toto řešení umožňuje každé dávce používat sadu hodnot OrderID, které začínají na 1. Tyto dočasné hodnoty OrderID popisují relace v dávce, ale skutečné hodnoty OrderID jsou určeny v době operace vložení. Stejné příkazy můžete spustit v předchozím příkladu opakovaně a generovat jedinečné objednávky v databázi. Z tohoto důvodu zvažte přidání další logiky kódu nebo databáze, která brání duplicitním objednávkám při použití této techniky dávkování.

Tento příklad ukazuje, že ještě složitější databázové operace, jako jsou operace s hlavními podrobnostmi, je možné dávkově dávkot pomocí parametrů s hodnotou tabulky.

UPSERT

Dalším scénářem dávkování je souběžná aktualizace existujících řádků a vkládání nových řádků. Tato operace se někdy označuje jako operace UPSERT (update + insert). Místo provádění samostatných volání insert a UPDATE může být příkaz MERGE vhodným nahrazením. Příkaz MERGE může provádět operace vložení i aktualizace v jednom volání. Mechanika uzamčení příkazu MERGE funguje jinak než samostatné příkazy INSERT a UPDATE. Před nasazením do produkčního prostředí otestujte konkrétní úlohy.

Parametry hodnot tabulky lze použít s příkazem MERGE k provádění aktualizací a vkládání. Představte si například zjednodušenou tabulku Employee, která obsahuje následující sloupce: 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 ))

V tomto příkladu můžete použít skutečnost, že SocialSecurityNumber je jedinečný k provedení sloučení více zaměstnanců. Nejprve vytvořte typ tabulky definované uživatelem:

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

Dále vytvořte uloženou proceduru nebo napište kód, který používá příkaz MERGE k provedení aktualizace a vložení. Následující příklad používá příkaz MERGE u parametru table-valued , @employeestypu EmployeeTableType. @employees Obsah tabulky se tady nezobrazuje.

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]);

Další informace najdete v dokumentaci a příklady příkazu MERGE. I když stejnou práci lze provést ve volání vícestupňové uložené procedury s samostatnými operacemi INSERT a UPDATE, příkaz MERGE je efektivnější. Kód databáze může také vytvořit volání Jazyka Transact-SQL, která používají příkaz MERGE přímo bez nutnosti dvou volání databáze pro INSERT a UPDATE.

Souhrn doporučení

Následující seznam obsahuje souhrn doporučení dávkování probíraných v tomto článku:

  • Použití ukládání do vyrovnávací paměti a dávkování ke zvýšení výkonu a škálovatelnosti aplikací Azure SQL Database a Azure SQL Managed Instance.
  • Seznamte se s kompromisy mezi dávkováním a ukládáním do vyrovnávací paměti a odolností. Během selhání role může riziko ztráty nezpracované dávky důležitých obchodních dat převažovat nad výkonem dávkování.
  • Pokuste se zachovat všechna volání databáze v rámci jednoho datacentra, aby se snížila latence.
  • Pokud zvolíte jednu metodu dávkování, parametry s hodnotou tabulky nabízejí nejlepší výkon a flexibilitu.
  • Pokud chcete dosáhnout nejrychlejšího výkonu vkládání, postupujte podle těchto obecných pokynů, ale otestujte váš scénář:
    • Pro < 100 řádků použijte jeden parametrizovaný příkaz INSERT.
    • Pro < 1 000 řádků použijte parametry hodnot tabulky.
    • Pro >= 1000 řádků použijte SqlBulkCopy.
  • Pro operace aktualizace a odstranění použijte parametry s hodnotami tabulky s logikou uložené procedury, která určuje správnou operaci na každém řádku v parametru tabulky.
  • Pokyny pro velikost dávky:
    • Používejte největší velikosti dávek, které mají smysl pro vaši aplikaci a obchodní požadavky.
    • Vyvažte výkon velkých dávek s riziky dočasných nebo katastrofických selhání. Jaký je výsledek opakování nebo ztráty dat v dávce?
    • Otestujte největší velikost dávky a ověřte, že ji Azure SQL Database nebo Azure SQL Managed Instance neodmítne.
    • Vytvořte nastavení konfigurace, které řídí dávkování, například velikost dávky nebo časové okno ukládání do vyrovnávací paměti. Tato nastavení poskytují flexibilitu. Chování dávkování v produkčním prostředí můžete změnit bez opětovného nasazení cloudové služby.
  • Vyhněte se paralelnímu provádění dávek, které pracují s jednou tabulkou v jedné databázi. Pokud se rozhodnete rozdělit jednu dávku mezi více pracovních vláken, spusťte testy a určete ideální počet vláken. Po nespecifikované prahové hodnotě sníží výkon více vláken a nezvýší ho.
  • Zvažte ukládání do vyrovnávací paměti velikosti a času jako způsob implementace dávkování pro další scénáře.

Další kroky

Tento článek se zaměřuje na to, jak může návrh databáze a techniky kódování související s dávkováním zlepšit výkon a škálovatelnost aplikace. Ale to je jen jeden faktor ve vaší celkové strategii. Další způsoby zlepšení výkonu a škálovatelnosti najdete v pokynech k výkonu databáze a informace o cenách a výkonu elastického fondu.