Повышение производительности приложений Базы данных SQL Azure и Управляемого экземпляра SQL Azure с помощью пакетной обработки

Применимо к: База данных SQL Azure Управляемый экземпляр SQL Azure

Пакетная обработка операций базы данных SQL Azure и Управляемого экземпляра SQL Azure значительно повышает производительность и масштабируемость приложений. В рамках объяснений преимуществ в первой части этой статьи рассматриваются некоторые примеры результатов проверки, в которых сравниваются последовательные и пакетные запросы к базе данных в Базе данных SQL Azure или Управляемом экземпляре SQL Azure. В оставшейся части статьи описаны методы, сценарии и рекомендации, позволяющие успешно использовать пакетную обработку в приложениях Azure.

Почему пакетная обработка так важна для Базы данных SQL Azure и Управляемого экземпляра SQL Azure?

Пакетная обработка вызовов удаленной службы — известная стратегия повышения производительности и масштабируемости. Обработка любого взаимодействия с удаленной службой (например, сериализация, десериализация и передача по сети) имеет фиксированную стоимость. Упаковка нескольких отдельных транзакций в один пакет позволяет сократить эти затраты.

В этой статье документе мы изучим различные стратегии и сценарии пакетной обработки. Хотя эти стратегии также важны для локальных приложений, использующих SQL Server, есть несколько причин уделить внимание использованию пакетной обработки для Базы данных SQL Azure и Управляемого экземпляра SQL Azure.

При доступе к Базе данных SQL Azure и Управляемому экземпляру SQL Azure может быть больше задержки в сети, особенно при доступе к Базе данных SQL Azure или Управляемому экземпляру SQL Azure за пределами одного центра обработки данных Microsoft Azure.
Мультитенантный характер Базе данных SQL Azure и Управляемого экземпляра SQL Azure означает, что эффективность уровня доступа к данным взаимосвязана с общей масштабируемостью базы данных. В ответ на использование ресурсов сверх предопределенных квот в Базе данных SQL Azure и Управляемом экземпляре SQL Azure может уменьшаться пропускная способность или возникать исключения регулирования. Эффективные методы, такие как пакетная обработка, позволяют выполнять больше действий до достижения этих ограничений.
Пакетная обработка также эффективна для архитектур, использующих несколько баз данных (сегментирование). Эффективность каждого обращения к единице базы данных остается главным условием общей масштабируемости.

Одно из преимуществ использования Базе данных SQL Azure или Управляемого экземпляра SQL Azure заключается в том, что вам не нужно управлять серверами, на которых размещены базы данных. Однако эта управляемая инфраструктура также означает, что необходимо будет по-другому продумывать оптимизацию баз данных. Придется отказаться от планов по улучшению оборудования для баз данных или сетевой инфраструктуры. Этими средами управляет Microsoft Azure. Основная область, которую можно контролировать, — взаимодействие приложения с Базой данных SQL Azure и Управляемым экземпляром SQL Azure. Пакетная обработка выступает одним из методов такой оптимизации.

В первой части этой статьи рассматриваются различные приемы пакетной обработки для приложений .NET, использующих Базу данных SQL Azure или Управляемый экземпляр SQL Azure. В последних двух разделах приведены рекомендации по пакетной обработке и связанные с ней сценарии.

Стратегии пакетной обработки

Примечание о результатах измерения времени в этой статье

Примечание.

Результаты не являются тестами, но предназначены для отображения относительной производительности. Временные показатели основаны на среднем значении результата выполнения минимум 10 тестов. Операции представляют собой вставки в пустую таблицу. Эти тесты измерялись до версии 12, и они не обязательно соответствуют пропускной способности, которую можно использовать в базе данных V12 с помощью модели приобретения на основе DTU или модели приобретения виртуальных ядер. Относительное преимущество использования метода пакетной обработки должно быть сходным.

Транзакции

Может показаться странным начинать рассмотрение пакетной обработки с обсуждения транзакций. Однако использование клиентских транзакций имеет некоторый эффект серверной пакетной обработки, который улучшает производительность. Кроме того, транзакции можно добавлять с помощью всего нескольких строк кода. Поэтому они представляют собой быстрый способ повышения производительности последовательных операций.

Рассмотрим следующий код на языке C#, содержащий последовательность из операций вставки и обновления для простой таблицы.

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

Следующий фрагмент кода ADO.NET последовательно выполняет эти операции.

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

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

Лучший способ оптимизации этого кода — реализовывать какую-либо форму клиентской пакетной обработки этих вызовов. Но существует простой способ повысить производительность этого кода, просто упаковав последовательность вызовов в транзакцию. Ниже приведен тот же код, который использует транзакцию.

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

На самом деле, транзакции используются в обоих этих примерах. В первом примере каждый отдельный вызов — это неявная транзакция. Во втором примере в явную транзакцию включены все вызовы. Согласно документации по журналу транзакций с упреждающей записью, записи журнала записываются на диск при фиксации транзакции. Таким образом, включая больше вызовов в транзакцию, можно отложить запись в журнал транзакций до момента фиксации транзакции. В действительности вы включаете пакетную обработку записей в журнал транзакций сервера.

В следующей таблице приведены некоторые результаты ad-hoc-теста. В тестах выполнялись одни и те же последовательные вставки с применением транзакций и без них. Для разнообразия первый набор тестов удаленно запускался с ноутбука в базе данных, размещенной в Microsoft Azure. Второй набор тестов запускался из облачной службы и базы данных, которые размещались в одном центре обработки данных Microsoft Azure (западная часть США). В следующей таблице приведена длительность последовательных вставок (в миллисекундах) с использованием транзакций и без них.

Из локальной среды в Azure:

Операции	Без транзакций (мс)	С транзакциями (мс)
1	130	402
10	120 Б	1226
100	12 662	10 395
1000	128 852	102 917

Между средами в Azure (один центр обработки данных):

Операции	Без транзакций (мс)	С транзакциями (мс)
1	двадцать один	26
10	220	56
100	2145	341
1000	21 479	2756

Примечание.

Результаты не являются эталонами. Дополнительные сведения см. в разделе Примечание о результатах измерения времени в этой статье.

Исходя из результатов предыдущего теста, помещение одной операции в транзакцию на самом деле снижает производительность. Однако по мере увеличения количества операций в рамках одной транзакции повышение производительности становится более заметным. Разница в производительности также более заметна, если все операции выполняются в центре обработки данных Microsoft Azure. Повышенная задержка при использовании Базы данных SQL Azure или Управляемого экземпляра SQL Azure за пределами центра обработки данных Microsoft Azure отодвигает на второй план увеличение производительности при использовании транзакций.

Хотя использование транзакций может увеличить производительность, не прекращайте следовать рекомендациям для транзакций и подключений. Транзакция должна быть как можно короче, а подключение к базе данных после завершения работы необходимо закрывать. Использование инструкции using в предыдущем примере гарантирует закрытие подключения после завершения последующего блока кода.

В предыдущем примере показано, что локальную транзакцию можно добавить в любой код ADO.NET с помощью двух строк. Транзакции позволяют быстро повысить производительность кода, который выполняет последовательные операции вставки, обновления и удаления. Однако для достижения наилучшей производительности рекомендуется изменить код еще больше, чтобы воспользоваться преимуществами клиентской пакетной обработки, например параметрами, которые возвращают табличное значение.

Дополнительные сведения о транзакциях в ADO.NET см. в статье Локальные транзакции.

Возвращающие табличные значения параметры

Параметры, которые возвращают табличное значение, поддерживают определяемые пользователем типы таблиц в качестве параметров в инструкциях, хранимых процедурах и функциях Transact-SQL. Этот метод клиентской пакетной обработки позволяет отправить несколько строк данных в рамках параметра, который возвращает табличное значение . Чтобы использовать такие параметры, необходимо сначала определить тип таблицы. Следующая инструкция Transact-SQL создает тип таблицы с именем MyTableType.

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

В коде вы создаете DataTable с точно такими же именами и типами, как у типа таблицы. Передайте этот DataTable параметр в текстовом запросе или вызове хранимой процедуры. Демонстрация этого метода приведена в следующем примере:

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

В предыдущем примере SqlCommand объект вставляет строки из табличного параметра @TestTvp. Ранее созданный DataTable объект назначается этому параметру SqlCommand.Parameters.Add с помощью метода. Пакетная обработка операций вставки в рамках одного вызова существенно повышает производительность по сравнению с последовательными вставками.

Чтобы совершенствовать предыдущий пример, используйте хранимую процедуру вместо текстовой команды. Следующая команда Transact-SQL создает хранимую процедуру, которая принимает SimpleTestTableType параметр с табличным значением.

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

Затем измените объявление объекта SqlCommand в предыдущем примере кода следующим образом.

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

В большинстве случаев параметры, которые возвращают табличное значение, отличаются аналогичной или более высокой производительностью в сравнении с другими методами пакетной обработки. Табличное значение параметров часто предпочтительнее, так как они являются более гибкими, чем другие параметры. Например, такие способы, как массовое копирование SQL, позволяют вставлять только новые строки. Однако с параметрами, которые возвращают табличное значение, можно использовать логику в хранимой процедуре, чтобы определить, какие строки являются вставками, а какие — обновлениями. Кроме того, табличный тип можно изменить таким образом, чтобы он содержал столбец "Operation", указывающий на действие, которое следует выполнить со строкой (вставка, обновление или удаление).

В следующей таблице приведены результаты ad-hoc-теста (в миллисекундах) по использованию параметров, которые возвращают табличное значение.

Операции	Из локальной среды в Azure (мс)	Один центр обработки данных Azure (мс)
1	124	32
10	131	двадцать пять
100	338	51
1000	2615	382
10 000	23 830	3586

Примечание.

Увеличение производительности благодаря пакетной обработке заметно сразу. В предыдущем последовательном тесте 1000 операций заняло 129 секунд за пределами центра обработки данных и 21 секунд из центра обработки данных. Но при использовании табличных параметров 1000 операций занимает всего 2,6 секунды за пределами центра обработки данных и 0,4 секунды в центре обработки данных.

Дополнительные сведения о параметрах с табличным значением см. в разделе Параметры, которые возвращают табличное значение.

Массовое копирование SQL

Массовое копирование SQL представляет собой еще один способ вставки больших объемов данных в целевую базу данных. Приложения .NET могут использовать SqlBulkCopy класс для выполнения операций массовой вставки. SqlBulkCopy аналогичен по функции инструменту командной строки Bcp.exe или оператору BULK INSERT Transact-SQL. В следующем примере кода показано, как массово скопировать строки в исходнойDataTable, таблице, в целевую таблицу. 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);
    }
}

В некоторых случаях вместо параметров, которые возвращают табличное значение, рекомендуется использовать массовое копирование. См. таблицу сравнения параметров Table-Valued и BULK INSERT операций в статье Table-Valued Параметры.

Результаты специального теста показывают производительность пакетной обработки с использованием SqlBulkCopy в миллисекундах.

Операции	Из локальной среды в Azure (мс)	Один центр обработки данных Azure (мс)
1	433	57
10	441	32
100	636	53
1000	2535	341
10 000	21605	2737

Примечание.

Результаты не являются измерениями производительности. Дополнительные сведения см. в разделе Примечание о результатах измерения времени в этой статье.

При небольших объемах пакетов используемые табличные параметры значений превосходили SqlBulkCopy класс. Однако SqlBulkCopy работал на 12-31% быстрее, чем параметры с табличными значениями, в тестах с 1,000 и 10 000 строками. Как и табличное значение параметров, SqlBulkCopy это хороший вариант для пакетных вставок, особенно при сравнении с производительностью не пакетных операций.

Дополнительные сведения о массовом копировании в ADO.NET см. в разделе Операции массового копирования.

Многострочные параметризованные инструкции INSERT

Альтернатива небольшим пакетам — создание большой параметризованной инструкции INSERT, которая вставляет несколько строк. Использование этого метода показано в следующем примере кода.

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

Этот пример демонстрирует сам принцип. В более реалистичном случае необходимо выполнить цикл с включением необходимых сущностей для одновременного создания строки запроса и параметров команды. Вы ограничены общим количеством 2100 параметров запроса, поэтому это ограничивает общее количество строк, которые можно обработать таким образом.

Ниже приведены результаты ad-hoc-теста (в миллисекундах), которые демонстрируют производительность инструкции вставки этого типа.

Операции	Параметры, которые возвращают табличное значение (мс)	Один оператор INSERT (мс)
1	32	20
10	30	двадцать пять
100	33	51

Примечание.

Этот подход может быть немного быстрее для пакетов, содержащих менее 100 строк. Хотя улучшение и незначительно, использование этого метода может привести к хорошим результатам в конкретном сценарии использования приложения.

Адаптер данных

Класс DataAdapter позволяет изменять DataSet объект, а затем отправлять изменения как INSERTи UPDATEDELETE операции. Если вы используете DataAdapter таким образом, важно отметить, что для каждой отдельной операции выполняются отдельные вызовы. Чтобы повысить производительность, используйте UpdateBatchSize свойство для количества операций, которые следует пакетировать одновременно. Дополнительные сведения см. в статье Выполнение пакетных операций с помощью объектов DataAdapter (ADO.NET).

Entity Framework (Энтити Фреймворк)

Entity Framework Core поддерживает пакетную обработку.

XML

Для полноты мы считаем, что важно говорить о XML в качестве стратегии пакетной обработки. Использование содержимого XML не имеет дополнительных преимуществ по сравнению с другими методами, зато отличается некоторыми недостатками. Этот подход аналогичен применению параметров, которые возвращают табличное значение, но вместо пользовательской таблицы хранимой процедуре передается файл или строка XML. Затем хранимая процедура анализирует команды.

У этого подхода есть несколько недостатков.

Работа с содержимым XML может быть весьма трудоемкой и приводить к ошибкам.
Анализ содержимого XML в базе данных может сильно нагружать ЦП.
В большинстве случаев при использовании этого метода операции выполняются медленнее, чем при использовании параметров, которые возвращают табличное значение.

По этим причинам использование XML для пакетных запросов не рекомендуется.

Размер пакета	Итерации	Параметры, которые возвращают табличное значение (мс)
1000	1	347
500	2	355
100	10	465
50	20	630

Размер пакета [количество итераций]	Два потока (мс)	Четыре потока (мс)	Шесть потоков (мс)
1000 [1]	277	315	266
500 [2]	548	278	256
250 [4]	405	329	265
100 [10]	488	439	391

Сценарии пакетной обработки

В следующих разделах описывается, как использовать параметры, которые возвращают табличное значение, в трех различных сценариях приложения. В первом сценарии показано взаимодействие буферизации и пакетной обработки. Во втором сценарии производительность повышается за счет выполнения операции с основными и подробными данными в одном вызове хранимой процедуры. В последнем сценарии показано, как в операции UPSERT использовать параметры, которые возвращают табличное значение.

Буферизация

Хотя некоторые сценарии явно предполагают пакетную обработку, во многих других сценариях можно использовать преимущество отложенной пакетной обработки. Однако при отложенной обработке также присутствует большой риск потери данных в случае непредвиденного сбоя. Важно понять этот риск и рассмотреть последствия.

Например, представьте веб-приложение, которое отслеживает историю навигации каждого пользователя. При каждом запросе страницы приложение может осуществлять вызов базы данных для записи просмотра страницы пользователем. Однако более высокой производительности и масштабируемости можно достичь, помещая открываемые пользователем страницы в буфер, а затем передавая эти данные в базу данных в виде пакетов. Можно активировать обновление базы данных по истечении определенного времени и/или на основе размера буфера. Например, правило может указать, что пакет должен обрабатываться через 20 секунд или когда буфер достигает 1000 элементов.

В следующем примере кода для обработки буферизованных событий, которые вызваны классом мониторинга, использованы реактивные расширения (Rx) . После заполнения буфера или по истечении времени ожидания в базу данных отправляется пакет данных пользователя с параметром, который возвращает табличное значение.

NavHistoryData Следующий класс моделирует сведения о навигации пользователей. Он содержит основную информацию, например идентификатор пользователя, URL-адрес, к которому получен доступ, а также время доступа.

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

Класс NavHistoryDataMonitor отвечает за буферизацию данных навигации пользователей в базу данных. Он содержит метод, RecordUserNavigationEntryкоторый отвечает путем вызова "OnAdded" события. В следующем коде показано, как логика конструктора использует расширение Rx, чтобы создать наблюдаемую коллекцию на основе события. Затем он подписывается с помощью метода Buffer на эту наблюдаемую коллекцию. Перегрузка указывает, что буфер должен отправляться каждые 20 секунд или 1000 записей.

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

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

Обработчик преобразует все буферизованные элементы в тип, который возвращает табличное значение, а затем передает этот тип в хранимую процедуру, которая обрабатывает пакет. В следующем коде показано полное определение для классов NavHistoryDataEventArgs и NavHistoryDataMonitor.

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

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

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

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

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

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

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

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

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

            cmd.ExecuteNonQuery();
        }
    }
}

Чтобы использовать этот класс буферизации, приложение создает статический NavHistoryDataMonitor объект. Каждый раз, когда пользователь обращается к странице, приложение вызывает NavHistoryDataMonitor.Метод RecordUserNavigationEntry. Затем выполняется логика буферизации, которая отправляет эти записи в базу данных в виде пакетов.

Основная и подробная информация

Параметры с табличным значением полезны для простых INSERT сценариев. Однако этот метод может быть более трудоемким для пакетных вставок при использовании нескольких таблиц. Сценарий с использованием схемы "главный/подчиненный" хорошо это иллюстрирует. Главная таблица определяет базовую сущность. В одной или нескольких таблицах хранятся дополнительные данные о сущности. В этом случае внешний ключ принудительно устанавливает связь подробной информации с уникальной основной сущностью. Рассмотрим упрощенную версию PurchaseOrder таблицы и связанную с ней OrderDetail таблицу. Следующая Transact-SQL создает таблицу PurchaseOrder с четырьмя столбцами: OrderID, OrderDate, CustomerID, и 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 ));

Каждый заказ содержит информацию об одной или нескольких покупках товара. Эти сведения записываются в PurchaseOrderDetail таблице. Следующая Transact-SQL создает таблицу с пятью PurchaseOrderDetail столбцами: OrderID, OrderDetailID, ProductID, UnitPrice и 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 ));

Столбец OrderID в PurchaseOrderDetail таблице должен ссылаться на порядок из PurchaseOrder таблицы. Следующее определение внешнего ключа обеспечивает принудительное применение этого ограничения.

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

Чтобы использовать параметры, которые возвращают табличное значение, необходимо иметь по одному пользовательскому типу таблицы для каждой целевой таблицы.

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

Затем определите хранимую процедуру, которая принимает таблицы этих типов. Эта процедура позволяет приложению выполнять локальную пакетную обработку набора заказов и информации о них в одном вызове. Следующий сценарий Transact-SQL позволяет выполнить полное объявление хранимой процедуры для этого примера заказа на покупку.

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

В этом примере локальная @IdentityLink таблица сохраняет фактические OrderID значения из недавно вставленных строк. Эти идентификаторы порядка отличаются от временных OrderID значений в @orders параметрах с @details табличным значением. По этой причине таблица @IdentityLink затем соединяет значения OrderID из параметра @orders с реальными значениями OrderID для новых строк в таблице PurchaseOrder. После этого таблица @IdentityLink может упростить вставку сведений о заказе с фактическими данными OrderID, которые удовлетворяют ограничению внешнего ключа.

Эту хранимую процедуру можно использовать из кода или из других вызовов Transact-SQL. Примеры кода см. в разделе «Параметры, которые возвращают табличное значение». В следующем Transact-SQL показано, как можно вызвать 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;

Это решение позволяет каждому пакету использовать набор значений OrderID , начинающихся с 1. Эти временные OrderID значения описывают связи в пакете, но фактические OrderID значения определяются во время операции вставки. Можно повторно выполнить операторы, которые использовались в предыдущем примере, и сформировать уникальные заказы в базе данных. Поэтому рассмотрите возможность добавления дополнительного кода или логики базы данных, которые не допустят повторения заказов при использовании этого метода пакетной обработки.

В этом примере показано, что с помощью параметров, которые возвращают табличное значение, в пакет можно включить даже такие сложные операции с базой данных, как операции с основной и подробной информацией.

UPSERT

Другой случай использования пакетной обработки подразумевает одновременное обновление существующих и вставку новых строк. Эта операция иногда называется UPSERT (сочетание обновления и вставки). Вместо того чтобы отдельно вызывать INSERT и UPDATE, можно воспользоваться инструкцией MERGE. Инструкция MERGE может выполнить операции вставки и обновления за один вызов. В инструкции MERGE блокировка работает не так, как при использовании отдельных инструкций INSERT и UPDATE. Протестируйте свои рабочие нагрузки перед развертыванием в рабочей среде.

Параметры, которые возвращают табличное значение, можно использовать с помощью инструкции MERGE для выполнения операций обновления и вставки. Например, рассмотрим упрощенную таблицу Employee со следующими столбцами: 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 ))

В этом примере слияние нескольких сотрудников выполняется на основе уникального свойства SocialSecurityNumber. Сначала создайте определяемый пользователем тип таблицы:

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

Затем создайте хранимую процедуру или напишите код, в котором инструкция MERGE выполняет обновление и вставку. В следующем примере инструкция MERGE используется с параметром с табличным значением @employees типа EmployeeTableType. @employees Содержимое таблицы не отображается здесь.

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

Дополнительную информацию см. в примерах и документации по инструкции MERGE. Хотя такие же действия можно выполнить в рамках вызова многоступенчатой хранимой процедуры с отдельными операциями INSERT и UPDATE, использование инструкции MERGE является более эффективным. Код базы данных также может формировать вызовы Transact-SQL с использованием инструкции MERGE напрямую без необходимости дважды вызывать базу данных, чтобы выполнить операции INSERT и UPDATE.

Сводка рекомендаций

В следующем списке приведена сводка рекомендаций по пакетной обработке, рассмотренной в этой статье.

Используйте буферизацию и пакетную обработку, чтобы повысить производительность и масштабируемость приложений Базы данных SQL Azure и Управляемого экземпляра SQL Azure.
Учитывайте компромиссную природу связи между пакетной обработкой или буферизацией и устойчивостью приложения. Во время сбоя роли риск потери необработанного пакета критически важных бизнес-данных может перевесить прирост в производительности за счет пакетной обработки.
Старайтесь выполнять все вызовы базы данных в пределах одного центра обработки данных для сокращения задержки.
При использовании метода с отправкой одного пакета оптимальную производительность и гибкость можно получить благодаря использованию параметров, которые возвращают табличное значение.
Для максимальной производительности вставки выполните следующие общие рекомендации, но протестируйте сценарий:
- Для < 100 строк используйте одну параметризованную INSERT команду.
- Для < 1000 строк используйте табличные параметры.
- Для >= 1000 строк, используйте SqlBulkCopy.
Для операций обновления и удаления используйте параметры, которые возвращают табличное значение, с логикой хранимых процедур, которая определяет правильную операцию для каждой строки в параметре таблицы.
Рекомендации по размеру пакета:
- Используйте самые большие размеры пакетов, которые соответствуют требованиям приложения и бизнес-требованиям.
- Сбалансируйте прирост производительности за счет использования больших пакетов и риски временных или неожиданных отказов. Учитывайте последствия повторного выполнения операций или потери данных в пакете.
- Проверьте максимальный размер пакета, чтобы убедиться, что База данных SQL Azure или Управляемый экземпляр SQL Azure не отклоняют его.
- Создайте параметры конфигурации, управляющие пакетной обработкой, такие как размер пакета или период выполнения буферизации. Эти параметры дают возможность гибко управлять пакетной обработкой. Можно изменить поведение пакетной обработки в рабочей среде без повторного развертывания облачной службы.
Избегайте параллельного выполнения пакетов, которые используют одну таблицу в одной базе данных. Если вы решили разделить один пакет на несколько рабочих потоков, выполните тесты, чтобы определить оптимальное количество потоков. После достижения неопределенного порогового значения большее число потоков уменьшит производительность, а не повысит ее.
Рассмотрите буферизацию операций по количеству и времени в качестве способа реализации пакетной обработки для нескольких сценариев.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2025-06-13

Поделиться через

Повышение производительности приложений Базы данных SQL Azure и Управляемого экземпляра SQL Azure с помощью пакетной обработки

Почему пакетная обработка так важна для Базы данных SQL Azure и Управляемого экземпляра SQL Azure?

Стратегии пакетной обработки

Примечание о результатах измерения времени в этой статье

Транзакции

Возвращающие табличные значения параметры

Массовое копирование SQL

Многострочные параметризованные инструкции INSERT

Адаптер данных

Entity Framework (Энтити Фреймворк)

XML

Рекомендации по использованию пакетной обработки

Компромиссы

Размер пакета

Параллельная обработка

Связанные факторы производительности

Сценарии пакетной обработки

Буферизация

Основная и подробная информация

UPSERT

Сводка рекомендаций

Связанный контент

Обратная связь

Дополнительные ресурсы