Работа над ошибками аппаратуры на стороне SQL сервера и пользе нагрузочных тестов

Некоторые наши пользователи стали сообшать что репорты иногда возврашают значение превышаюшее 100% для показа роста данных.

При этом выяснилось что сервис, доставляюший данные от аппаратуры иногда делает пропуски в значениях.
Там где сервер читал аппаратуру напрямую, вместо значений вставлял NULL, а там где читал с использованием SNMP, там вставлял 0.

То есть ряд значений счетчика был как: 4, 10, 20, NULL, NULL, 31, 0, 0, 0, 50 а теперь надо 4, 10, 20, 20, 20, 31, 31, 31, 31, 50

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

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

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

Первое решение был очевидным и простым.

для MSSQL 2008:

Update curr set curr.dev_counter_color=coalesce(curr.dev_counter_color, prev.dev_counter_color) from device_counter  curr  left join device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color is null    and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub where sub.dev_counter_date < curr.dev_counter_date and dev_counter_color is not null); 

для Oracle:

merge into DEVICE_COUNTER t using ( select ROWID as rid, last_value(dev_counter_duplex ignore nulls )    over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value from DEVICE_COUNTER t ) v   on (t.rowid = v.rid) when matched then update set dev_counter_duplex = new_cnt_value 

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

Одновремено пришло письмо из техсапорта решивших предложить починить базу со всеми 57 счетчиками.

Казалось бы — чинишь запросом колонку за колонкой да и дело с концом.

Когда мы все таки стянули базу у клиента, оказалось запрос работает на одной колонке почти две минуты на средненьком виртуальном сервере, а слабенький лаптоп клиента, жаловавшегося на скорость апдейта, по нашим подсчетам делал все все 57 sql запросов за ~52 часа!..

По обыкновению мы рекомендуем нашим клиентам работать с техникой, параметры которой указаны в technical requirements, но неужели все так плохо в реляционных языках?

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

Если бы пропуски данных были во всех колонках — то простой проход с апдейтом всех строк занимает примерно тоже время что и первоначальный вариант — 38 минут против 92 при реляционном подходе…

И только тот факт, что пропуски редки — сократило апгрейд до 2! минут — это для 22,000 строк из 3 миллионнов.

BEGIN DECLARE @updated int = 0; DECLARE @cur_dev_id int = NULL, @cur_id int = NULL DECLARE @cur_counter_total_color int = NULL, @cur_counter_total_mono int = NULL;  DECLARE @next_dev_id int = NULL, @next_id int = NULL DECLARE @next_counter_total_color int = NULL, @next_counter_total_mono int = NULL;  DECLARE UPDCURSOR CURSOR for select dev_id, dev_counter_id,  dev_counter_total_color, dev_counter_total_mono from device_counter d  order by dev_id, dev_counter_date      OPEN UPDCURSOR FETCH NEXT FROM UPDCURSOR into @cur_dev_id, @cur_id, @cur_counter_total_color;  WHILE @@FETCH_STATUS = 0 BEGIN     FETCH NEXT FROM UPDCURSOR INTO @next_dev_id, @next_id 		, @next_counter_total_color, @next_counter_total_mono  IF @@FETCH_STATUS = 0 AND @cur_dev_id = @next_dev_id AND ( 	((@next_counter_total_color IS NULL AND @cur_counter_total_color IS NOT NULL) OR (@next_counter_total_color = 0 AND (@cur_counter_total_color > 0 OR @cur_counter_total_color Is NULL ))) OR 	((@next_counter_total_mono IS NULL AND @cur_counter_total_mono IS NOT NULL) OR (@next_counter_total_mono = 0 AND (@cur_counter_total_mono > 0 OR @cur_counter_total_mono Is NULL ))) ) BEGIN     SELECT 	@cur_counter_total_color = (CASE WHEN @next_counter_total_color IS NULL OR @next_counter_total_color = 0 THEN @cur_counter_total_color ELSE @next_counter_total_color END), 	@cur_counter_total_mono = (CASE WHEN @next_counter_total_mono IS NULL OR @next_counter_total_mono = 0 THEN @cur_counter_total_mono ELSE @next_counter_total_mono END);      SET @updated = @updated + 1     UPDATE device_counter SET  		dev_counter_total_color = @cur_counter_total_color, 		dev_counter_total_mono = @cur_counter_total_mono 	WHERE CURRENT OF UPDCURSOR  END ELSE 	BEGIN 		SELECT 			@cur_counter_total_color = @next_counter_total_color, 			@cur_counter_total_mono = @next_counter_total_mono; 	END 	SET @cur_dev_id = @next_dev_id END CLOSE UPDCURSOR DEALLOCATE UPDCURSOR END 

Ничего особенно в этой статье не изобрел, просто практика настояшего SQL девелопера требует избегать использование курсора, но как показал данный пример если есть некрасивое альтернативное решение и оно работает лучше, то надо его использовать…

И особенно важно делать не только юнит тесты но нагрузочные тесты на максимально возможных обьемах.

ссылка на оригинал статьи https://habrahabr.ru/post/276835/