Dela via

Så här använder du batchbearbetning för att förbättra prestanda för Azure SQL Database- och Azure SQL Managed Instance-program

  • Artikel

Gäller för:Azure SQL DatabaseAzure SQL Managed Instance

Batchbearbetningsåtgärder till Azure SQL Database och Azure SQL Managed Instance förbättrar avsevärt prestanda och skalbarhet för dina program. För att förstå fördelarna omfattar den första delen av den här artikeln några exempeltestresultat som jämför sekventiella och batchbaserade begäranden med en databas i Azure SQL Database eller Azure SQL Managed Instance. Resten av artikeln visar tekniker, scenarier och överväganden som hjälper dig att använda batchbearbetning i dina Azure-program.

Varför är batchbearbetning viktigt för Azure SQL Database och Azure SQL Managed Instance?

Batchanrop till en fjärrtjänst är en välkänd strategi för att öka prestanda och skalbarhet. Det finns fasta bearbetningskostnader för alla interaktioner med en fjärrtjänst, till exempel serialisering, nätverksöverföring och deserialisering. Om du paketerar många separata transaktioner i en enda batch minimeras dessa kostnader.

I den här artikeln vill vi undersöka olika batchbearbetningsstrategier och scenarier. Även om de här strategierna också är viktiga för lokala program som använder SQL Server finns det flera orsaker till att du markerar användningen av batchbearbetning för Azure SQL Database och Azure SQL Managed Instance:

  • Det finns potentiellt större nätverksfördröjning vid åtkomst till Azure SQL Database och Azure SQL Managed Instance, särskilt om du har åtkomst till Azure SQL Database eller Azure SQL Managed Instance utanför samma Microsoft Azure-datacenter.
  • Egenskaperna för flera klientorganisationer i Azure SQL Database och Azure SQL Managed Instance innebär att effektiviteten i dataåtkomstskiktet korrelerar med databasens övergripande skalbarhet. Som svar på användning utöver fördefinierade kvoter kan Azure SQL Database och Azure SQL Managed Instance minska dataflödet eller svara med begränsningsfel. Effektivitet, till exempel batchbearbetning, gör att du kan utföra mer arbete innan du når dessa gränser.
  • Batchbearbetning är också effektivt för arkitekturer som använder flera databaser (horisontell partitionering). Effektiviteten i din interaktion med varje databasenhet är fortfarande en viktig faktor i din övergripande skalbarhet.

En av fördelarna med att använda Azure SQL Database eller Azure SQL Managed Instance är att du inte behöver hantera de servrar som är värdar för databasen. Men den här hanterade infrastrukturen innebär också att du måste tänka annorlunda på databasoptimeringar. Du kan inte längre försöka förbättra databasmaskinvaran eller nätverksinfrastrukturen. Microsoft Azure styr dessa miljöer. Det viktigaste området som du kan styra är hur ditt program interagerar med Azure SQL Database och Azure SQL Managed Instance. Batchbearbetning är en av dessa optimeringar.

Den första delen av den här artikeln undersöker olika batchbearbetningstekniker för .NET-program som använder Azure SQL Database eller Azure SQL Managed Instance. De två sista avsnitten beskriver riktlinjer och scenarier för batchbearbetning.

Batchbearbetningsstrategier

Obs! Om tidsschemaresultat i den här artikeln

Kommentar

Resultaten är inte benchmarks men är avsedda att visa relativa prestanda. Tidsinställningar baseras på ett genomsnitt på minst 10 testkörningar. Åtgärder infogas i en tom tabell. Dessa tester mättes före V12 och motsvarar inte nödvändigtvis det dataflöde som du kan uppleva i en V12-databas med hjälp av de nya DTU-tjänstnivåerna eller vCore-tjänstnivåerna. Den relativa fördelen med batchbearbetningstekniken bör vara liknande.

Transaktioner

Det verkar konstigt att påbörja en granskning av batchbearbetning genom att diskutera transaktioner. Men användningen av transaktioner på klientsidan har en diskret batchbearbetningseffekt på serversidan som förbättrar prestandan. Och transaktioner kan läggas till med bara några rader kod, så de ger ett snabbt sätt att förbättra prestanda för sekventiella åtgärder.

Överväg följande C#-kod som innehåller en sekvens med infognings- och uppdateringsåtgärder i en enkel tabell.

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

Följande ADO.NET kod utför följande åtgärder sekventiellt.

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

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

Det bästa sättet att optimera den här koden är att implementera någon form av batchbearbetning på klientsidan av dessa anrop. Men det finns ett enkelt sätt att öka kodens prestanda genom att helt enkelt omsluta sekvensen med anrop i en transaktion. Här är samma kod som använder en transaktion.

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

Transaktioner används faktiskt i båda dessa exempel. I det första exemplet är varje enskilt anrop en implicit transaktion. I det andra exemplet omsluter en explicit transaktion alla anrop. I dokumentationen för transaktionsloggen write-ahead töms loggposter till disken när transaktionen checkas in. Genom att inkludera fler anrop i en transaktion kan skrivning till transaktionsloggen fördröjas tills transaktionen har checkats in. I själva verket aktiverar du batchbearbetning för skrivningar till serverns transaktionslogg.

I följande tabell visas några ad hoc-testresultat. Testerna utförde samma sekventiella infogningar med och utan transaktioner. För mer perspektiv kördes den första uppsättningen tester via fjärranslutning från en bärbar dator till databasen i Microsoft Azure. Den andra uppsättningen tester kördes från en molntjänst och databas som båda fanns i samma Microsoft Azure-datacenter (USA, västra). I följande tabell visas varaktigheten i millisekunder för sekventiella infogningar med och utan transaktioner.

Lokalt till Azure:

Drift Ingen transaktion (ms) Transaktion (ms)
1 130 402
10 1208 1226
100 12662 10395
1000 128852 102917

Azure till Azure (samma datacenter):

Drift Ingen transaktion (ms) Transaktion (ms)
1 21 26
10 220 56
100 2145 341
1000 21479 2756

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

Baserat på tidigare testresultat minskar omslutningen av en enskild åtgärd i en transaktion faktiskt prestandan. Men när du ökar antalet åtgärder inom en enda transaktion blir prestandaförbättringen mer markerad. Prestandaskillnaden är också mer märkbar när alla åtgärder utförs i Microsoft Azure-datacentret. Den ökade svarstiden med att använda Azure SQL Database eller Azure SQL Managed Instance utanför Microsoft Azure-datacentret överskuggar prestandavinsten med att använda transaktioner.

Även om användningen av transaktioner kan öka prestandan fortsätter du att följa metodtipsen för transaktioner och anslutningar. Håll transaktionen så kort som möjligt och stäng databasanslutningen när arbetet har slutförts. Instruktionen using i föregående exempel försäkrar att anslutningen stängs när det efterföljande kodblocket slutförs.

I föregående exempel visas att du kan lägga till en lokal transaktion i valfri ADO.NET kod med två rader. Transaktioner ger ett snabbt sätt att förbättra prestanda för kod som utför sekventiella åtgärder för att infoga, uppdatera och ta bort. För den snabbaste prestandan bör du dock överväga att ändra koden ytterligare för att dra nytta av batchbearbetning på klientsidan, till exempel tabellvärdesparametrar.

Mer information om transaktioner i ADO.NET finns i Lokala transaktioner i ADO.NET.

Tabellvärdesparametrar

Tabellvärdesparametrar stöder användardefinierade tabelltyper som parametrar i Transact-SQL-instruktioner, lagrade procedurer och funktioner. Med den här batchtekniken på klientsidan kan du skicka flera rader med data i parametern table-valued. Om du vill använda tabellvärdesparametrar måste du först definiera en tabelltyp. Följande Transact-SQL-uttryck skapar en tabelltyp med namnet MyTableType.

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

I kod skapar du en DataTable med exakt samma namn och typer av tabelltyp. Skicka datatabellen i en parameter i en textfråga eller ett lagrat proceduranrop. Följande exempel visar den här tekniken:

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

I föregående exempel infogar SqlCommand-objektet rader från en tabellvärdesparameter, @TestTvp. Det tidigare skapade DataTable-objektet tilldelas den här parametern med metoden SqlCommand.Parameters.Add . Om du batchar infogningarna i ett anrop ökar prestandan avsevärt jämfört med sekventiella infogningar.

Om du vill förbättra föregående exempel ytterligare använder du en lagrad procedur i stället för ett textbaserat kommando. Följande Transact-SQL-kommando skapar en lagrad procedur som tar tabellvärdesparametern SimpleTestTableType .

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

Ändra sedan SqlCommand-objektdeklarationen i föregående kodexempel till följande.

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

I de flesta fall har tabellvärdesparametrar motsvarande eller bättre prestanda än andra batchbearbetningstekniker. Tabellvärdesparametrar är ofta att föredra, eftersom de är mer flexibla än andra alternativ. Andra tekniker, till exempel SQL-masskopiering, tillåter till exempel endast infogning av nya rader. Men med tabellvärdesparametrar kan du använda logik i den lagrade proceduren för att avgöra vilka rader som är uppdateringar och vilka som är infogningar. Tabelltypen kan också ändras så att den innehåller en "Åtgärd"-kolumn som anger om den angivna raden ska infogas, uppdateras eller tas bort.

I följande tabell visas ad hoc-testresultat för användning av tabellvärdesparametrar i millisekunder.

Drift Lokalt till Azure (ms) Azure samma datacenter (ms)
1 124 32
10 131 25
100 338 51
1000 2615 382
10000 23830 3586

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

Prestandavinsten från batchbearbetning är omedelbart uppenbar. I föregående sekventiella test tog 1 000 åtgärder 129 sekunder utanför datacentret och 21 sekunder inifrån datacentret. Men med tabellvärdesparametrar tar 1 000 åtgärder bara 2,6 sekunder utanför datacentret och 0,4 sekunder inom datacentret.

Mer information om tabellvärdesparametrar finns i Tabellvärdesparametrar.

SQL-masskopiering

SQL-masskopiering är ett annat sätt att infoga stora mängder data i en måldatabas. .NET-program kan använda klassen SqlBulkCopy för att utföra massinfogningsåtgärder. SqlBulkCopy liknar i funktion kommandoradsverktyget Bcp.exe eller Transact-SQL-instruktionen BULK INSERT. I följande kodexempel visas hur du masskopierar raderna i datatabellen för källan, tabellen, till måltabellen 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);
    }
}

Det finns vissa fall där masskopiering föredras framför tabellvärdesparametrar. Se jämförelsetabellen med tabellvärdesparametrar jämfört med BULK INSERT-åtgärder i artikeln Tabellvärdesparametrar.

Följande ad hoc-testresultat visar prestanda för batchbearbetning med SqlBulkCopy i millisekunder.

Drift Lokalt till Azure (ms) Azure samma datacenter (ms)
1 433 57
10 441 32
100 636 53
1000 2535 341
10000 21605 2737

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

I mindre batchstorlekar överträffade parametrarna för användning av tabellvärde SqlBulkCopy-klassen . SqlBulkCopy presterade dock 12–31 % snabbare än tabellvärdesparametrar för testerna på 1 000 och 10 000 rader. Precis som tabellvärdesparametrar är SqlBulkCopy ett bra alternativ för batchinfogningar, särskilt jämfört med prestanda för icke-batchbaserade åtgärder.

Mer information om masskopiering i ADO.NET finns i Masskopieringsåtgärder.

Parameteriserade INSERT-uttryck med flera rader

Ett alternativ för små batchar är att konstruera en stor parameteriserad INSERT-instruktion som infogar flera rader. Följande kodexempel visar den här tekniken.

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

Det här exemplet är avsett att visa det grundläggande konceptet. Ett mer realistiskt scenario skulle loopa igenom de nödvändiga entiteterna för att konstruera frågesträngen och kommandoparametrarna samtidigt. Du är begränsad till totalt 2 100 frågeparametrar, vilket begränsar det totala antalet rader som kan bearbetas på det här sättet.

Följande ad hoc-testresultat visar prestanda för den här typen av insert-instruktion i millisekunder.

Drift Tabellvärdesparametrar (ms) INSERT med en instruktion (ms)
1 32 20
10 30 25
100 33 51

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

Den här metoden kan vara något snabbare för batchar som är mindre än 100 rader. Även om förbättringen är liten är den här tekniken ett annat alternativ som kan fungera bra i ditt specifika programscenario.

DataAdapter

Med klassen DataAdapter kan du ändra ett DataSet-objekt och sedan skicka ändringarna som INSERT-, UPDATE- och DELETE-åtgärder. Om du använder DataAdapter på det här sättet är det viktigt att observera att separata anrop görs för varje separat åtgärd. För att förbättra prestandan använder du egenskapen UpdateBatchSize till antalet åtgärder som ska batchas åt gången. Mer information finns i Utföra Batch-åtgärder med hjälp av DataAdapters.

Entity Framework

Entity Framework Core stöder batchbearbetning.

XML

För fullständighet anser vi att det är viktigt att prata om XML som en batchstrategi. Användningen av XML har dock inga fördelar jämfört med andra metoder och flera nackdelar. Metoden liknar tabellvärdesparametrar, men en XML-fil eller -sträng skickas till en lagrad procedur i stället för en användardefinierad tabell. Den lagrade proceduren parsar kommandona i den lagrade proceduren.

Det finns flera nackdelar med den här metoden:

  • Att arbeta med XML kan vara besvärligt och felbenäget.
  • Parsning av XML på databasen kan vara processorintensivt.
  • I de flesta fall är den här metoden långsammare än tabellvärdesparametrar.

Därför rekommenderas inte användning av XML för batchfrågor.

Överväganden för batchbearbetning

Följande avsnitt innehåller mer vägledning för användning av batchbearbetning i Azure SQL Database- och Azure SQL Managed Instance-program.

Avvägningar

Beroende på din arkitektur kan batchbearbetning innebära en kompromiss mellan prestanda och återhämtning. Tänk till exempel på scenariot där din roll oväntat slutar fungera. Om du förlorar en rad med data är effekten mindre än effekten av att förlora en stor mängd rader som inte har skickats. Det finns en större risk när du buffr rader innan du skickar dem till databasen i ett angivet tidsfönster.

På grund av den här kompromissen utvärderar du vilken typ av åtgärder du batchar. Batcha mer aggressivt (större batchar och längre tidsfönster) med data som är mindre kritiska.

Batchstorlek

I våra tester var det vanligtvis ingen fördel med att dela upp stora batchar i mindre segment. I själva verket resulterade den här indelningen ofta i långsammare prestanda än att skicka en enda stor batch. Tänk dig till exempel ett scenario där du vill infoga 1 000 rader. Följande tabell visar hur lång tid det tar att använda tabellvärdesparametrar för att infoga 1 000 rader när de är indelade i mindre batchar.

Batchstorlek Iterationer Tabellvärdesparametrar (ms)
1000 1 347
500 2 355
100 10 465
50 20 630

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

Du kan se att den bästa prestandan för 1 000 rader är att skicka dem alla samtidigt. I andra tester (visas inte här) fanns det en liten prestandavinst för att dela upp en batch på 1 0000 rader i två batchar med 5 000. Men tabellschemat för dessa tester är relativt enkelt, så du bör utföra tester på dina specifika data och batchstorlekar för att verifiera dessa resultat.

En annan faktor att tänka på är att om den totala batchen blir för stor kan Azure SQL Database eller Azure SQL Managed Instance begränsa och vägra att checka in batchen. För bästa resultat testar du ditt specifika scenario för att avgöra om det finns en idealisk batchstorlek. Gör batchstorleken konfigurerbar vid körning för att aktivera snabbjusteringar baserat på prestanda eller fel.

Slutligen balanserar du batchens storlek med de risker som är associerade med batchbearbetning. Om det finns tillfälliga fel eller om rollen misslyckas bör du överväga konsekvenserna av att försöka utföra åtgärden igen eller förlora data i batchen.

Parallell bearbetning

Vad händer om du har använt metoden för att minska batchstorleken men använt flera trådar för att köra arbetet? Återigen visade våra tester att flera mindre flertrådade batchar vanligtvis presterade sämre än en enda större batch. Följande test försöker infoga 1 000 rader i en eller flera parallella batchar. Det här testet visar hur fler samtidiga batchar faktiskt minskade prestanda.

Batchstorlek [Iterationer] Två trådar (ms) Fyra trådar (ms) Sex trådar (ms)
1000 [1] 277 315 266
500 [2] 548 278 256
250 [4] 405 329 265
100 [10] 488 439 391

Kommentar

Resultat är inte riktmärken. Se anteckningen om tidsschemaresultat i den här artikeln.

Det finns flera möjliga orsaker till prestandaförsämringen på grund av parallellitet:

  • Det finns flera samtidiga nätverksanrop i stället för ett.
  • Flera åtgärder mot en enskild tabell kan leda till konkurrens och blockering.
  • Det finns kostnader som är associerade med multitrådning.
  • Kostnaden för att öppna flera anslutningar uppväger fördelen med parallell bearbetning.

Om du riktar in dig på olika tabeller eller databaser är det möjligt att se vissa prestandaökningar med den här strategin. Databassharding eller federationer skulle vara ett scenario för den här metoden. Horisontell partitionering använder flera databaser och dirigerar olika data till varje databas. Om varje liten batch går till en annan databas kan det vara mer effektivt att utföra åtgärderna parallellt. Prestandaökningen är dock inte tillräckligt betydande för att kunna användas som grund för ett beslut om att använda databassharding i din lösning.

I vissa utföranden kan parallell körning av mindre batchar resultera i förbättrat dataflöde för begäranden i ett system som är under belastning. I det här fallet, även om det går snabbare att bearbeta en enda större batch, kan det vara mer effektivt att bearbeta flera batchar parallellt.

Om du använder parallell körning kan du kontrollera det maximala antalet arbetstrådar. Ett mindre tal kan leda till mindre konkurrens och snabbare körningstid. Tänk också på den ytterligare belastning som detta lägger på måldatabasen både i anslutningar och transaktioner.

Typiska riktlinjer för databasprestanda påverkar även batchbearbetning. Till exempel minskas infogningsprestanda för tabeller som har en stor primärnyckel eller många icke-grupperade index.

Om tabellvärdesparametrar använder en lagrad procedur kan du använda kommandot SET NOCOUNT ON i början av proceduren. Den här instruktionen undertrycker returen av antalet berörda rader i proceduren. I våra tester hade dock användningen av SET NOCOUNT ON antingen ingen effekt eller lägre prestanda. Den lagrade testproceduren var enkel med ett enda INSERT-kommando från parametern table-valued. Det är möjligt att mer komplexa lagrade procedurer skulle dra nytta av den här instruktionen. Men anta inte att du automatiskt förbättrar prestandan genom att lägga till SET NOCOUNT ON i den lagrade proceduren. Testa den lagrade proceduren med och utan SET NOCOUNT ON-instruktionen för att förstå effekten.

Batchbearbetningsscenarier

I följande avsnitt beskrivs hur du använder tabellvärdesparametrar i tre programscenarier. Det första scenariot visar hur buffring och batchbearbetning kan fungera tillsammans. Det andra scenariot förbättrar prestandan genom att utföra master-detail-åtgärder i ett enda lagrat proceduranrop. Det sista scenariot visar hur du använder tabellvärdesparametrar i en "UPSERT"-åtgärd.

Buffrar

Även om det finns vissa scenarier som är självklara för batchbearbetning finns det många scenarier som kan dra nytta av batchbearbetning genom fördröjd bearbetning. Fördröjd bearbetning medför dock också en större risk för att data går förlorade i händelse av ett oväntat fel. Det är viktigt att förstå denna risk och ta hänsyn till konsekvenserna.

Tänk dig till exempel ett webbprogram som spårar varje användares navigeringshistorik. På varje sidbegäran kan programmet göra ett databasanrop för att registrera användarens sidvy. Men högre prestanda och skalbarhet kan uppnås genom att buffrar användarnas navigeringsaktiviteter och sedan skickar dessa data till databasen i batchar. Du kan utlösa databasuppdateringen efter förfluten tid och/eller buffertstorlek. En regel kan till exempel ange att batchen ska bearbetas efter 20 sekunder eller när bufferten når 1 000 objekt.

I följande kodexempel används reaktiva tillägg – Rx för att bearbeta buffrade händelser som genereras av en övervakningsklass. När bufferten fylls eller en tidsgräns nås skickas batchen med användardata till databasen med en tabellvärdesparameter.

Följande NavHistoryData-klass modellerar användarnavigeringsinformationen. Den innehåller grundläggande information, till exempel användaridentifierare, den URL som används och åtkomsttiden.

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

Klassen NavHistoryDataMonitor ansvarar för buffring av användarnavigeringsdata till databasen. Den innehåller en metod, RecordUserNavigationEntry, som svarar genom att skapa en OnAdded-händelse . Följande kod visar konstruktorlogik som använder Rx för att skapa en observerbar samling baserat på händelsen. Den prenumererar sedan på den här observerbara samlingen med buffertmetoden. Överlagringen anger att bufferten ska skickas var 20:e sekund eller var 1000:e post.

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

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

Hanteraren konverterar alla buffrade objekt till en tabellvärdestyp och skickar sedan den här typen till en lagrad procedur som bearbetar batchen. Följande kod visar den fullständiga definitionen för både Klasserna NavHistoryDataEventArgs och 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);
    }

Hanteraren konverterar alla buffrade objekt till en tabellvärdestyp och skickar sedan den här typen till en lagrad procedur som bearbetar batchen. Följande kod visar den fullständiga definitionen för både Klasserna NavHistoryDataEventArgs och NavHistoryDataMonitor.

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

Om du vill använda den här buffringsklassen skapar programmet ett statiskt NavHistoryDataMonitor-objekt. Varje gång en användare kommer åt en sida anropar programmet metoden NavHistoryDataMonitor.RecordUserNavigationEntry. Buffertlogik fortsätter att ta hand om att skicka dessa poster till databasen i batchar.

Huvudinformation

Tabellvärdesparametrar är användbara för enkla INSERT-scenarier. Det kan dock vara svårare att batchinfogningar som omfattar mer än en tabell. Scenariot "master/detail" är ett bra exempel. Huvudtabellen identifierar den primära entiteten. En eller flera informationstabeller lagrar mer data om entiteten. I det här scenariot framtvingar sekundärnyckelrelationer relationen mellan information och en unik huvudentitet. Överväg en förenklad version av en PurchaseOrder-tabell och dess associerade OrderDetail-tabell. Följande Transact-SQL skapar tabellen PurchaseOrder med fyra kolumner: OrderID, OrderDate, CustomerID och 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 ))

Varje beställning innehåller ett eller flera produktköp. Den här informationen samlas in i tabellen PurchaseOrderDetail. Följande Transact-SQL skapar tabellen PurchaseOrderDetail med fem kolumner: OrderID, OrderDetailID, ProductID, UnitPrice och 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 ))

Kolumnen OrderID i tabellen PurchaseOrderDetail måste referera till en order från tabellen PurchaseOrder. Följande definition av en sekundärnyckel tillämpar den här begränsningen.

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

För att kunna använda tabellvärdesparametrar måste du ha en användardefinierad tabelltyp för varje måltabell.

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

Definiera sedan en lagrad procedur som accepterar tabeller av dessa typer. Med den här proceduren kan ett program lokalt batcha en uppsättning beställningar och orderinformation i ett enda anrop. Följande Transact-SQL innehåller den fullständiga lagrade procedurdeklarationen för det här inköpsorderexemplet.

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

I det här exemplet lagrar den lokalt definierade @IdentityLink tabellen de faktiska OrderID-värdena från de nyligen infogade raderna. Dessa orderidentifierare skiljer sig från de tillfälliga OrderID-värdena i parametrarna @orders och @details tabellvärdeparametrarna. Därför @IdentityLink ansluter tabellen sedan OrderID-värdena från parametern @orders till de verkliga OrderID-värdena för de nya raderna i tabellen PurchaseOrder. Efter det här steget kan tabellen @IdentityLink underlätta infogning av orderinformationen med det faktiska OrderID som uppfyller villkoret för sekundärnyckeln.

Den här lagrade proceduren kan användas från kod eller från andra Transact-SQL-anrop. Se avsnittet tabellvärdeparametrar i det här dokumentet för ett kodexempel. Följande Transact-SQL visar hur du anropar 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

Med den här lösningen kan varje batch använda en uppsättning OrderID-värden som börjar vid 1. Dessa tillfälliga OrderID-värden beskriver relationerna i batchen, men de faktiska OrderID-värdena bestäms vid tidpunkten för infogningsåtgärden. Du kan köra samma instruktioner i föregående exempel upprepade gånger och generera unika beställningar i databasen. Därför bör du överväga att lägga till mer kod- eller databaslogik som förhindrar dubbletter av beställningar när du använder den här batchbearbetningstekniken.

Det här exemplet visar att ännu mer komplexa databasåtgärder, till exempel master-detail-åtgärder, kan batchhanteras med hjälp av tabellvärdesparametrar.

UPSERT

Ett annat batchbearbetningsscenario innebär att befintliga rader uppdateras samtidigt och nya rader infogas. Den här åtgärden kallas ibland för en "UPSERT"-åtgärd (uppdatera + infoga). I stället för att göra separata anrop till INSERT och UPDATE kan MERGE-instruktionen vara en lämplig ersättning. MERGE-instruktionen kan utföra både infognings- och uppdateringsåtgärder i ett enda anrop. Låsningsmekaniken för MERGE-instruktionen fungerar annorlunda än separata INSERT- och UPDATE-instruktioner. Testa dina specifika arbetsbelastningar innan du distribuerar till produktion.

Tabellvärdeparametrar kan användas med MERGE-instruktionen för att utföra uppdateringar och infogningar. Tänk dig till exempel en förenklad employee-tabell som innehåller följande kolumner: 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 ))

I det här exemplet kan du använda det faktum att SocialSecurityNumber är unikt för att utföra en SAMMANSLAGNING av flera anställda. Skapa först den användardefinierade tabelltypen:

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

Skapa sedan en lagrad procedur eller skrivkod som använder MERGE-instruktionen för att utföra uppdateringen och infoga. I följande exempel används MERGE-instruktionen på en tabellvärdesparameter, @employees, av typen EmployeeTableType. Innehållet i @employees tabellen visas inte här.

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

Mer information finns i dokumentationen och exemplen för MERGE-instruktionen. Även om samma arbete kan utföras i ett lagrat proceduranrop i flera steg med separata INSERT- och UPDATE-åtgärder är MERGE-instruktionen mer effektiv. Databaskod kan också konstruera Transact-SQL-anrop som använder MERGE-instruktionen direkt utan att kräva två databasanrop för INSERT och UPDATE.

Rekommendationssammanfattning

Följande lista innehåller en sammanfattning av de batchrekommendationer som beskrivs i den här artikeln:

  • Använd buffring och batchbearbetning för att öka prestanda och skalbarhet för Azure SQL Database- och Azure SQL Managed Instance-program.
  • Förstå kompromisserna mellan batchbearbetning/buffring och återhämtning. Under ett rollfel kan risken för att förlora en obearbetad batch med affärskritiska data uppväga prestandafördelarna med batchbearbetning.
  • Försök att hålla alla anrop till databasen inom ett enda datacenter för att minska svarstiden.
  • Om du väljer en enda batchteknik ger tabellvärdesparametrar bästa prestanda och flexibilitet.
  • För den snabbaste infogningsprestandan följer du dessa allmänna riktlinjer men testar ditt scenario:
    • För < 100 rader använder du ett enda parameteriserat INSERT-kommando.
    • För < 1 000 rader använder du tabellvärdesparametrar.
    • För >= 1 000 rader använder du SqlBulkCopy.
  • För uppdaterings- och borttagningsåtgärder använder du tabellvärdesparametrar med lagrad procedurlogik som avgör rätt åtgärd på varje rad i tabellparametern.
  • Riktlinjer för batchstorlek:
    • Använd de största batchstorlekarna som passar dina program- och affärskrav.
    • Balansera prestandavinsten för stora batchar med risken för tillfälliga eller katastrofala fel. Vad är konsekvensen av återförsök eller förlust av data i batchen?
    • Testa den största batchstorleken för att kontrollera att Azure SQL Database eller Azure SQL Managed Instance inte avvisar den.
    • Skapa konfigurationsinställningar som styr batchbearbetning, till exempel batchstorleken eller tidsfönstret för buffring. De här inställningarna ger flexibilitet. Du kan ändra batchbearbetningsbeteendet i produktion utan att distribuera om molntjänsten.
  • Undvik parallell körning av batchar som körs på en enda tabell i en databas. Om du väljer att dela upp en enda batch mellan flera arbetstrådar kör du tester för att fastställa det ideala antalet trådar. Efter ett ospecificerat tröskelvärde minskar fler trådar prestanda i stället för att öka det.
  • Överväg att buffring på storlek och tid som ett sätt att implementera batchbearbetning för fler scenarier.

Nästa steg

Den här artikeln fokuserar på hur databasdesign och kodningstekniker som rör batchbearbetning kan förbättra programmets prestanda och skalbarhet. Men detta är bara en faktor i din övergripande strategi. Fler sätt att förbättra prestanda och skalbarhet finns i Vägledning för databasprestanda och Pris- och prestandaöverväganden för en elastisk pool.