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

Статья
06/02/2023

Применимо к:Управляемому экземпляру Базы данных 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, и их показатели не обязательно соответствуют пропускной способности базы данных версии 12, получаемой при использовании новых уровней служб на основе DTU или на основе виртуальных ядер. Относительное преимущество использования метода пакетной обработки должно быть сходным.

Transactions

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

Рассмотрим следующий код на языке 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:

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

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

Operations	Без транзакций (мс)	С транзакциями (мс)
1	21	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-теста (в миллисекундах) по использованию параметров, которые возвращают табличное значение.

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

Примечание.

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

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

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

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

В некоторых случаях вместо параметров, которые возвращают табличное значение, рекомендуется использовать массовое копирование. Сравнительная таблица параметров с табличным значением и операций BULK INSERT приведена в статье Использование параметров, возвращающих табличные значения (компонент Database Engine).

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

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

Примечание.

В случае с пакетами меньшего размера использование параметров, которые возвращают табличное значение, приводит к повышению производительности в сравнении с использованием класса SqlBulkCopy . Однако при выполнении тестов на 1000 и 10 000 строк с использованием класса SqlBulkCopy операции выполнялись на 12–31 % быстрее, чем при использовании параметров, которые возвращают табличное значение. Аналогично параметрам, которые возвращают табличное значение, класс 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-теста (в миллисекундах), которые демонстрируют производительность инструкции вставки этого типа.

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

Примечание.

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

DataAdapter

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

Entity Framework

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

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 не отклоняет его.
- Создайте параметры конфигурации, управляющие пакетной обработкой, такие как размер пакета или период выполнения буферизации. Эти параметры дают возможность гибко управлять пакетной обработкой. Можно изменить поведение пакетной обработки в рабочей среде без повторного развертывания облачной службы.
Избегайте параллельного выполнения пакетов, которые используют одну таблицу в одной базе данных. Если вы решили разделить один пакет на несколько рабочих потоков, выполните тесты, чтобы определить оптимальное количество потоков. После достижения неопределенного порогового значения большее число потоков уменьшит производительность, а не повысит ее.
Рассмотрите буферизацию операций по количеству и времени в качестве способа реализации пакетной обработки для нескольких сценариев.

Дальнейшие действия

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

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

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

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

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

Transactions

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

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

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

DataAdapter

Entity Framework

XML

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

Компромиссы

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

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

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

Буферизация

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

UPSERT

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

Дальнейшие действия

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

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

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

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

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

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

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

Transactions

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

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

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

DataAdapter

Entity Framework

XML

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

Компромиссы

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

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

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

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

Буферизация

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

UPSERT

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

Дальнейшие действия

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

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

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