Экспорт (0) Печать
Развернуть все

Методы пакетной обработки в приложениях базы данных SQL в Azure

Обновлено: Январь 2015 г.

Авторы: Джейсон Рот (Jason Roth),Силвано Кориани (Silvano Coriani), Трент Свонсон (Trent Swanson) (Full Scale 180 Inc)

Рецензенты: Конор Каннингхэм (Conor Cunningham), Майкл Томасси (Michael Thomassy)

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

  1. Почему пакетная обработка так важна при работе с базой данных SQL?

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

    1. Транзакции

    2. Параметры, возвращающие табличные значения

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

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

    5. DataAdapter

    6. Entity Framework

    7. XML

  3. Вопросы пакетной обработки

    1. Компромисс

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

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

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

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

    1. Буферизация

    2. Основные и подробные сведения

    3. UPSERT

  5. Рекомендации

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

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

  • Потенциально большая задержка в сети при обращении к база данных SQL, особенно при доступе к база данных SQL из-за пределов центра обработки данных .

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

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

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

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

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

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

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

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

 

Операции Из локальной системы в : (один центр обработки данных)

Без транзакций (мс)

С транзакциями (мс)

Без транзакций (мс)

С транзакциями (мс)

1

130

402

21

26

10

1208

1226

220

56

100

12662

10395

2145

341

1000

128852

102917

21479

2756

noteПримечание
Результаты не являются чистыми замерами производительности, но показывают относительную производительность. Временные показания основаны на среднем значении по крайней мере из 10 запусков тестов. Операции — вставки в пустую таблицу.

Заключение одной операции в транзакцию на самом деле снижает производительность. Однако по мере увеличения количества операций в рамках одной транзакции улучшение производительности становится более заметным. Разница в производительности также более заметна, когда все операции выполняются в одном центре обработки данных . Повышенная задержка при использовании база данных SQL из-за пределов центра обработки данных перекрывает увеличение производительности при использовании транзакций.

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

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

Дополнительные сведения о транзакциях в 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, позволяют делать вставку только новых строк. Однако с возвращающими табличное значение параметрами можно использовать логику в хранимой процедуре, чтобы определить, какие строки являются вставками, а какие обновлениями. Табличный тип также можно изменить так, чтобы он содержал столбец «Операция», который показывает, должна указанная строка вставляться, обновляться или удаляться.

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

 

Операции Локальные в (мс) (один центр обработки данных, мс)

1

124

32

10

131

25

100

338

51

1000

2615

382

10000

23830

3586

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

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

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

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

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

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

 

Операции Локальные в (мс) (один центр обработки данных, мс)

1

433

57

10

441

32

100

636

53

1000

2535

341

10000

21605

2737

noteПримечание
Результаты не являются чистыми замерами производительности, но показывают относительную производительность. Временные показания основаны на среднем значении по крайней мере из 10 запусков тестов. Операции — вставки в пустую таблицу.

В пакетах меньшего размера возвращающие табличные значения параметры превзошли класс SqlBulkCopy. Однако SqlBulkCopy выполнялось на 12–31 % быстрее, чем тест с параметрами, возвращающими табличные значения, для 1000 и 10 000 строк. Как и параметры, возвращающие табличное значение, SqlBulkCopy — хороший вариант для пакетных вставок, особенно по сравнению с операциями, в которых не используются пакеты.

Дополнительные сведения о массовом копировании в ADO.NET см. в разделе Массовое копирование в SQL Server.

Альтернатива небольшим пакетам — построение большой параметризованной инструкции 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 параметров запроса, в результате чего общее число строк, которое можно обработать таким образом, ограничивается.

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

 

Операции Возвращающие табличные значения параметры (мс) Одна инструкция INSERT (мс)

1

32

20

10

30

25

100

33

51

noteПримечание
Результаты не являются точными замерами производительности. Временные показания основаны на среднем значении по крайней мере из 30 запусков тестов. Операции — вставки в пустую таблицу.

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

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

Entity Framework в настоящее время не поддерживает пакетную обработку. Разные разработчики из сообщества попытались обойти это ограничение, например переопределяя метод SaveChanges. Однако подобные решения обычно сложны и подогнаны под приложение и модель данных. Проект Entity Framework Сodeplex в данный момент содержит страницу с обсуждением запроса этой функции. Для просмотра данного обсуждения см. запись Заметки о встрече по проектированию — 2 августа 2012 г.

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

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

  1. Работа с XML может быть весьма трудоемкой и вести к ошибкам.

  2. Синтаксический анализ XML в базе данных может сильно нагружать процессор.

  3. В большинстве случаев этот метод медленнее, чем параметры, возвращающие табличные значения.

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

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

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

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

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

 

Размер пакета [итерации] Возвращающие табличные значения параметры (мс)

1000 [1]

347

500 [2]

355

100 [10]

465

50 [20]

630

noteПримечание
Результаты не являются точными замерами производительности. Локальные тесты запускались с ноутбука по беспроводной сети. Временные показания основаны на среднем значении по крайней мере из 10 запусков тестов. Операции — вставки в пустую таблицу.

Видно, что наилучшая производительность для 1000 строк достигается, когда они отправляются все сразу. В других тестах (не показанных здесь) имеется небольшой прирост производительности при разделении пакета в 10000 строк на два пакета в 5000 строк. Но схема таблицы для тестов довольно проста, и вам следует провести тесты с учетом размеров пакета ваших и данных.

Другой фактор, который стоит учесть, заключается в том, что, если конечный пакет будет слишком большим, база данных SQL может отказаться зафиксировать пакет. Для достижения наилучших результатов проводите тестирование, чтобы определить идеальный размер пакета. Сделайте размер пакета настраиваемым во время выполнения, чтобы его можно было быстро регулировать с учетом показателей производительности или ошибок.

Наконец, сбалансируйте размер пакета и риски, связанные с пакетной обработкой. Если есть временные ошибки или роль завершается сбоем, учтите возможные последствия — нужно либо повторить операцию, либо потерять данные из пакета.

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

 

Размер пакета [итерации] Два потока (мс) Четыре потока (мс) Шесть потоков (мс)

1000 [1]

277

315

266

500 [2]

548

278

256

250 [4]

405

329

265

100 [10]

488

439

391

noteПримечание
Результаты не являются точными замерами производительности. Временные показания основаны на среднем значении по крайней мере из 10 запусков тестов. Операции — вставки в пустую таблицу. В тестах параллельной обработки использовалась библиотека параллельных задач .NET.

Существует несколько возможных причин ухудшения производительности из-за параллелизма.

  1. Выполняется несколько одновременных сетевых вызовов вместо одного.

  2. Выполнение нескольких обращений с одной таблицей может привести к состязаниям и блокировке.

  3. Есть и издержки, связанные с многопотоковостью.

  4. Расходы на открытие нескольких подключений перевешивают выгоду от параллельной обработки.

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

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

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

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

Если возвращающие табличные значения параметры используют хранимую процедуру, можно использовать команду SET NOCOUNT ON в начале процедуры. Эта инструкция подавляет возврат числа изменяемых в процедуре строк. Однако в наших тестах использование SET NOCOUNT ON либо не дало эффекта, либо уменьшило производительность. Хранимая процедура для теста была простой: с одной командой INSERT из параметра, возвращающего табличное значение. Возможно, что более сложные хранимые процедуры получат преимущество от использования этой инструкции. Но не стоит думать, что добавление SET NOCOUNT ON к хранимой процедуре автоматически повысит производительность. Чтобы понять производимый эффект, проверьте хранимую процедуру с инструкцией SET NOCOUNT ON и без нее.

В следующих подразделах описывается, как использовать возвращающие табличное значение параметры в трех различных случаях. В первом сценарии показано, как совместно использовать пакетную обработку и буферизацию. Во втором сценарий производительность повышается за счет выполнения операции с основными данными в одном вызове хранимой процедуры. В последнем сценарии показано использование параметров, возвращающих табличные значения, в операции 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 void RecordUserNavigationEntry(NavHistoryData data)
    {    
        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 с 4 столбцами: 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 с 5 столбцами: 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].[PurchaseOrderDetail2]  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 (update + insert, обновление и вставка). Вместо выполнения отдельных вызовов к INSERT и UPDATE вызывается инструкция MERGE, которая лучше подходит для этой задачи. Инструкция MERGE может выполнить операции вставки и обновления за один вызов.

Возвращающие табличное значение параметры можно использовать с помощью инструкции 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.

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

  • Старайтесь делать все вызовы к базе данных в пределах одного центра обработки данных для сокращения задержки.

  • Если вы выбрали метод с отправкой одного пакета, возвращающие табличное значение параметры дают оптимальную производительность и гибкость.

  • Для обеспечения самого быстрого выполнения операций вставки следуйте следующим рекомендациям, но не забывайте тестировать решение.

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

  • Рекомендации по размеру пакета.

    • Используйте самые большие размеры пакетов, которые соответствуют нуждам вашего приложения и бизнес-требованиям.

    • Сбалансируйте прирост производительности от больших пакетов с рисками временных или неожиданных отказов. Каковы последствия повторного выполнения операций или потери данных в пакете?

    • Протестируйте максимальный размер пакета и убедитесь, что база данных SQL не отклоняет его.

    • Создайте параметры конфигурации, управляющие пакетной обработкой, такие как размер пакета или период выполнения буферизации. Данные параметры дают возможность гибко управлять пакетной обработкой. Можно изменить поведение пакетной обработки в производственной среде без повторного развертывания облачной службы.

  • Избегайте параллельного выполнения пакетов, которые работают с одной таблицей в одной базе данных. Если вы решили делить один пакет на несколько рабочих потоков, выполните тесты, чтобы определить оптимальное количество потоков. После неопределенного порога большее число потоков уменьшит производительность, а не повысит ее.

  • Рассмотрите буферизацию операций по количеству и времени в качестве способа реализации пакетной обработки для своего сценария.

См. также

Показ:
© 2015 Microsoft