Некоторые наши пользователи стали сообшать что репорты иногда возврашают значение превышаюшее 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 девелопера требует избегать использование курсора, но как показал данный пример если есть некрасивое альтернативное решение и оно работает лучше, то надо его использовать…
И особенно важно делать не только юнит тесты но нагрузочные тесты на максимально возможных обьемах.
Автор: BalinTomsk
