Логарифмически увеличивающееся время выполнения для каждого цикла элемента управления ForEach

Во-первых, немного предыстории, я новичок в SSIS и только что завершил свой второй проект по импорту данных -.

Пакет очень прост и состоит из потока данных, который импортирует разделенный вкладкой -файл значений клиента из ~30 000 записей в переменную набора записей ADO, которая, в свою очередь, используется для включения контейнера цикла ForEach, который выполняет часть передачи SQL. в значениях из каждой строки набора записей.

Импорт первых ~21 000 записей занял 59 часов, после чего произошел сбой! Последние ~9000 заняли еще 8 часов. Да, всего 67 часов!

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

Когда я протестировал подмножество из 3000 записей a ~на своем локальном компьютере, утилита выполнения пакетов сообщила, что каждая вставка занимает примерно 1 секунду. Немного быстрой математики, и предположение заключалось в том, что общий импорт займет около 8 часов. Казалось, что это долго, чего я и ожидал, учитывая все, что я читал о выполнении SSIS и RBAR. Я подумал, что окончательный импорт будет немного быстрее, так как сервер значительно мощнее. Хотя я получаю доступ к серверу удаленно, но я не ожидал, что это будет проблемой, так как я выполнял импорт в прошлом, используя сделанные на заказ консольные приложения c #, которые используют простые соединения ADO и ничего не запускалось. как медленно.

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

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

Однако то, что происходило во время этого импорта и заставило меня почесать голову, заключается в том, что время выполнения пакета SQL, как сообщает вывод утилиты выполнения пакетов, логарифмически увеличивалось во время выполнения.То, что начинается с времени выполнения sub -в одну секунду, заканчивается в ходе импорта более чем 20 секундами, и в конечном итоге пакет импорта просто полностью останавливается.

Я несколько раз искал в Интернете, спасибо Google, а также StackOverflow, и не нашел ничего, что бы описывало эти симптомы.

Надеюсь, у кого-то есть какие-то подсказки.

Спасибо

В ответ на ErikE:(Не смог вместить это в комментарий, поэтому добавил сюда.)

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

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

Продолжительность для каждого элемента остается довольно постоянной на протяжении всего цикла.

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

------------------------------------------
| Item | CPU | Reads | Writes | Duration |
------------------------------------------
| 0001 |   0 |    29 |    0   |     0    |
| 0002 |   0 |    32 |    0   |     0    |
| 0003 |   0 |    27 |    0   |     0    |
|…                                       |
| 0799 |   0 |    32 |    0   |     0    |
| 0800 |  78 |  4073 |   40   |   124    |
| 0801 |  32 |  2122 |    4   |    54    |
| 0802 |  46 |  2128 |    8   |   174    |
| 0803 |  46 |  2128 |    8   |   174    |
| 0804 |  47 |  2131 |   15   |   242    |
|…                                       |
| 1400 |  16 |  2156 |    1   |    54    |
| 1401 |  16 |  2167 |    3   |    72    |
| 1402 |  16 |  2153 |    4   |    84    |
|…                                       |
| 2997 |  31 |  2193 |    2   |    72    |
| 2998 |  31 |  2195 |    2   |    48    |
| 2999 |  31 |  2184 |    2   |    35    |
| 3000 |  31 |  2180 |    2   |    53    |
------------------------------------------

Ночью я также провел в системе полный повторный -запуск импорта с включенным профилировщиком, чтобы увидеть, как обстоят дела. На моей локальной машине удалось выполнить 1 треть импорта за 15,5 часов. Я экспортировал данные трассировки в таблицу SQL, чтобы получить из нее некоторую статистику. Глядя на данные в трассировке, дельта между вставками увеличивается на ~1 секунду на каждую тысячу обработанных записей,поэтому к тому времени, когда будет достигнута запись 10 000, для вставки каждой записи потребуется 10 секунд. Фактический код, выполняемый для каждой записи, приведен ниже. Не утруждайте себя критикой процедуры, SQL был написан разработчиком-самоучкой -, который первоначально был нашим администратором задолго до того, как кто-либо с фактическим образованием разработчика был принят на работу в компанию. Мы прекрасно понимаем, что это нехорошо. Главное, я считаю, что он должен выполняться с постоянной скоростью, а это совершенно очевидно, что это не так.

if not exists
(
    select 1
    from [dbo].[tblSubscriber]
    where strSubscriberEmail = @EmailAddress
    and ProductId = @ProductId
    and strTrialSource = @Source
)
begin
    declare @ThePassword varchar(20)
    select @ThePassword = [dbo].[DefaultPassword]()

    exec [dbo].[MemberLookupTransitionCDS5] 
     @ProductId
   ,@EmailAddress
   ,@ThePassword
   ,NULL --IP Address
   ,NULL --BrowserName
   ,NULL --BrowserVersion
   ,2 --blnUpdate
   ,@FirstName --strFirstName
   ,@Surname --strLastName
   ,@Source --strTrialSource
   ,@Comments --strTrialComments
   ,@Phone --strSubscriberPhone
   ,@TrialType --intTrialType
   ,NULL --Redundant MonitorGroupID
   ,NULL --strTrialFirstPage
   ,NULL --strTrialRefererUrl
   ,30 --intTrialSubscriptionDaysLength
   ,0 --SourceCategoryId
end
GO

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

----------------------
|  Row  | Delta (ms) |
----------------------
|   500 |  510       |
|  1000 |  976       |
|  1500 | 1436       |
|  2000 | 1916       |
|  2500 | 2336       |
|  3000 | 2816       |
|  3500 | 3263       |
|  4000 | 3726       |
|  4500 | 4163       |
|  5000 | 4633       |
|  5500 | 5223       |
|  6000 | 5563       |
|  6500 | 6053       |
|  7000 | 6510       |
|  7500 | 6926       |
|  8000 | 7393       |
|  8500 | 7846       |
|  9000 | 8503       |
|  9500 | 8820       |
| 10000 | 9296       |
| 10500 | 9750       |
----------------------