Kötegelés használata az Azure SQL Database és az Azure SQL Managed Instance alkalmazás teljesítményének javítása érdekében

Cikk
07/31/2023

A következőre vonatkozik: Azure SQL Database Felügyelt Azure SQL-példány

Az Azure SQL Database-be és a felügyelt Azure SQL-példányba történő kötegelt műveletek jelentősen javítják az alkalmazások teljesítményét és méretezhetőségét. Az előnyök megismerése érdekében a cikk első része olyan mintateszt-eredményeket mutat be, amelyek összehasonlítják a szekvenciális és kötegelt kéréseket egy Azure SQL Database-ben vagy felügyelt Azure SQL-példányban található adatbázissal. A cikk további része azokat a technikákat, forgatókönyveket és szempontokat mutatja be, amelyek segítenek a kötegelés sikeres használatához az Azure-alkalmazásokban.

Miért fontos a kötegelt feldolgozás az Azure SQL Database és a felügyelt Azure SQL-példány esetében?

A távoli szolgáltatásba irányuló hívások kötegetése jól ismert stratégia a teljesítmény és a méretezhetőség növeléséhez. A távoli szolgáltatásokkal való interakciók, például a szerializálás, a hálózati átvitel és a deszerializálás rögzített feldolgozási költségekkel járnak. A sok különálló tranzakció egyetlen kötegbe való csomagolása minimálisra csökkenti ezeket a költségeket.

Ebben a cikkben különböző kötegelési stratégiákat és forgatókönyveket szeretnénk megvizsgálni. Bár ezek a stratégiák az SQL Servert használó helyszíni alkalmazások esetében is fontosak, az Azure SQL Database-hez és a felügyelt Azure SQL-példányhoz való kötegelés használatának több oka is van:

Az Azure SQL Database és a felügyelt Azure SQL-példány elérése potenciálisan nagyobb hálózati késéssel jár, különösen akkor, ha az Azure SQL Database-hez vagy az Azure SQL Managed Instance-hez ugyanazon a Microsoft Azure-adatközponton kívülről fér hozzá.
Az Azure SQL Database és az Azure SQL Managed Instance több-bérlős jellemzői azt jelentik, hogy az adatelérési réteg hatékonysága korrelál az adatbázis általános méretezhetőségével. Az előre definiált kvótákat meghaladó használat esetén az Azure SQL Database és a felügyelt Azure SQL-példány csökkentheti az átviteli sebességet, vagy szabályozási kivételekkel válaszolhat. Az olyan hatékonysági tényezők, mint a kötegelés, lehetővé teszik, hogy több munkát végezzenek, mielőtt elérnék ezeket a korlátokat.
A kötegelés a több adatbázist (horizontális skálázást) használó architektúrák esetében is hatékony. Az egyes adatbázisegységekkel való interakció hatékonysága továbbra is kulcsfontosságú tényező az általános méretezhetőségben.

Az Azure SQL Database vagy az Azure SQL Managed Instance használatának egyik előnye, hogy nem kell kezelnie az adatbázist üzemeltető kiszolgálókat. Ez a felügyelt infrastruktúra azonban azt is jelenti, hogy másképpen kell gondolnia az adatbázis-optimalizálásokra. A továbbiakban nem lehet javítani az adatbázis hardverét vagy a hálózati infrastruktúrát. A Microsoft Azure szabályozza ezeket a környezeteket. A fő vezérelhető terület az, hogy az alkalmazás hogyan kommunikál az Azure SQL Database-zel és a felügyelt Azure SQL-példányokkal. A kötegelés az egyik ilyen optimalizálás.

A cikk első része az Azure SQL Database-t vagy felügyelt Azure SQL-példányt használó .NET-alkalmazások különböző kötegelési technikáit vizsgálja. Az utolsó két szakasz a kötegelési irányelveket és forgatókönyveket ismerteti.

Kötegelési stratégiák

Megjegyzés az időzítési eredményekről ebben a cikkben

Megjegyzés:

Az eredmények nem teljesítménytesztek, hanem relatív teljesítmény megjelenítésére szolgálnak. Az időzítések átlagosan legalább 10 tesztfuttatáson alapulnak. A műveletek egy üres táblába szúrnak be. Ezeket a teszteket a V12 előtt mérték, és nem feltétlenül felelnek meg az új DTU-szolgáltatási szinteket vagy virtuálismag-szolgáltatási szinteket használó V12-adatbázisokban tapasztalt átviteli sebességnek. A kötegelési technika relatív előnyének hasonlónak kell lennie.

Tranzakciók

Furcsának tűnik a kötegelés áttekintésének megkezdése a tranzakciók megvitatásával. Az ügyféloldali tranzakciók használata azonban finom kiszolgálóoldali kötegelési hatással rendelkezik, amely javítja a teljesítményt. A tranzakciók pedig csak néhány sornyi kóddal adhatók hozzá, így gyors módot biztosítanak a szekvenciális műveletek teljesítményének javítására.

Vegye figyelembe a következő C#-kódot, amely egy egyszerű táblán lévő beszúrási és frissítési műveletek sorozatát tartalmazza.

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

Az alábbi ADO.NET kód egymás után hajtja végre ezeket a műveleteket.

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

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

A kód optimalizálásának legjobb módja a hívások ügyféloldali kötegelésének implementálása. A kód teljesítményének növeléséhez azonban egyszerűen meg lehet növelni a kód teljesítményét úgy, hogy egyszerűen körbefuttatjuk a hívássorozatot egy tranzakcióban. Itt ugyanaz a kód található, amely egy tranzakciót használ.

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

Mindkét példában ténylegesen tranzakciókat használnak. Az első példában minden egyes hívás implicit tranzakció. A második példában egy explicit tranzakció az összes hívást körbefuttatja. Az előreírási tranzakciónapló dokumentációja szerint a rendszer a naplórekordokat kiüríti a lemezre a tranzakció véglegesítésekor. Így ha több hívást is belevesz egy tranzakcióba, a tranzakciónaplóba való írás késleltetheti a tranzakció véglegesítéseig. Gyakorlatilag engedélyezi a kötegelést a kiszolgáló tranzakciónaplójába való íráshoz.

Az alábbi táblázat néhány alkalmi tesztelési eredményt mutat be. A tesztek ugyanazokat a szekvenciális beszúrásokat hajtották végre tranzakciókkal és anélkül. A további szempontok érdekében az első tesztkészlet távolról futott egy laptopról a Microsoft Azure-beli adatbázisba. A második tesztkészlet egy olyan felhőszolgáltatásból és adatbázisból futott, amely mindkettő ugyanabban a Microsoft Azure-adatközpontban (USA nyugati régiójában) található. Az alábbi táblázat a tranzakciókkal és anélkül rendelkező egymást követő beszúrások ezredmásodpercében mutatja be az időtartamot.

Helyszíni és Azure-beli:

Üzemeltetés	Nincs tranzakció (ms)	Tranzakció (ms)
1	130	402
10	1208	1226
100	12662	10395
1000	128852	102917

Azure-ból Azure-ba (ugyanaz az adatközpont):

Üzemeltetés	Nincs tranzakció (ms)	Tranzakció (ms)
1	21	26
10	220	56
100	2145	341
1000	21479	2756

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

Az előző teszteredmények alapján egy tranzakció egyetlen műveletének körbefuttatása ténylegesen csökkenti a teljesítményt. Ha azonban egy tranzakción belül növeli a műveletek számát, a teljesítmény javulása markánsabbá válik. A teljesítménybeli különbség akkor is észrevehetőbb, ha minden művelet a Microsoft Azure-adatközpontban történik. Az Azure SQL Database vagy a Felügyelt Azure SQL-példány Microsoft Azure-adatközponton kívüli használatának megnövekedett késése túlárnyékolja a tranzakciók használatának teljesítménynövekedését.

Bár a tranzakciók használata növelheti a teljesítményt, továbbra is figyelje meg a tranzakciók és kapcsolatok ajánlott eljárásait. Tartsa a lehető legrövidebb ideig a tranzakciót, és zárja be az adatbázis-kapcsolatot a munka befejezése után. Az előző példában szereplő használatutasítás biztosítja, hogy a kapcsolat a következő kódblokk befejeződésekor le legyen zárva.

Az előző példa azt mutatja be, hogy egy helyi tranzakciót adhat hozzá bármely ADO.NET kódhoz két sortal. A tranzakciók gyors módot kínálnak a kód teljesítményének javítására, amely szekvenciális beszúrási, frissítési és törlési műveleteket hajt végre. A leggyorsabb teljesítmény érdekében azonban fontolja meg a kód további módosítását, hogy kihasználhassa az ügyféloldali kötegelés előnyeit, például a táblaértékű paramétereket.

A ADO.NET tranzakcióiról további információt a ADO.NET helyi tranzakciói című témakörben talál.

Ideiglenes értékű paraméterek

A táblaértékű paraméterek támogatják a felhasználó által definiált táblatípusokat paraméterként a Transact-SQL-utasításokban, a tárolt eljárásokban és a függvényekben. Ez az ügyféloldali kötegelési technika lehetővé teszi, hogy több adatsort küldjön a táblaértékű paraméteren belül. Táblaértékkel rendelkező paraméterek használatához először adjon meg egy táblatípust. Az alábbi Transact-SQL utasítás létrehoz egy MyTableType nevű táblázattípust.

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

A kódban létre kell hoznia egy DataTable-t , amely pontosan ugyanazokkal a névvel és típusokkal rendelkezik, mint a táblatípus. Adja át ezt a DataTable-t egy paraméterben egy szöveges lekérdezésben vagy tárolt eljáráshívásban. Az alábbi példa ezt a technikát mutatja be:

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

Az előző példában az SqlCommand objektum egy táblaértékű paraméter sorait szúrja be, @TestTvp. A korábban létrehozott DataTable-objektum az SqlCommand.Parameters.Add metódussal van hozzárendelve ehhez a paraméterhez . A beszúrások egyetlen hívásban történő kötegelése jelentősen növeli a szekvenciális beszúrások teljesítményét.

Az előző példa továbbfejlesztéséhez szövegalapú parancs helyett használjon tárolt eljárást. Az alábbi Transact-SQL parancs létrehoz egy tárolt eljárást, amely a SimpleTestTableType táblaértékű paramétert használja.

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

Ezután módosítsa az sqlcommand objektumdeklarációt az előző kód példájában a következőre.

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

A táblaértékelő paraméterek a legtöbb esetben egyenértékű vagy jobb teljesítménnyel rendelkeznek, mint más kötegelési technikák. A táblaértékű paraméterek gyakran előnyösebbek, mert rugalmasabbak, mint más lehetőségek. Más technikák, például az SQL tömeges másolása például csak új sorok beszúrását teszik lehetővé. A táblaértékkel rendelkező paraméterekkel azonban a tárolt eljárás logikájával meghatározhatja, hogy mely sorok frissítések, és melyek a beszúrások. A táblázat típusa módosítható úgy is, hogy tartalmazzon egy "Művelet" oszlopot, amely jelzi, hogy a megadott sort be kell-e szúrni, frissíteni vagy törölni.

Az alábbi táblázat a táblaértékű paraméterek ezredmásodpercben történő használatára vonatkozó alkalmi teszteredményeket mutatja be.

Üzemeltetés	Helyszíni az Azure-ba (ms)	Azure same datacenter (ms)
1	124	32
10	131	25
100	338	51
1000	2615	382
10000	23830	3586

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

A kötegelésből származó teljesítménynövekedés azonnal nyilvánvaló. Az előző szekvenciális tesztben 1000 művelet 129 másodpercet vett igénybe az adatközponton kívül, 21 másodpercet pedig az adatközponton belül. Táblaértékkel rendelkező paraméterekkel azonban az 1000 művelet csak 2,6 másodpercet vesz igénybe az adatközponton kívül, 0,4 másodpercet pedig az adatközponton belül.

A táblaértékű paraméterekről további információt a Table-Valued Parameters (Táblaérték) paraméterek című témakörben talál.

SQL tömeges másolása

Az SQL tömeges másolása egy másik módja annak, hogy nagy mennyiségű adatot szúrjon be a céladatbázisba. A .NET-alkalmazások az SqlBulkCopy osztály használatával végezhetnek tömeges beszúrási műveleteket. Az SqlBulkCopy függvénye hasonló a parancssori eszközhöz, a Bcp.exe-hez vagy a Transact-SQL utasításhoz, a BULK INSERT-hez. Az alábbi példakód bemutatja, hogyan másolhatja tömegesen a forrásadattábla sorait a céltáblába, a MyTable-ba.

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

Vannak olyan esetek, amikor a tömeges másolást előnyben részesítik a táblaértékkel megadott paraméterekkel szemben. A Table-Valued parameters és a BULK INSERT műveletek összehasonlító táblázatát a Table-Valued Parameters című cikkben találja.

Az alábbi eseti teszteredmények az SqlBulkCopyval történő kötegelés teljesítményét mutatják ezredmásodpercben.

Üzemeltetés	Helyszíni az Azure-ba (ms)	Azure same datacenter (ms)
1	433	57
10	441	32
100	636	53
1000	2535	341
10000	21605	2737

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

Kisebb kötegméretekben a táblaértékű paraméterek használata felülmúlta az SqlBulkCopy osztályt. Az SqlBulkCopy azonban 12–31%-kal gyorsabban teljesített, mint az 1000 és 10 000 sorból álló tesztek táblaértékű paraméterei. A táblaértékkel rendelkező paraméterekhez hasonlóan az SqlBulkCopy is jó választás kötegelt beszúrásokhoz, különösen akkor, ha összehasonlítjuk a nem kötegelt műveletek teljesítményével.

Az ADO.NET tömeges másolásáról további információt a Tömeges másolási műveletek című témakörben talál.

Többsoros paraméteres INSERT-utasítások

A kis kötegek egyik alternatíva egy nagy paraméteres INSERT utasítás létrehozása, amely több sort szúr be. Az alábbi példakód ezt a technikát mutatja be.

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

Ez a példa az alapfogalmat mutatja be. Egy reálisabb forgatókönyv végighaladna a szükséges entitásokon a lekérdezési sztring és a parancsparaméterek egyidejű létrehozásához. Összesen 2100 lekérdezési paraméterre van korlátozva, így ez korlátozza az ilyen módon feldolgozható sorok teljes számát.

Az alábbi alkalmi teszteredmények az ilyen típusú beszúrási utasítás teljesítményét mutatják ezredmásodpercben.

Üzemeltetés	Táblaértékű paraméterek (ms)	Egyutas INSERT (ms)
1	32	20
10	30	25
100	33	51

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

Ez a megközelítés valamivel gyorsabb lehet a 100 sornál kisebb kötegek esetében. Bár a fejlesztés kicsi, ez a technika egy másik lehetőség, amely jól működik az adott alkalmazásforgatókönyvben.

DataAdapter

A DataAdapter osztály lehetővé teszi, hogy módosítson egy Adathalmaz-objektumot , majd a módosításokat INSERT, UPDATE és DELETE műveletként küldje el. Ha ilyen módon használja a DataAdaptert , fontos megjegyezni, hogy minden egyes művelethez külön hívások történnek. A teljesítmény javítása érdekében használja az UpdateBatchSize tulajdonságot az egyszerre kötegelendő műveletek számához. További információ: Batch-műveletek végrehajtása DataAdapters használatával.

Entity Framework

Az Entity Framework Core támogatja a kötegelést.

XML

A teljesség érdekében fontosnak tartjuk, hogy az XML-ről kötegelési stratégiaként beszéljünk. Az XML használata azonban nem jár előnyökkel más módszerekkel és számos hátrányokkal. A módszer hasonló a táblaértékelő paraméterekhez, de egy XML-fájlt vagy sztringet a rendszer a felhasználó által megadott tábla helyett egy tárolt eljárásnak ad át. A tárolt eljárás elemzi a tárolt eljárás parancsait.

Ennek a megközelítésnek számos hátránya van:

Az XML használata nehézkes lehet, és hibalehetőségek is lehetnek.
Az XML elemzése az adatbázisban processzorigényes lehet.
Ez a módszer a legtöbb esetben lassabb, mint a táblaértékkel megadott paraméterek.

Ezért nem ajánlott XML használata kötegelt lekérdezésekhez.

Kötegelési szempontok

Az alábbi szakaszok további útmutatást nyújtanak a kötegelés Azure SQL Database-ben és felügyelt Azure SQL-példányokban való használatához.

Kompromisszumok

Az architektúrától függően a kötegelés a teljesítmény és a rugalmasság közötti kompromisszumot is magában foglalhatja. Vegyük például azt a forgatókönyvet, amikor a szerepkör váratlanul leáll. Ha egy adatsort veszít el, az hatás kisebb, mint a nem megadott sorok nagy kötegének elvesztése. Nagyobb a kockázat, ha puffereli a sorokat, mielőtt elküldené őket az adatbázisba egy megadott időablakban.

A kompromisszum miatt értékelje ki a kötegelési műveletek típusát. A kevésbé kritikus adatokkal agresszívabban (nagyobb kötegekkel és hosszabb időablakokkal) kötegel.

Köteg mérete

A tesztek során általában nem volt előnye a nagy kötegek kisebb adattömbökbe való feltörése. Valójában ez az albontás gyakran lassabb teljesítményt eredményezett, mint egyetlen nagy köteg beküldése. Vegyük például azt a forgatókönyvet, amelyben 1000 sort szeretne beszúrni. Az alábbi táblázat azt mutatja be, hogy mennyi ideig tart táblázatértékkel megadott paraméterekkel 1000 sort beszúrni kisebb kötegekre osztva.

Köteg mérete	Iterációk	Táblaértékű paraméterek (ms)
1000	1	347
500	2	355
100	10	465
50	20	630

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

Láthatja, hogy az 1000 sor legjobb teljesítménye az, ha egyszerre küldi el őket. Más tesztekben (itt nem látható) kisebb teljesítménynövekedés történt egy 10000 sorból álló köteg két 5000-es kötegre való lebontásához. Ezeknek a teszteknek a táblázatsémái azonban viszonylag egyszerűek, ezért az eredmények ellenőrzéséhez el kell végeznie a konkrét adatokon és kötegméreteken végzett teszteket.

Egy másik megfontolandó tényező, hogy ha a teljes köteg túl nagy lesz, előfordulhat, hogy az Azure SQL Database vagy az Azure SQL Managed Instance megköti és elutasítja a köteg véglegesítését. A legjobb eredmény érdekében tesztelje az adott forgatókönyvet, és állapítsa meg, hogy van-e ideális kötegméret. Konfigurálhatja a köteg méretét futásidőben, hogy a teljesítmény vagy a hibák alapján gyors módosításokat hajthasson végre.

Végül egyensúlyozza ki a köteg méretét a kötegeléshez kapcsolódó kockázatokkal. Ha átmeneti hibák lépnek fel, vagy a szerepkör meghiúsul, fontolja meg a művelet újrapróbálkozásának vagy a kötegben lévő adatok elvesztésének következményeit.

Párhuzamos feldolgozás

Mi a teendő, ha a kötegméret csökkentésének módszerét alkalmazta, de több szálat használt a munka végrehajtásához? A teszteink ismét azt mutatták, hogy több kisebb többszálú köteg jellemzően rosszabbul teljesített, mint egy nagyobb köteg. Az alábbi teszt 1000 sort próbál beszúrni egy vagy több párhuzamos kötegbe. Ez a teszt azt mutatja be, hogy a több egyidejű köteg ténylegesen csökkentette a teljesítményt.

Kötegelt méret [Iterációk]	Két szál (ms)	Négy szál (ms)	Hat szál (ms)
1000 [1]	277	315	266
500 [2]	548	278	256
250 [4]	405	329	265
100 [10]	488	439	391

Megjegyzés:

Az eredmények nem viszonyítási pontok. Ebben a cikkben az időzítési eredményekről szóló megjegyzést tekintheti meg.

A teljesítmény párhuzamosság miatti romlásának számos lehetséges oka lehet:

Egy helyett több egyidejű hálózati hívás is van.
Egy tábla több művelete versengést és blokkolást eredményezhet.
A többszálúság többletterheléssel jár.
A több kapcsolat megnyitásának költsége meghaladja a párhuzamos feldolgozás előnyeit.

Ha különböző táblákat vagy adatbázisokat céloz meg, ezzel a stratégiával némi teljesítménynövekedés érhető el. Ehhez a megközelítéshez az adatbázis-skálázás vagy összevonások forgatókönyve lenne. A horizontális skálázás több adatbázist használ, és különböző adatokat irányít az egyes adatbázisokhoz. Ha minden kis köteg egy másik adatbázisba kerül, akkor a műveletek párhuzamos végrehajtása hatékonyabb lehet. A teljesítménynövekedés azonban nem elég jelentős ahhoz, hogy alapul szolgáljon az adatbázis-skálázás megoldásban való használatának eldöntéséhez.

Egyes tervekben a kisebb kötegek párhuzamos végrehajtása a kérelmek nagyobb átviteli sebességét eredményezheti egy terhelés alatt álló rendszerben. Ebben az esetben még ha gyorsabb is egy nagyobb köteg feldolgozása, több köteg párhuzamos feldolgozása hatékonyabb lehet.

Ha párhuzamos végrehajtást használ, fontolja meg a feldolgozószálak maximális számának szabályozását. A kisebb szám kisebb versengést és gyorsabb végrehajtási időt eredményezhet. Vegye figyelembe azt a további terhelést is, amelyet ez a céladatbázison helyez el a kapcsolatokban és a tranzakciókban is.

Az adatbázis teljesítményével kapcsolatos tipikus útmutatás a kötegelésre is hatással van. A beszúrási teljesítmény például csökken a nagy elsődleges kulccsal vagy sok nemclustered indexel rendelkező táblák esetében.

Ha a táblaértékű paraméterek tárolt eljárást használnak, az eljárás elején használhatja a SET NOCOUNT ON parancsot. Ez az utasítás letiltja az eljárás érintett sorainak számának visszatérését. A tesztek során azonban a SET NOCOUNT ON használata nem volt hatással vagy csökkent a teljesítményre. A teszt által tárolt eljárás egyszerű volt, egyetlen INSERT paranccsal a táblaértékkel rendelkező paraméterből. Lehetséges, hogy az összetettebb tárolt eljárások is profitálnak ebből az utasításból. Ne feltételezze azonban, hogy a SET NOCOUNT ON hozzáadása a tárolt eljáráshoz automatikusan javítja a teljesítményt. A hatás megértéséhez tesztelje a tárolt eljárást a SET NOCOUNT ON utasítással és anélkül.

Kötegelési forgatókönyvek

A következő szakaszok azt mutatják be, hogyan használhatók táblaértékű paraméterek három alkalmazásforgatókönyvben. Az első forgatókönyv bemutatja, hogyan működik együtt a pufferelés és a kötegelés. A második forgatókönyv a fő részletes műveletek egyetlen tárolt eljáráshívásban történő végrehajtásával javítja a teljesítményt. Az utolsó forgatókönyv bemutatja, hogyan használható táblaértékkel rendelkező paraméterek egy UPSERT műveletben.

Pufferelés

Bár vannak olyan forgatókönyvek, amelyek egyértelműen alkalmasak a kötegelésre, számos olyan forgatókönyv létezik, amely a késleltetett feldolgozással kihasználhatja a kötegelés előnyeit. A késleltetett feldolgozás azonban nagyobb kockázatot is hordoz, hogy váratlan hiba esetén elvesznek az adatok. Fontos megérteni ezt a kockázatot, és figyelembe venni a következményeket.

Vegyük például azt a webalkalmazást, amely nyomon követi az egyes felhasználók navigációs előzményeit. Az alkalmazás minden lapkérelem esetén létrehozhat egy adatbázis-hívást, amely rögzíti a felhasználó oldalnézetét. A nagyobb teljesítményt és méretezhetőséget azonban úgy érheti el, hogy puffereli a felhasználók navigációs tevékenységeit, majd kötegekben elküldi ezeket az adatokat az adatbázisnak. Az adatbázis frissítését az eltelt idő és/vagy pufferméret alapján aktiválhatja. Egy szabály például megadhatja, hogy a köteg feldolgozása 20 másodperc után történjen, vagy amikor a puffer eléri az 1000 elemet.

Az alábbi kód példa reaktív bővítményeket – Rx-et használ a figyelési osztály által létrehozott pufferelt események feldolgozásához. A puffer kitöltése vagy időtúllépése esetén a rendszer a felhasználói adatok kötegét táblaértékkel rendelkező paraméterrel továbbítja az adatbázisnak.

A következő NavHistoryData osztály modellozza a felhasználó navigációs adatait. Alapvető információkat tartalmaz, például a felhasználói azonosítót, a elért URL-címet és a hozzáférési időt.

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

A NavHistoryDataMonitor osztály feladata a felhasználói navigációs adatok adatbázisba való pufferelése. Tartalmaz egy RecordUserNavigationEntry metódust, amely egy OnAdded esemény emelésével válaszol. Az alábbi kód azt a konstruktori logikát mutatja be, amely Rx használatával hoz létre megfigyelhető gyűjteményt az esemény alapján. Ezután előfizet erre a megfigyelhető gyűjteményre a Puffer metódussal. A túlterhelés azt határozza meg, hogy a puffert 20 másodpercenként vagy 1000 bejegyzésenként kell elküldeni.

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

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

A kezelő az összes pufferelt elemet táblaértékű típussá alakítja, majd ezt a típust egy tárolt eljárásra továbbítja, amely feldolgozza a köteget. Az alábbi kód a NavHistoryDataEventArgs és a NavHistoryDataMonitor osztályok teljes definícióját tartalmazza.

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

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

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

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

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

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

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

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

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

            cmd.ExecuteNonQuery();
        }
    }
}

A pufferelési osztály használatához az alkalmazás létrehoz egy statikus NavHistoryDataMonitor-objektumot. Minden alkalommal, amikor egy felhasználó hozzáfér egy laphoz, az alkalmazás meghívja a NavHistoryDataMonitor.RecordUserNavigationEntry metódust. A pufferelési logika folytatja, hogy ezeket a bejegyzéseket kötegekben küldje el az adatbázisnak.

Mesteralakzat részletei

A táblaértékű paraméterek egyszerű INSERT-forgatókönyvekhez hasznosak. A több táblát tartalmazó kötegbe szúrások azonban nagyobb kihívást jelenthetnek. A "master/detail" forgatókönyv jó példa. A főtábla azonosítja az elsődleges entitást. Egy vagy több részletes tábla több adatot tárol az entitásról. Ebben az esetben az idegenkulcs-kapcsolatok egy egyedi fő entitáshoz kényszerítik a részletek kapcsolatát. Fontolja meg egy PurchaseOrder-tábla és a hozzá tartozó OrderDetail tábla egyszerűsített verzióját. A következő Transact-SQL négy oszlopból hozza létre a PurchaseOrder táblát: OrderID, OrderDate, CustomerID és 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 ))

Minden megrendelés egy vagy több termékvásárlást tartalmaz. Ezeket az információkat a PurchaseOrderDetail tábla rögzíti. A következő Transact-SQL öt oszlopból hozza létre a PurchaseOrderDetail táblát: OrderID, OrderDetailID, ProductID, UnitPrice és 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 ))

A PurchaseOrderDetail tábla OrderID oszlopának hivatkoznia kell egy rendelésre a PurchaseOrder táblából. Ezt a kényszert egy idegen kulcs alábbi definíciója kényszeríti ki.

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

A táblaértékkel rendelkező paraméterek használatához minden céltáblához egy felhasználó által definiált táblatípussal kell rendelkeznie.

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

Ezután definiáljon egy tárolt eljárást, amely elfogadja az ilyen típusú táblákat. Ez az eljárás lehetővé teszi, hogy az alkalmazás helyileg kötegelje a rendelések és rendelések részleteit egyetlen hívásban. A következő Transact-SQL tartalmazza a teljes tárolt eljárás deklarációját ehhez a megrendelési példához.

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

Ebben a példában a helyileg definiált @IdentityLink tábla az újonnan beszúrt sorok tényleges OrderID-értékeit tárolja. Ezek a rendelésazonosítók eltérnek a táblaértékelő paraméterek ideiglenes OrderID-értékeitől @orders@details . Ezért a @IdentityLink tábla ezután összekapcsolja a paraméter OrderID-értékeit @orders a PurchaseOrder tábla új sorainak valós OrderID-értékeivel. A lépés után a @IdentityLink táblázat megkönnyítheti a rendelés részleteinek beszúrását a tényleges OrderID azonosítóval, amely megfelel az idegenkulcs-korlátozásnak.

Ez a tárolt eljárás kódból vagy más Transact-SQL-hívásokból is használható. A példakódért tekintse meg a dokumentum táblaértékkel rendelkező paramétereinek szakaszát. Az alábbi Transact-SQL bemutatja, hogyan hívhatja meg a sp_InsertOrdersBatch.

declare @orders as PurchaseOrderTableType
declare @details as PurchaseOrderDetailTableType

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

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

exec sp_InsertOrdersBatch @orders, @details

Ez a megoldás lehetővé teszi, hogy minden köteg 1-nél kezdődő OrderID-értékeket használjon. Ezek az ideiglenes OrderID-értékek a kötegben lévő kapcsolatokat írják le, de a tényleges OrderID-értékek a beszúrási művelet időpontjában lesznek meghatározva. Az előző példában szereplő utasításokat többször is futtathatja, és egyedi rendeléseket hozhat létre az adatbázisban. Ezért érdemes lehet több olyan kód- vagy adatbázislogikát hozzáadni, amely megakadályozza az ismétlődő rendeléseket a kötegelési technika használatakor.

Ez a példa azt mutatja be, hogy a még összetettebb adatbázisműveletek, például a master-detail műveletek táblaértékű paraméterekkel kötegelhetők.

UPSERT

Egy másik kötegelési forgatókönyv magában foglalja a meglévő sorok egyidejű frissítését és új sorok beszúrását. Ezt a műveletet néha "UPSERT" (update + insert) műveletnek is nevezik. Az INSERT és AZ UPDATE különálló hívásai helyett a MERGE utasítás megfelelő csere lehet. A MERGE utasítás egyetlen hívásban is végrehajthat beszúrási és frissítési műveleteket. A MERGE utasítás zárolási mechanikája eltér a különálló INSERT és UPDATE utasításoktól. Az éles környezetben való üzembe helyezés előtt tesztelje az adott számítási feladatokat.

A table-valued paraméterek a MERGE utasítással használhatók a frissítések és beszúrások végrehajtásához. Vegyük például egy egyszerűsített Alkalmazott táblát, amely a következő oszlopokat tartalmazza: 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 ))

Ebben a példában azt a tényt használhatja, hogy a SocialSecurityNumber egyedi, ha több alkalmazott EGYESÍTÉSét hajtja végre. Először hozza létre a felhasználó által definiált táblatípust:

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

Ezután hozzon létre egy tárolt eljárást vagy egy olyan kódot, amely a MERGE utasítást használja a frissítés végrehajtásához és beszúrásához. Az alábbi példa egy EmployeeTableType típusú táblaértékelő paraméter @employeesMERGE utasítását használja. A táblázat tartalma @employees itt nem jelenik meg.

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

További információkért tekintse meg a MERGE utasítás dokumentációját és példáit. Bár ugyanez a munka elvégezhető egy többlépéses tárolt eljáráshívásban külön INSERT és UPDATE műveletekkel, a MERGE utasítás hatékonyabb. Az adatbáziskód olyan Transact-SQL-hívásokat is létrehozhat, amelyek közvetlenül a MERGE utasítást használják anélkül, hogy két adatbázis-hívásra lenne szükség az INSERT és az UPDATE használatához.

Javaslatok összefoglalása

Az alábbi lista összefoglalja a cikkben tárgyalt kötegelési javaslatokat:

Pufferelés és kötegelés használatával növelheti az Azure SQL Database és az Azure SQL Managed Instance-alkalmazások teljesítményét és méretezhetőségét.
Ismerje meg a kötegelés/pufferelés és a rugalmasság közötti kompromisszumot. Szerepkörhiba esetén az üzleti szempontból kritikus fontosságú adatok feldolgozatlan kötegének elvesztésének kockázata meghaladja a kötegelés teljesítménybeli előnyét.
A késés csökkentése érdekében próbálja meg egyetlen adatközponton belül tartani az adatbázis összes hívását.
Ha egyetlen kötegelési technikát választ, a táblaértékelő paraméterek a legjobb teljesítményt és rugalmasságot biztosítják.
A leggyorsabb beszúrási teljesítmény érdekében kövesse az alábbi általános irányelveket, de tesztelje a forgatókönyvet:
- 100 sornál < használjon egyetlen paraméteres INSERT-parancsot.
- 1000 sornál < használjon táblaértékkel megadott paramétereket.
- = >1000 sor esetén használja az SqlBulkCopyt.
Frissítési és törlési műveletekhez használjon táblaértékű paramétereket tárolt eljáráslogikával, amelyek meghatározzák a megfelelő műveletet a táblaparaméter egyes soraiban.
Kötegméretre vonatkozó irányelvek:
- Használja a legnagyobb kötegméreteket, amelyek megfelelnek az alkalmazás és az üzleti követelményeknek.
- Egyensúlyozza ki a nagy kötegek teljesítménynövekedését az átmeneti vagy katasztrofális hibák kockázatával. Mi a kötegben lévő adatok újrapróbálkozásának vagy elvesztésének a következménye?
- Tesztelje a legnagyobb kötegméretet annak ellenőrzéséhez, hogy az Azure SQL Database vagy az Azure SQL Managed Instance nem utasítja-e el.
- A kötegelést vezérlő konfigurációs beállításokat hozhat létre, például a köteg méretét vagy a pufferelési időablakot. Ezek a beállítások rugalmasságot biztosítanak. A kötegelési viselkedést az éles környezetben a felhőszolgáltatás újbóli üzembe helyezése nélkül módosíthatja.
Kerülje a kötegek párhuzamos végrehajtását, amelyek egyetlen táblán működnek egy adatbázisban. Ha úgy dönt, hogy egyetlen köteget több munkaszálra oszt, futtassa a teszteket az ideális szálszám meghatározásához. Egy meghatározatlan küszöbérték után több szál csökkenti a teljesítményt ahelyett, hogy növelné azt.
Fontolja meg a méret és az idő pufferelését a kötegelés implementálásának módjaként további forgatókönyvek esetén.

További lépések

Ez a cikk arra összpontosított, hogy az adatbázis-tervezés és a kötegeléshez kapcsolódó kódolási technikák hogyan javíthatják az alkalmazás teljesítményét és méretezhetőségét. Ez azonban csak egy tényező az általános stratégiában. A teljesítmény és a méretezhetőség javításának további módjaiért tekintse meg az adatbázis teljesítményére vonatkozó útmutatást , valamint a rugalmas készlet ár- és teljesítményproblémáit.

Kötegelés használata az Azure SQL Database és az Azure SQL Managed Instance alkalmazás teljesítményének javítása érdekében

Miért fontos a kötegelt feldolgozás az Azure SQL Database és a felügyelt Azure SQL-példány esetében?

Kötegelési stratégiák

Megjegyzés az időzítési eredményekről ebben a cikkben

Tranzakciók

Ideiglenes értékű paraméterek

SQL tömeges másolása

Többsoros paraméteres INSERT-utasítások

DataAdapter

Entity Framework

XML

Kötegelési szempontok

Kompromisszumok

Köteg mérete

Párhuzamos feldolgozás

Kötegelési forgatókönyvek

Pufferelés

Mesteralakzat részletei

UPSERT

Javaslatok összefoglalása

További lépések

Visszajelzés

Visszajelzés

További források

Kötegelés használata az Azure SQL Database és az Azure SQL Managed Instance alkalmazás teljesítményének javítása érdekében

Miért fontos a kötegelt feldolgozás az Azure SQL Database és a felügyelt Azure SQL-példány esetében?

Kötegelési stratégiák

Megjegyzés az időzítési eredményekről ebben a cikkben

Tranzakciók

Ideiglenes értékű paraméterek

SQL tömeges másolása

Többsoros paraméteres INSERT-utasítások

DataAdapter

Entity Framework

XML

Kötegelési szempontok

Kompromisszumok

Köteg mérete

Párhuzamos feldolgozás

Kapcsolódó teljesítménytényezők

Kötegelési forgatókönyvek

Pufferelés

Mesteralakzat részletei

UPSERT

Javaslatok összefoglalása

További lépések

Visszajelzés

Visszajelzés

További források