[Из песочницы] Как я улучшал перформанс SSRS репортов
Предыстория Будучи «недомиддлом» в одной Харьковской конторе, я внезапно получил в LinkedIn приглашение переехать в Польшу и начать работать на один большой банк. После очень долгих раздумий я таки согласился, мотивируя себя надеждой, что это будет очень полезный опыт. Поработать на большую корпорацию это не только опыт программирования, но и два, ато и три года полезных записей для резюме, а так же +100 к ношению костюма.(Как бы не так, но об этом в другой раз). Оказалось, что уровень говнокода в Польше намного привосходит оный в Украине, а средний уровень Senior девелопера, по крайней мере из тех кого я тут встречал, не идет ни в какое сравнение с украинскими коллегами.Итак придя на новую работу, я по прошествии трех месяцев организационной рутины и поиска проекта для меня, получил себе тулзу на себя одного, которая генерирует SSRS репорты. Сказать, что здесь клали с высокой колокольни на любые принципы и советы от ведущих мировых инженеров, значит ничего не сказать. Но делать что-то надо — так что пенять уже не на кого.
Дано: .Net Console Application, которое — запускается по скедулеру; — скачивает два txt файла с ftp; — парсит; — мерджит; — запихивает в базу данных; — как результат одна таблица на 100 стринговых (преимущественно) колонок, в которую каждый день добавляется около 3000 записей, у которых даже нету id IDENTITY.SQL Server, в котором хранится база данных, в которой — охрененно здоровая таблица, с кучей колонок и строк; — нормализация умерла; — есть пара view’юх; — есть сопровождающие таблицы, как информация про пользователя, логи и так далее; — очень важная деталь — в большой таблице хранились два типа данных, которые содержали одинаковые колонки, но должны были матчится друг на друга. Например, информация о сделке со стороны покупателя и покупаемого дома, улица цена и все остальное одинаковое, только несколько полей составляли ключ, по которому можно было соотнести дом и покупателя друг к другу. Это была основная задача репорта.SSRS репорты, которые — лезут непосредственно в базу данных запросами, которые выбирают данные из view’юх, по определенным дням. Можно генерировать как дневной репорт, так и на месяц. Например: SELECT a.col1, a.col2 … a.col100 FROM vw_Data_With_User_Info WHERE a.TimeStamp >= @StartDate AND a.TimeStamp <=@EndDate Дополнительные условия: — Нету доступа к UAT;— В DEV базе данных данные за месяц, тестовые, доступа к реальным данным нету;— Нету пермишенов на Profiler.Задача — Репорт на один день загружается примерно 3 минуты, нужно около 40 секунд;— Репорт на месяц загружается около часа, или вообще не загружается — просто исправить по возможности.Решения: Нужно отметить, что про SSRS я до этого даже не читал, поэтому, возможно, некоторые действительно хорошие программисты уже видят, почему оно работает так плохо, но у меня решение заняло около месяца.Попытка 1 Первое, самое логичное решение было уменьшить количество колонок в репорте. Ведь никто не может нормально просматривать данные со 100 колонок на 3000 записей, пускай и группированных по компаниям и юзерам. Поговорили с заказчиком, выяснили наиболее необходимые колонки, и оказалось, что вполне безболезнено можно уменьшить их количество до 16. Так появился на свет репорт Lite.Результат попытки 1. Репорт_Lite на один день — 40 секунд — 1 мин 20 секунд в зависимости настроения сервера и положения звезд. На месяц полчаса — час. На этом заказчики затихли на некоторое время довольствуясь результатом.Попытка 2 В течение следующего месяца я постепенно старался уменьшать время загрузки репорта, наводя порядок в view'хах и запросах. Например, все расчеты, а с ними и логику матчинга клиента и дома друг на друга, в консольное приложение, потому что требований к его скорости работы пока никто не предъявлял. После этого в таблице добавилась пара колонок, среди которых был MatchingKey, и резко упала скорость генерации репортов на UAT энваронменте. Нужно также отметить, что за этот месяц скорость генерации медленно падала с 1минуты (в среднем) до 1.30. Тогда меня спутал факт добавления новой колонки. Я посмотрел, что для записей в таблице до моего релиза MatchingKey is NULL, что естественно заставляло запрос перебирать почти милион записей и не находить мачинга. Плюс я обратил внимание, что сначала выполняется view без ограничения по дате и времени, и только потом из нее выбираются данные по времени. По моей логике это должно было значительно улучшить перформанс генерации репорта и, кстати, на DEV энвайронменте работало более ни менее, вкладывался в 40 секунд, но на UAT никак не повлияло.Результат попытки 2. Практически не виден.
Попытка 3 (успех и всеобщее признание) После того как я разочаровал мое начальство попыткой два, мне поступило конкретное указание. Бросай все, и занимайся только перформансом. Первой идеей был ПОЛНЫЙ РЕФАКТОРИНГ ПРИЛОЖЕНИЯ. Я хотел разбить большую таблицу на две как файлы и матчить в третьей по id, полностью перенести любые расчеты в .net апликуху. Убрать как можно больше группинга из репортов и в общем сделать все как велит Макаронный Бог. На это я потратил неделю, но когда дошел до части сохранения данных в базу данных, начал экспериментировать с таблицами и запросами и оказалось, что разницы в скорости выполнения запросов с выбором из view, join двух отдельных таблиц практически нет. По крайней мере не такая, чтобы репорт загружался 1 минуту. На 3000 записей запрос в SQL выполнялся 3–5 секунд. Значит, дело в группинге на клиент cтороне. Убираем все группинги, чистый репорт даже без сортинга — минус пара секунд к генерации. Получается на группинг уходит пара секунд, на SQL кверю 3–5. А куда девается еще почти минута? Вопрос не риторический, и по хорошему такие эксперименты нужно было проводить перед попыткой 1, но когда дошло — тогда дошло. Продолжаем эксперимент. Начинаем играться с запросами из репорта. К тому времени уже зареквестил у админов права на доступ к Profiler. Открыл Profiler, но поскольку особого (никакого) опыта работы с ним не имел, поглядывал не особо внимательно. И тут в какой-то момент мне надоело каждый раз вводить @StartDate и @EndDate, это раздражает, когда каждые полминуты нужно кликать два датапикера. Поэтому в репорте без группинга я зашел в квери генератор и вместо @StartDate и @EndDate написал '2014–06–11'. Хлоп, 3 секунды на генерацию! Как так? Наверное, закэшилось, очистил кэш везде, еще раз. 3 секунды. Не может быть. Теперь с группингом — 5 секунд. Да вы шутите.После получаса чесания репы и оновременного копания в Profiler я, наконец-то, нашел ту тварь, что мучала меня месяц, а этот бедный проект — полгода.
Результат Оказалось, что имея в репорте кверю например: SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate На стороне SQL она будет выполнена в таком виде:
EXEC sp_executesql N’SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate','@StartDate datetime, @EndDate datetime','2014-06-11','2014-06-11' И уже вот такой запрос выполняется около 1.30 секунд. Тогда как когда я подставил в кверю сразу значения, то выполнился не DSQL, а обычный запрос.
В результате я смог переубедить клиета перейти на Stored Procedre’ы и теперь дневной репорт загружается 6 секунд, месячный — 1 минуту.
Выводы 1.Поспешишь людей насмешишь 2.Поспешишь потеряешь месяц 3.Поспешишь начальство разозлишь 4.Лучше потратить время на понимание проблемы, а не предпологать возможные решения, особенно в теме, в которой ты новичек. P.S. Я понимаю, что это скорее статья не про решение проблемы, а про то, как было неправильно ее решать. Но я много гуглил по этому поводу и не находил подобных советов, или подсказок, что репорты могут устроить подобную подляну.
P.S.2 У меня есть пара предположений по поводу того, почему подобная кверя выполняется так долго, но я буду очень благодарен, если найдется кто-то, кто точно знает, почему — и объяснит мне.
