Оптимизация передачи списка в SQL Stored Procedure

Быстрый ответ

Для передачи списка List<> в хранимую процедуру SQL наилучшим решением будет использование табличных параметров (TVP). Этот процесс можно упростить до трех шагов:

1. Вначале определите табличный тип данных в SQL:

   SQL

   Скопировать код

   CREATE TYPE dbo.ListType AS TABLE (Value INT); -- Готово к хранению чисел!

   Запустить код

2. Включите TVP в свою хранимую процедуру:

   SQL

   Скопировать код

   CREATE PROCEDURE dbo.InsertList @List dbo.ListType READONLY AS BEGIN
       INSERT INTO YourTable SELECT * FROM @List; -- Вставка выполнится мгновенно!
   END;

   Запустить код

3. Создайте объект DataTable в C# и заполните его своим списком, затем передайте его в хранимую процедуру:

   csharp

   Скопировать код

   DataTable table = new DataTable();
   table.Columns.Add("Value", typeof(int)); // Добавляем столбец для чисел
   foreach(var item in yourList) {
       table.Rows.Add(item); // Заполняем таблицу поэлементно
   }
   SqlParameter param = new SqlParameter {
       ParameterName = "@List",
       SqlDbType = SqlDbType.Structured,
       TypeName = "dbo.ListType",
       Value = table // Отправляем таблицу в SQL Server
   };
   SqlCommand command = new SqlCommand("InsertList", connection) {
       CommandType = CommandType.StoredProcedure
   };
   command.Parameters.Add(param);
   command.ExecuteNonQuery(); // Запускаем процесс!

   Запустить код

Применение этого метода значительно упрощает код и повышает производительность, экономя время, затрачиваемое на манипуляции со строками.

Максимальное использование табличных параметров

Обновление сервера до SQL Server 2008 и более новых версий предоставляет возможность использования табличных параметров, которые значительно улучшают производительность при передаче списков List<>. Причины, по которым стоит применять это обновление:

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

Работа с большими объемами данных

Табличные параметры идеально подходят для передачи больших объемов данных:

• Минимизация обращений к серверу: Отправляйте масштабные данные за одну операцию.
• Защита выполнения операций на сервере: SQL Server обеспечивает атомарность транзакций.
• Уменьшение конкуренции за ресурсы: TVP сокращают конфликты блокировки, позволяя другим операциям осуществляться параллельно.

Если нет TVP? Это не проблема!

Если TVP недоступен, рекомендуются следующие альтернативы:

• JSON/XML: Используйте эти форматы для работы со старыми версиями SQL Server или для нереляционных данных.
• Массовая вставка: SqlBulkCopy подходит для массовых операций, однако он менее гибок по сравнению с TVP.

Визуализация

Представьте:

🧺🍏🍎 ➡️ 🚚 ➡️ 🏬

🧺 – это ваш List<>, 🚚 – TVP, а 🏬 – хранимая процедура SQL.

Перенос 🧺 в 🏬 с помощью 🚚 за одну операцию – именно так работают TVP!

Продвинутое использование TVP

Выразительность польз. типов

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

Подробнее об этом расскажет наш спикер на видео

Оптимизация все, оптимизация!

Табличные параметры быстры, но можно ускорить чтение запросов, добавив индексы.

Предотвращение ошибок

Включите в хранимые процедуры обработку ошибок и логирование для минимизации вероятности сбоев.

Полезные материалы

1. Табличные параметры – ADO.NET | Microsoft Учебник
2. Параметризация условия SQL IN – Stack Overflow
3. Работа с данными JSON в SQL Server – SQL Server | Microsoft Учебник
4. STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Учебник