Delen via

Batchverwerking gebruiken om de prestaties van azure SQL Database- en Azure SQL Managed Instance-toepassingen te verbeteren

  • Artikel

Van toepassing op: Azure SQL DatabaseAzure SQL Managed Instance

Batchbewerkingen naar Azure SQL Database en Azure SQL Managed Instance verbeteren de prestaties en schaalbaarheid van uw toepassingen aanzienlijk. Om inzicht te krijgen in de voordelen, behandelt het eerste deel van dit artikel enkele voorbeeldtestresultaten waarmee opeenvolgende en batchaanvragen worden vergeleken met een database in Azure SQL Database of Azure SQL Managed Instance. In de rest van het artikel worden de technieken, scenario's en overwegingen beschreven waarmee u batchverwerking in uw Azure-toepassingen kunt gebruiken.

Waarom is batchverwerking belangrijk voor Azure SQL Database en Azure SQL Managed Instance?

Batchverwerking van aanroepen naar een externe service is een bekende strategie voor het verhogen van de prestaties en schaalbaarheid. Er zijn vaste verwerkingskosten voor interacties met een externe service, zoals serialisatie, netwerkoverdracht en deserialisatie. Als u veel afzonderlijke transacties in één batch verpakt, worden deze kosten geminimaliseerd.

In dit artikel willen we verschillende batchstrategieën en -scenario's onderzoeken. Hoewel deze strategieën ook belangrijk zijn voor on-premises toepassingen die gebruikmaken van SQL Server, zijn er verschillende redenen om het gebruik van batchverwerking voor Azure SQL Database en Azure SQL Managed Instance te benadrukken:

  • Er is mogelijk meer netwerklatentie bij het openen van Azure SQL Database en Azure SQL Managed Instance, met name als u toegang hebt tot Azure SQL Database of Azure SQL Managed Instance van buiten hetzelfde Microsoft Azure-datacenter.
  • De multitenant-kenmerken van Azure SQL Database en Azure SQL Managed Instance betekent dat de efficiëntie van de gegevenstoegangslaag overeenkomt met de algehele schaalbaarheid van de database. Als reactie op het gebruik van meer dan vooraf gedefinieerde quota, kunnen Azure SQL Database en Azure SQL Managed Instance de doorvoer verminderen of reageren met beperkingen. Met efficiëntie, zoals batchverwerking, kunt u meer werk doen voordat u deze limieten bereikt.
  • Batching is ook effectief voor architecturen die gebruikmaken van meerdere databases (sharding). De efficiëntie van uw interactie met elke database-eenheid is nog steeds een belangrijke factor in uw algehele schaalbaarheid.

Een van de voordelen van het gebruik van Azure SQL Database of Azure SQL Managed Instance is dat u de servers waarop de database wordt gehost, niet hoeft te beheren. Deze beheerde infrastructuur betekent echter ook dat u anders moet nadenken over databaseoptimalisaties. U kunt de databasehardware of netwerkinfrastructuur niet meer verbeteren. Microsoft Azure beheert deze omgevingen. Het belangrijkste gebied dat u kunt beheren, is hoe uw toepassing communiceert met Azure SQL Database en Azure SQL Managed Instance. Batchverwerking is een van deze optimalisaties.

In het eerste deel van dit artikel worden verschillende batchverwerkingstechnieken onderzocht voor .NET-toepassingen die gebruikmaken van Azure SQL Database of Azure SQL Managed Instance. De laatste twee secties hebben betrekking op batchrichtlijnen en -scenario's.

Batchverwerkingsstrategieën

Opmerking over timingresultaten in dit artikel

Notitie

Resultaten zijn geen benchmarks, maar zijn bedoeld om relatieve prestaties weer te geven. Tijdsinstellingen zijn gebaseerd op een gemiddelde van ten minste 10 testuitvoeringen. Bewerkingen worden ingevoegd in een lege tabel. Deze tests zijn gemeten vóór V12 en ze komen niet noodzakelijkerwijs overeen met de doorvoer die u mogelijk ondervindt in een V12-database met behulp van de nieuwe DTU-servicelagen of vCore-servicelagen. Het relatieve voordeel van de batchverwerkingstechniek moet vergelijkbaar zijn.

Transacties

Het lijkt vreemd om te beginnen met een beoordeling van batchverwerking door transacties te bespreken. Maar het gebruik van transacties aan de clientzijde heeft een subtiel batcheffect aan de serverzijde dat de prestaties verbetert. En transacties kunnen worden toegevoegd met slechts een paar regels code, zodat ze een snelle manier bieden om de prestaties van sequentiële bewerkingen te verbeteren.

Bekijk de volgende C#-code die een reeks invoeg- en updatebewerkingen op een eenvoudige tabel bevat.

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

In de volgende ADO.NET code worden deze bewerkingen opeenvolgend uitgevoerd.

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

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

De beste manier om deze code te optimaliseren, is door een vorm van batchverwerking aan de clientzijde van deze aanroepen te implementeren. Maar er is een eenvoudige manier om de prestaties van deze code te verbeteren door de reeks aanroepen in een transactie te verpakken. Hier volgt dezelfde code die gebruikmaakt van een transactie.

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

Transacties worden in beide voorbeelden daadwerkelijk gebruikt. In het eerste voorbeeld is elke afzonderlijke aanroep een impliciete transactie. In het tweede voorbeeld verpakt een expliciete transactie alle aanroepen. Volgens de documentatie voor het write-ahead transactielogboek worden logboekrecords naar de schijf leeggemaakt wanneer de transactie doorvoert. Door meer aanroepen in een transactie op te geven, kan de schrijfbewerking naar het transactielogboek vertragen totdat de transactie is doorgevoerd. In feite schakelt u batchverwerking in voor de schrijfbewerkingen naar het transactielogboek van de server.

In de volgende tabel ziet u enkele ad-hoc testresultaten. Tijdens de tests zijn dezelfde opeenvolgende invoegingen uitgevoerd met en zonder transacties. Voor meer perspectief werd de eerste reeks tests extern uitgevoerd van een laptop naar de database in Microsoft Azure. De tweede reeks tests die zijn uitgevoerd vanuit een cloudservice en database die beide zich in hetzelfde Microsoft Azure-datacenter (VS - west) bevinden. In de volgende tabel ziet u de duur in milliseconden van sequentiële invoegingen met en zonder transacties.

On-premises naar Azure:

Bewerkingen Geen transactie (ms) Transactie (ms)
1 130 402
10 1208 1226
100 12662 10395
1000 128852 102917

Azure naar Azure (hetzelfde datacenter):

Bewerkingen Geen transactie (ms) Transactie (ms)
1 21 26
10 220 56
100 2145 341
1000 21479 2756

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

Op basis van de vorige testresultaten vermindert het verpakken van één bewerking in een transactie de prestaties. Maar naarmate u het aantal bewerkingen binnen één transactie verhoogt, wordt de prestatieverbetering duidelijker. Het prestatieverschil is ook duidelijker wanneer alle bewerkingen plaatsvinden in het Microsoft Azure-datacenter. De toegenomen latentie van het gebruik van Azure SQL Database of Azure SQL Managed Instance van buiten het Microsoft Azure-datacenter overschaduwt de prestatiewinst van het gebruik van transacties.

Hoewel het gebruik van transacties de prestaties kan verbeteren, blijft u de best practices voor transacties en verbindingen observeren. Houd de transactie zo kort mogelijk en sluit de databaseverbinding nadat het werk is voltooid. De using-instructie in het vorige voorbeeld zorgt ervoor dat de verbinding wordt gesloten wanneer het volgende codeblok is voltooid.

In het vorige voorbeeld ziet u dat u een lokale transactie kunt toevoegen aan elke ADO.NET code met twee regels. Transacties bieden een snelle manier om de prestaties van code te verbeteren waardoor opeenvolgende invoeg-, update- en verwijderbewerkingen worden uitgevoerd. Voor de snelste prestaties kunt u echter overwegen om de code verder te wijzigen om te profiteren van batchverwerking aan de clientzijde, zoals parameters met tabelwaarden.

Zie Lokale transacties in ADO.NET voor meer informatie over transacties in ADO.NET.

Tabelwaardeparameters

Parameters met tabelwaarde ondersteunen door de gebruiker gedefinieerde tabeltypen als parameters in Transact-SQL-instructies, opgeslagen procedures en functies. Met deze batchverwerkingstechniek aan de clientzijde kunt u meerdere rijen met gegevens verzenden binnen de parameter met tabelwaarde. Als u parameters met tabelwaarden wilt gebruiken, definieert u eerst een tabeltype. Met de volgende Transact-SQL-instructie maakt u een tabeltype met de naam MyTableType.

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

In code maakt u een gegevenstabel met exact dezelfde namen en typen van het tabeltype. Geef deze gegevenstabel door in een parameter in een tekstquery of opgeslagen procedure-aanroep. In het volgende voorbeeld ziet u deze techniek:

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

In het vorige voorbeeld voegt het object SqlCommand rijen uit een tabelwaardeparameter in, @TestTvp. Het eerder gemaakte DataTable-object wordt toegewezen aan deze parameter met de methode SqlCommand.Parameters.Add . Als u de invoegingen in één aanroep batcheert, neemt de prestaties ten opzichte van sequentiële invoegingen aanzienlijk toe.

Als u het vorige voorbeeld verder wilt verbeteren, gebruikt u een opgeslagen procedure in plaats van een op tekst gebaseerde opdracht. Met de volgende Transact-SQL-opdracht maakt u een opgeslagen procedure die de parameter SimpleTestTableType-tabelwaarde gebruikt.

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

Wijzig vervolgens de declaratie van het SqlCommand-object in het vorige codevoorbeeld in het volgende.

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

In de meeste gevallen hebben parameters met tabelwaarden equivalente of betere prestaties dan andere batchverwerkingstechnieken. Parameters met tabelwaarden hebben vaak de voorkeur, omdat ze flexibeler zijn dan andere opties. Andere technieken, zoals het bulksgewijs kopiëren van SQL, maken bijvoorbeeld alleen het invoegen van nieuwe rijen mogelijk. Maar met parameters met tabelwaarden kunt u logica in de opgeslagen procedure gebruiken om te bepalen welke rijen worden bijgewerkt en welke invoegingen zijn. Het tabeltype kan ook worden gewijzigd om een kolom 'Bewerking' te bevatten die aangeeft of de opgegeven rij moet worden ingevoegd, bijgewerkt of verwijderd.

In de volgende tabel ziet u ad-hoc testresultaten voor het gebruik van parameters met tabelwaarden in milliseconden.

Bewerkingen On-premises naar Azure (ms) Hetzelfde datacenter van Azure (ms)
1 124 32
10 131 25
100 338 51
1000 2615 382
10000 23830 3586

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

De prestatiewinst van batchverwerking is onmiddellijk duidelijk. In de vorige sequentiële test duurde 1000 bewerkingen 129 seconden buiten het datacenter en 21 seconden vanuit het datacenter. Maar met parameters met tabelwaarden duurt het 1000 bewerkingen slechts 2,6 seconden buiten het datacenter en 0,4 seconden binnen het datacenter.

Zie Parameters met tabelwaarde voor meer informatie over parameters met tabelwaarden.

Bulkkopie van SQL

Het bulksgewijs kopiëren van SQL is een andere manier om grote hoeveelheden gegevens in te voegen in een doeldatabase. .NET-toepassingen kunnen de klasse SqlBulkCopy gebruiken om bulksgewijze invoegbewerkingen uit te voeren. SqlBulkCopy is vergelijkbaar met het opdrachtregelprogramma, Bcp.exe of de Transact-SQL-instructie BULK INSERT. In het volgende codevoorbeeld ziet u hoe u de rijen in de brongegevenstabel, tabel, bulksgewijs kopieert naar de doeltabel, 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);
    }
}

Er zijn enkele gevallen waarin bulksgewijs kopiëren de voorkeur heeft boven parameters met tabelwaarden. Zie de vergelijkingstabel met parameters met tabelwaarde versus BULK INSERT-bewerkingen in het artikel Tabelwaardeparameters.

In de volgende ad-hoctestresultaten ziet u de prestaties van batchverwerking met SqlBulkCopy in milliseconden.

Bewerkingen On-premises naar Azure (ms) Hetzelfde datacenter van Azure (ms)
1 433 57
10 441 32
100 636 53
1000 2535 341
10000 21605 2737

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

In kleinere batchgrootten presteerden de parameters met tabelwaarden beter dan de SqlBulkCopy-klasse . SqlBulkCopy heeft echter 12-31% sneller uitgevoerd dan tabelwaardeparameters voor de tests van 10.000 en 10.000 rijen. Net als parameters met tabelwaarden is SqlBulkCopy een goede optie voor ingevoegde batches, met name in vergelijking met de prestaties van niet-batchbewerkingen.

Zie Bewerkingen voor bulksgewijs kopiëren in ADO.NET voor meer informatie over bulksgewijs kopiëren.

Met meerdere rijen geparameteriseerde INSERT-instructies

Een alternatief voor kleine batches is het maken van een grote geparameteriseerde INSERT-instructie waarmee meerdere rijen worden ingevoegd. In het volgende codevoorbeeld ziet u deze techniek.

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

Dit voorbeeld is bedoeld om het basisconcept weer te geven. Een realistischer scenario zou de vereiste entiteiten doorlopen om tegelijkertijd de queryreeks en de opdrachtparameters samen te stellen. U bent beperkt tot een totaal van 2100 queryparameters, dus dit beperkt het totale aantal rijen dat op deze manier kan worden verwerkt.

In de volgende ad-hoctestresultaten ziet u de prestaties van dit type insert-instructie in milliseconden.

Bewerkingen Parameters met tabelwaarde (ms) INSERT met één instructie (ms)
1 32 20
10 30 25
100 33 51

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

Deze benadering kan iets sneller zijn voor batches die kleiner zijn dan 100 rijen. Hoewel de verbetering klein is, is deze techniek een andere optie die goed werkt in uw specifieke toepassingsscenario.

DataAdapter

Met de DataAdapter-klasse kunt u een DataSet-object wijzigen en de wijzigingen vervolgens verzenden als INSERT-, UPDATE- en DELETE-bewerkingen. Als u de DataAdapter op deze manier gebruikt, is het belangrijk te weten dat er afzonderlijke aanroepen worden gedaan voor elke afzonderlijke bewerking. Als u de prestaties wilt verbeteren, gebruikt u de eigenschap UpdateBatchSize voor het aantal bewerkingen dat tegelijk moet worden gebatcheerd. Zie Batchbewerkingen uitvoeren met Behulp van DataAdapters voor meer informatie.

Entity Framework

Entity Framework Core ondersteunt batchverwerking.

XML

Voor volledigheid vinden we het belangrijk om over XML te praten als een batchstrategie. Het gebruik van XML heeft echter geen voordelen ten opzichte van andere methoden en verschillende nadelen. De benadering is vergelijkbaar met parameters met tabelwaarden, maar een XML-bestand of tekenreeks wordt doorgegeven aan een opgeslagen procedure in plaats van een door de gebruiker gedefinieerde tabel. De opgeslagen procedure parseert de opdrachten in de opgeslagen procedure.

Er zijn verschillende nadelen voor deze aanpak:

  • Het werken met XML kan omslachtig en foutgevoelig zijn.
  • Het parseren van de XML in de database kan CPU-intensief zijn.
  • In de meeste gevallen is deze methode langzamer dan parameters met tabelwaarden.

Daarom wordt het gebruik van XML voor batchquery's niet aanbevolen.

Overwegingen voor batchverwerking

De volgende secties bieden meer richtlijnen voor het gebruik van batchverwerking in Azure SQL Database- en Azure SQL Managed Instance-toepassingen.

Compromissen

Afhankelijk van uw architectuur kan batchverwerking een afweging maken tussen prestaties en tolerantie. Denk bijvoorbeeld aan het scenario waarin uw rol onverwacht uitvalt. Als u één rij met gegevens kwijtraakt, is de impact kleiner dan het verlies van een grote batch niet-verzonden rijen. Er is een groter risico wanneer u rijen buffert voordat u deze in een opgegeven tijdvenster naar de database verzendt.

Als gevolg hiervan evalueert u het type bewerkingen dat u batcht. Batcher agressief (grotere batches en langere tijdvensters) met gegevens die minder kritiek zijn.

Batchgrootte

In onze tests was er meestal geen voordeel om grote batches in kleinere segmenten te breken. In feite heeft deze onderverdeling vaak geleid tot tragere prestaties dan het indienen van één grote batch. Denk bijvoorbeeld aan een scenario waarin u 1000 rijen wilt invoegen. In de volgende tabel ziet u hoe lang het duurt voordat u parameters met tabelwaarden gebruikt om 1000 rijen in te voegen wanneer deze zijn onderverdeeld in kleinere batches.

Batchgrootte Iteraties Parameters met tabelwaarde (ms)
1000 1 347
500 2 355
100 10 465
50 20 630

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

U kunt zien dat de beste prestaties voor 1000 rijen zijn om ze allemaal tegelijk in te dienen. In andere tests (niet hier weergegeven), was er een kleine prestatiewinst om een batch van 10000 rijen te verbreken in twee batches van 5000. Maar het tabelschema voor deze tests is relatief eenvoudig, dus u moet tests uitvoeren op uw specifieke gegevens en batchgrootten om deze bevindingen te controleren.

Een andere factor om rekening mee te houden is dat als de totale batch te groot wordt, Azure SQL Database of Azure SQL Managed Instance de batch kan beperken en weigeren om de batch door te voeren. Voor de beste resultaten test u uw specifieke scenario om te bepalen of er een ideale batchgrootte is. Maak de batchgrootte tijdens runtime configureerbaar om snelle aanpassingen in te schakelen op basis van prestaties of fouten.

Ten slotte moet u de grootte van de batch verdelen over de risico's die betrekking hebben op batchverwerking. Als er tijdelijke fouten optreden of als de rol mislukt, kunt u rekening houden met de gevolgen van het opnieuw proberen van de bewerking of het verlies van de gegevens in de batch.

Parallelle verwerking

Wat moet u doen als u de batchgrootte hebt verkleind, maar meerdere threads hebt gebruikt om het werk uit te voeren? Nogmaals, onze tests toonden aan dat verschillende kleinere multithreaded batches doorgaans slechter presteerden dan één grotere batch. Met de volgende test worden 1000 rijen in een of meer parallelle batches ingevoegd. Deze test laat zien hoe meer gelijktijdige batches de prestaties daadwerkelijk hebben verminderd.

Batchgrootte [Iteraties] Twee threads (ms) Vier threads (ms) Zes threads (ms)
1000 [1] 277 315 266
500 [2] 548 278 256
250 [4] 405 329 265
100 [10] 488 439 391

Notitie

Resultaten zijn geen benchmarks. Zie de opmerking over timingresultaten in dit artikel.

Er zijn verschillende mogelijke oorzaken voor de afname van de prestaties vanwege parallelle uitvoering:

  • Er zijn meerdere gelijktijdige netwerkaanroepen in plaats van één.
  • Meerdere bewerkingen op één tabel kunnen leiden tot conflicten en blokkeringen.
  • Er zijn overheads verbonden aan multithreading.
  • De kosten van het openen van meerdere verbindingen wegen op tegen het voordeel van parallelle verwerking.

Als u zich richt op verschillende tabellen of databases, is het mogelijk om prestatieverbeteringen met deze strategie te zien. Database-sharding of federaties zijn een scenario voor deze benadering. Sharding maakt gebruik van meerdere databases en stuurt verschillende gegevens naar elke database. Als elke kleine batch naar een andere database gaat, kan het parallel uitvoeren van de bewerkingen efficiënter zijn. De prestatieverbetering is echter niet significant genoeg om te gebruiken als basis voor een beslissing om database-sharding in uw oplossing te gebruiken.

In sommige ontwerpen kan parallelle uitvoering van kleinere batches leiden tot een verbeterde doorvoer van aanvragen in een systeem dat wordt belast. In dit geval kan het efficiënter zijn om meerdere batches parallel te verwerken, ook al is het sneller om één grotere batch te verwerken.

Als u parallelle uitvoering gebruikt, kunt u overwegen om het maximum aantal werkthreads te beheren. Een kleiner getal kan leiden tot minder conflicten en een snellere uitvoeringstijd. Houd ook rekening met de extra belasting die dit op de doeldatabase plaatst, zowel in verbindingen als transacties.

Typische richtlijnen voor databaseprestaties zijn ook van invloed op batchverwerking. Invoegprestaties worden bijvoorbeeld verminderd voor tabellen met een grote primaire sleutel of veel niet-geclusterde indexen.

Als parameters met tabelwaarden een opgeslagen procedure gebruiken, kunt u de opdracht SET NOCOUNT ON aan het begin van de procedure gebruiken. Met deze instructie wordt het resultaat onderdrukt van het aantal betrokken rijen in de procedure. In onze tests had het gebruik van SET NOCOUNT ON echter geen effect of verminderde prestaties. De opgeslagen testprocedure is eenvoudig met één INSERT-opdracht uit de parameter met tabelwaarde. Het is mogelijk dat complexere opgeslagen procedures profiteren van deze instructie. Maar neem niet aan dat het toevoegen van SET NOCOUNT ON aan uw opgeslagen procedure automatisch de prestaties verbetert. Als u het effect wilt begrijpen, test u de opgeslagen procedure met en zonder de INSTRUCTIE SET NOCOUNT ON .

Batchscenario's

In de volgende secties wordt beschreven hoe u parameters met tabelwaarden gebruikt in drie toepassingsscenario's. In het eerste scenario ziet u hoe buffering en batchverwerking kunnen samenwerken. Het tweede scenario verbetert de prestaties door masterdetailbewerkingen uit te voeren in één opgeslagen procedure-aanroep. In het laatste scenario ziet u hoe u tabelwaardeparameters gebruikt in een UPSERT-bewerking.

Buffering

Hoewel er enkele scenario's zijn die duidelijk kandidaat zijn voor batchverwerking, zijn er veel scenario's die kunnen profiteren van batchverwerking door vertraagde verwerking. Vertraagde verwerking draagt echter ook een groter risico dat de gegevens verloren gaan in het geval van een onverwachte fout. Het is belangrijk om dit risico te begrijpen en rekening te houden met de gevolgen.

Denk bijvoorbeeld aan een webtoepassing waarmee de navigatiegeschiedenis van elke gebruiker wordt bijgehouden. Op elke paginaaanvraag kan de toepassing een databaseaanroep maken om de paginaweergave van de gebruiker vast te leggen. Maar hogere prestaties en schaalbaarheid kunnen worden bereikt door de navigatieactiviteiten van de gebruikers te bufferen en deze gegevens vervolgens in batches naar de database te verzenden. U kunt de database-update activeren door de verstreken tijd en/of buffergrootte. Een regel kan bijvoorbeeld opgeven dat de batch na 20 seconden moet worden verwerkt of wanneer de buffer 1000 items bereikt.

In het volgende codevoorbeeld wordt gebruikgemaakt van reactieve extensies - Rx om gebufferde gebeurtenissen te verwerken die zijn gegenereerd door een bewakingsklasse. Wanneer de buffer wordt gevuld of een time-out wordt bereikt, wordt de batch met gebruikersgegevens naar de database verzonden met een parameter met een tabelwaarde.

De volgende NavHistoryData-klasse modelleert de gebruikersnavigatiegegevens. Deze bevat basisinformatie, zoals de gebruikers-id, de URL die wordt geopend en de toegangstijd.

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

De klasse NavHistoryDataMonitor is verantwoordelijk voor het bufferen van de gebruikersnavigatiegegevens naar de database. Het bevat een methode, RecordUserNavigationEntry, die reageert door een OnAdded-gebeurtenis op te halen. De volgende code toont de constructorlogica die Rx gebruikt om een waarneembare verzameling te maken op basis van de gebeurtenis. Vervolgens wordt u geabonneerd op deze waarneembare verzameling met de buffermethode. De overbelasting geeft aan dat de buffer elke 20 seconden of 1000 vermeldingen moet worden verzonden.

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

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

De handler converteert alle gebufferde items naar een tabelwaardetype en geeft dit type vervolgens door aan een opgeslagen procedure waarmee de batch wordt verwerkt. De volgende code toont de volledige definitie voor de klassen NavHistoryDataEventArgs en 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);
    }

De handler converteert alle gebufferde items naar een tabelwaardetype en geeft dit type vervolgens door aan een opgeslagen procedure waarmee de batch wordt verwerkt. De volgende code toont de volledige definitie voor de klassen NavHistoryDataEventArgs en 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();
        }
    }
}

Als u deze bufferklasse wilt gebruiken, maakt de toepassing een statisch NavHistoryDataMonitor-object. Telkens wanneer een gebruiker een pagina opent, roept de toepassing de methode NavHistoryDataMonitor.RecordUserNavigationEntry aan. De bufferlogica zorgt ervoor dat deze vermeldingen in batches naar de database worden verzonden.

Modeldetails

Parameters met tabelwaarden zijn handig voor eenvoudige INSERT-scenario's. Het kan echter lastiger zijn om batchinvoegingen te maken die betrekking hebben op meer dan één tabel. Het 'master/detail'-scenario is een goed voorbeeld. De hoofdtabel identificeert de primaire entiteit. In een of meer detailtabellen worden meer gegevens over de entiteit opgeslagen. In dit scenario dwingen refererende sleutelrelaties de relatie van details af naar een unieke hoofdentiteit. Overweeg een vereenvoudigde versie van een PurchaseOrder-tabel en de bijbehorende OrderDetail-tabel. Met de volgende Transact-SQL wordt de tabel PurchaseOrder gemaakt met vier kolommen: OrderID, OrderDate, CustomerID en 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 ))

Elke bestelling bevat een of meer productaankopen. Deze informatie wordt vastgelegd in de tabel PurchaseOrderDetail. Met de volgende Transact-SQL wordt de tabel PurchaseOrderDetail gemaakt met vijf kolommen: OrderID, OrderDetailID, ProductID, UnitPrice en 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 ))

De kolom Order-id in de tabel PurchaseOrderDetail moet verwijzen naar een order uit de tabel PurchaseOrder. Met de volgende definitie van een refererende sleutel wordt deze beperking afgedwongen.

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

Als u parameters met tabelwaarden wilt gebruiken, moet u één door de gebruiker gedefinieerd tabeltype hebben voor elke doeltabel.

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

Definieer vervolgens een opgeslagen procedure die tabellen van deze typen accepteert. Met deze procedure kan een toepassing in één aanroep een reeks orders en ordergegevens lokaal batcheren. De volgende Transact-SQL bevat de volledige opgeslagen proceduredeclaratie voor dit voorbeeld van een inkooporder.

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

In dit voorbeeld worden in de lokaal gedefinieerde @IdentityLink tabel de werkelijke OrderID-waarden uit de zojuist ingevoegde rijen opgeslagen. Deze order-id's verschillen van de tijdelijke OrderID-waarden in de @orders parameters met @details tabelwaarden. Daarom verbindt de @IdentityLink tabel vervolgens de OrderID-waarden van de @orders parameter met de werkelijke OrderID-waarden voor de nieuwe rijen in de tabel PurchaseOrder. Na deze stap kan de tabel het @IdentityLink invoegen van de ordergegevens vergemakkelijken met de werkelijke Order-id die voldoet aan de beperking van de refererende sleutel.

Deze opgeslagen procedure kan worden gebruikt vanuit code of andere Transact-SQL-aanroepen. Zie de sectie met parameters met tabelwaarden van dit document voor een codevoorbeeld. In de volgende Transact-SQL ziet u hoe u de sp_InsertOrdersBatch aanroept.

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

Met deze oplossing kan elke batch een set OrderID-waarden gebruiken die beginnen bij 1. Deze tijdelijke OrderID-waarden beschrijven de relaties in de batch, maar de werkelijke OrderID-waarden worden bepaald op het moment van de invoegbewerking. U kunt dezelfde instructies in het vorige voorbeeld herhaaldelijk uitvoeren en unieke orders genereren in de database. Daarom kunt u overwegen om meer code of databaselogica toe te voegen waarmee dubbele orders worden voorkomen bij het gebruik van deze batchverwerkingstechniek.

In dit voorbeeld ziet u dat nog complexere databasebewerkingen, zoals masterdetailbewerkingen, kunnen worden gebatcheerd met behulp van parameters met tabelwaarden.

UPSERT

Een ander batchscenario omvat het gelijktijdig bijwerken van bestaande rijen en het invoegen van nieuwe rijen. Deze bewerking wordt soms een UPSERT-bewerking (update + insert) genoemd. In plaats van afzonderlijke aanroepen naar INSERT en UPDATE uit te voeren, kan de MERGE-instructie een geschikte vervanging zijn. De MERGE-instructie kan zowel invoeg- als updatebewerkingen uitvoeren in één aanroep. De vergrendelingsmechanica van de MERGE-instructie werkt anders dan afzonderlijke INSERT- en UPDATE-instructies. Test uw specifieke workloads voordat u implementeert in productie.

Parameters met tabelwaarden kunnen worden gebruikt met de MERGE-instructie om updates en invoegingen uit te voeren. Denk bijvoorbeeld aan een vereenvoudigde tabel Employee die de volgende kolommen bevat: 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 ))

In dit voorbeeld kunt u het feit gebruiken dat het SocialSecurityNumber uniek is om een MERGE van meerdere werknemers uit te voeren. Maak eerst het door de gebruiker gedefinieerde tabeltype:

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

Maak vervolgens een opgeslagen procedure of schrijf code die gebruikmaakt van de MERGE-instructie om de update uit te voeren en in te voegen. In het volgende voorbeeld wordt de instructie MERGE gebruikt voor een parameter met tabelwaarde, @employeesvan het type EmployeeTableType. De inhoud van de @employees tabel wordt hier niet weergegeven.

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

Zie de documentatie en voorbeelden voor de MERGE-instructie voor meer informatie. Hoewel hetzelfde werk kan worden uitgevoerd in een opgeslagen procedureoproep met meerdere stappen met afzonderlijke INSERT- en UPDATE-bewerkingen, is de MERGE-instructie efficiënter. Databasecode kan ook Transact-SQL-aanroepen maken die rechtstreeks gebruikmaken van de MERGE-instructie zonder dat er twee databaseoproepen voor INSERT en UPDATE nodig zijn.

Samenvatting van aanbevelingen

De volgende lijst bevat een overzicht van de aanbevelingen voor batchverwerking die in dit artikel worden besproken:

  • Gebruik buffering en batchverwerking om de prestaties en schaalbaarheid van Azure SQL Database- en Azure SQL Managed Instance-toepassingen te verbeteren.
  • Inzicht in de afwegingen tussen batchverwerking/buffering en tolerantie. Tijdens een rolfout kan het risico dat een niet-verwerkte batch met bedrijfskritieke gegevens verloren gaat, opwegen tegen het prestatievoordeel van batchverwerking.
  • Probeer alle aanroepen naar de database binnen één datacenter te bewaren om de latentie te verminderen.
  • Als u één batchverwerkingstechniek kiest, bieden tabelwaardeparameters de beste prestaties en flexibiliteit.
  • Volg deze algemene richtlijnen voor de snelste invoegprestaties, maar test uw scenario:
    • Gebruik < voor 100 rijen één geparameteriseerde INSERT-opdracht.
    • Voor < 1000 rijen gebruikt u parameters met tabelwaarden.
    • Gebruik SqlBulkCopy voor >= 1000 rijen.
  • Voor bijwerk- en verwijderbewerkingen gebruikt u parameters met tabelwaarden met opgeslagen procedurelogica die de juiste bewerking voor elke rij in de tabelparameter bepaalt.
  • Richtlijnen voor batchgrootte:
    • Gebruik de grootste batchgrootten die zinvol zijn voor uw toepassing en bedrijfsvereisten.
    • De prestatiewinst van grote batches verdelen met de risico's van tijdelijke of onherstelbare fouten. Wat is het gevolg van nieuwe pogingen of verlies van de gegevens in de batch?
    • Test de grootste batchgrootte om te controleren of Azure SQL Database of Azure SQL Managed Instance deze niet weigert.
    • Maak configuratie-instellingen waarmee batchverwerking wordt bepaald, zoals de batchgrootte of het buffertijdvenster. Deze instellingen bieden flexibiliteit. U kunt het batchgedrag in productie wijzigen zonder de cloudservice opnieuw te implementeren.
  • Vermijd parallelle uitvoering van batches die worden uitgevoerd op één tabel in één database. Als u ervoor kiest om één batch over meerdere werkrolthreads te verdelen, voert u tests uit om het ideale aantal threads te bepalen. Na een niet-opgegeven drempelwaarde verminderen meer threads de prestaties in plaats van deze te verhogen.
  • Overweeg om te bufferen op grootte en tijd als een manier om batchverwerking te implementeren voor meer scenario's.

Volgende stappen

Dit artikel is gericht op de manier waarop databaseontwerp- en coderingstechnieken met betrekking tot batchverwerking de prestaties en schaalbaarheid van uw toepassing kunnen verbeteren. Maar dit is slechts één factor in uw algehele strategie. Zie de richtlijnen voor databaseprestaties en prijs- en prestatieoverwegingen voor een elastische pool voor meer manieren om de prestaties en schaalbaarheid te verbeteren.