Несколько инструкций INSERT против одного INSERT с несколькими значениями VALUES

Я выполняю сравнение производительности между 1000 инструкциями INSERT:

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0)
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1)
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999)

.. против использования одного оператора INSERT с 1000 значениями:

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
VALUES 
('db72b358-e9b5-4101-8d11-7d7ea3a0ae7d', 'First 0', 'Last 0', 0),
('6a4874ab-b6a3-4aa4-8ed4-a167ab21dd3d', 'First 1', 'Last 1', 1),
...
('9d7f2a58-7e57-4ed4-ba54-5e9e335fb56c', 'First 999', 'Last 999', 999)

К моему большому удивлению, результаты противоположны тому, что я думал:

1000 инструкций INSERT: 290 мсек.
1 инструкция INSERT с 1000 знаками: 2800 мс.

Тест выполняется непосредственно в MSSQL Management Studio с использованием SQL Server Profiler, используемого для измерения (и у меня есть аналогичные результаты, которые запускаются из кода С# с использованием SqlClient, что еще более удивительно, учитывая все раунды DAL roundtrips)

Может ли это быть разумным или каким-то образом объяснить? Почему, возможно, более быстрый метод приводит к результату в 10 раз (!) хуже?

Спасибо.

EDIT: Присоединение планов выполнения для обоих:

Ответ 1

Дополнение: SQL Server 2012 показывает некоторую улучшенную производительность в этой области, но, похоже, не рассматривает конкретные проблемы, отмеченные ниже. Эта должен по-видимому, исправлен в следующей крупной версии послеSQL Server 2012!

В вашем плане показаны отдельные вставки с использованием параметризованных процедур (возможно, с автоматическим параметрированием), поэтому время их разбора/компиляции должно быть минимальным.

Мне показалось, что я посмотрю на это немного больше, поэтому настроил цикл (script) и попытался настроить число из VALUES и записывать время компиляции.

Затем я разделил время компиляции на количество строк, чтобы получить среднее время компиляции для каждого предложения. Результаты ниже

До тех пор, пока не будет представлено 250 предложений VALUES, время компиляции/количество предложений имеет небольшой восходящий тренд, но ничего слишком драматичного.

Но затем происходит внезапное изменение.

Этот раздел данных показан ниже.

+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
|  245 |            528 |          41 |          2400 | 0.167346939   |
|  246 |            528 |          40 |          2416 | 0.162601626   |
|  247 |            528 |          38 |          2416 | 0.153846154   |
|  248 |            528 |          39 |          2432 | 0.157258065   |
|  249 |            528 |          39 |          2432 | 0.156626506   |
|  250 |            528 |          40 |          2448 | 0.16          |
|  251 |            400 |         273 |          3488 | 1.087649402   |
|  252 |            400 |         274 |          3496 | 1.087301587   |
|  253 |            400 |         282 |          3520 | 1.114624506   |
|  254 |            408 |         279 |          3544 | 1.098425197   |
|  255 |            408 |         290 |          3552 | 1.137254902   |
+------+----------------+-------------+---------------+---------------+

Размер кешированного плана, который рос линейно, внезапно падает, но CompileTime увеличивается в 7 раз, и CompileMemory стреляет вверх. Это точка отсечения между планом, являющимся автоматической параметризованной (с 1000 параметрами), к не параметризованной. После этого он, кажется, становится линейно менее эффективным (с точки зрения количества предложений стоимости, обработанных за данный момент времени).

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

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

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

Я попытался рассмотреть это в отладчике, но общедоступные символы для моей версии SQL Server 2008, похоже, не доступны, поэтому вместо этого мне пришлось посмотреть эквивалентную конструкцию UNION ALL в SQL Server 2005.

Типичная трассировка стека ниже

sqlservr.exe!FastDBCSToUnicode()  + 0xac bytes  
sqlservr.exe!nls_sqlhilo()  + 0x35 bytes    
sqlservr.exe!CXVariant::CmpCompareStr()  + 0x2b bytes   
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare()  + 0x18 bytes  
sqlservr.exe!CXVariant::CmpCompare()  + 0x11f67d bytes  
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion()  + 0xe2 bytes   
sqlservr.exe!CConstraintProp::PcnstrUnion()  + 0x35e bytes  
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive()  + 0x11a bytes    
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler()  + 0x18f bytes    
sqlservr.exe!CLogOpArg::DeriveGroupProperties()  + 0xa9 bytes   
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties()  + 0x40 bytes    
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x18a bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!CQuery::PqoBuild()  + 0x3cb bytes  
sqlservr.exe!CStmtQuery::InitQuery()  + 0x167 bytes 
sqlservr.exe!CStmtDML::InitNormal()  + 0xf0 bytes   
sqlservr.exe!CStmtDML::Init()  + 0x1b bytes 
sqlservr.exe!CCompPlan::FCompileStep()  + 0x176 bytes   
sqlservr.exe!CSQLSource::FCompile()  + 0x741 bytes  
sqlservr.exe!CSQLSource::FCompWrapper()  + 0x922be bytes    
sqlservr.exe!CSQLSource::Transform()  + 0x120431 bytes  
sqlservr.exe!CSQLSource::Compile()  + 0x2ff bytes

Таким образом, чтобы скрыть имена в трассировке стека, он тратит много времени на сравнение строк.

Эта статья в KB указывает, что DeriveNormalizedGroupProperties связана с тем, что раньше называлось нормализация этап обработки запросов

Этот этап теперь называется связыванием или алгебраизацией, и он берет дерево синтаксиса выражения, выводимое из предыдущей стадии синтаксического анализа, и выводит дерево алгебраических выражений (дерево обработчиков запросов), чтобы перейти к оптимизации (в этом случае оптимизация тривиального плана) [ref].

Я попробовал еще один эксперимент (script), который должен был повторно запустить исходный тест, но рассмотрел три разных случая.

Имя и фамилия Строки длиной 10 символов без дубликатов.
Имя и фамилия Строки длиной 50 символов без дубликатов.
Имя и фамилия Строки длиной 10 символов со всеми дубликатами.

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

Edit

Одно место, где используется эта информация, показано здесь @Lieven

SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID)

Потому что во время компиляции он может определить, что столбец Name не имеет дубликатов, он пропускает упорядочение по вторичному выражению 1/ (ID - ID) во время выполнения (тип в плане имеет только столбец ORDER BY) и не делит на ошибка нуля. Если в таблицу добавлены дубликаты, то оператор сортировки показывает два порядка по столбцам и ожидаемую ошибку.

Ответ 2

Не удивительно: план выполнения для крошечной вставки вычисляется один раз, а затем повторно используется 1000 раз. Анализ и подготовка плана выполняется быстро, поскольку он имеет только четыре значения. С другой стороны, план 1000 рядов должен иметь дело с 4000 значениями (или 4000 параметров, если вы параметризировали свои тесты С#). Это может легко съесть экономию времени, которую вы получите, исключив 999 обращений к SQL Server, особенно если ваша сеть не слишком медленная.

Ответ 3

Вероятно, проблема связана с временем, которое требуется для компиляции запроса.

Если вы хотите ускорить вставку, то вам действительно нужно сделать перенос в транзакции:

BEGIN TRAN;
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0);
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1);
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999);
COMMIT TRAN;

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

Ответ 4

Я столкнулся с аналогичной ситуацией, пытаясь преобразовать таблицу с несколькими 100k строками с помощью С++-программы (MFC/ODBC).

Поскольку эта операция заняла очень много времени, я решил объединить несколько вложений в одну (до 1000 из-за Ограничения MSSQL). Мое предположение, что множество отдельных инструкций вставки создавали бы накладные расходы, подобные описанному здесь.

Однако получается, что преобразование прошло довольно немного дольше:

        Method 1       Method 2     Method 3 
        Single Insert  Multi Insert Joined Inserts
Rows    1000           1000         1000
Insert  390 ms         765 ms       270 ms
per Row 0.390 ms       0.765 ms     0.27 ms

Таким образом, 1000 одиночных вызовов в CDatabase:: ExecuteSql, каждый с одним оператором INSERT (метод 1), примерно в два раза быстрее, чем один вызов в CDatabase:: ExecuteSql с многострочным оператором INSERT с 1000 кортежами значений (метод 2).

Обновление. Итак, следующая вещь, которую я пробовал, заключалась в том, чтобы объединить 1000 отдельных инструкций INSERT в одну строку и выполнить сервер (метод 3). Оказывается, это даже немного быстрее, чем метод 1.

Изменить: Я использую Microsoft SQL Server Express Edition (64-разрядная версия) v10.0.2531.0