Предыстория
Будучи «недомиддлом» в одной Харьковской конторе, я внезапно получил в LinkedIn приглашение переехать в Польшу и начать работать на один большой банк. После очень долгих раздумий я таки согласился, мотивируя себя надеждой, что это будет очень полезный опыт. Поработать на большую корпорацию это не только опыт программирования но и два, ато и три года полезных записей для резюме, а так же +100 к ношению костюма.(Как бы не так, но об этом в другой раз). Оказалось, что уровень говнокода в Польше намного привосходит оный в Украине, а средний уровень Senior девелопера, по крайней мере из тех кого я тут встречал, не идет ни в какое сравнение с украинскими коллегами.
Итак придя на новую работу, я по прошествии трех месяцев организационной рутины и поиска проекта для меня, получил себе тулзу на себя одного, которая генерирует SSRS репорты. Сказать, что здесь клали с высокой колокольни на любые принципы и советы от ведущих мировых инженеров, значит ничего не сказать. Но делать что-то надо — так что пенять уже не на кого.
Дано:
.Net Console Application, которое
— запускается по стандартному планировщику Windows;
— скачивает два 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 У меня есть пара предположений по поводу того, почему подобный запрос выполняется так долго, но я буду очень благодарен, если найдется кто-то, кто точно знает, почему — и объяснит мне.
UPD1: Я учел нарекания к этой статье и постарался исправить все слова, который вызвали критику.
UPD2: Сегодня в новом отчете, уже переделав его на хранимую процедуру все равно столкнулся с проблемой.
exec rOtex_Mis_DailyHighLevelReport @StartDate='2014-06-01 00:00:00',@EndDate='2014-06-30 00:00:00'
во время генерации отчета выполнялось 6 минут, а руками 1 секунду. Поэтому залез в гугл с конкретным вопросом, «SSRS stored procedure slow». И получил вразумительный ответ. Горе мне гугл-рукожопу, он лежал на поверхности ПРАВИЛЬНОГО поиска.
stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs
Дело в том, что это известная проблема SQL Servera, которая относится к Parameter Sniffing, как мне справедливо посоветовали товарищи BelAnt и microuser — и решение довольно говнокодное и, как часто бывает с говнокодом, простое. Приведу пример из моей хранимой процедуры:
@StartDate Date,
@EndDate Date
AS
BEGIN
declare @Start datetime
declare @End datetime
set @Start = @StartDate
set @End = @EndDate
И это работает:
Вывод 2.0
-Гугли нормально, епта
UPD3: После нескольких тестов, оказалось что хранимые процедуры тоже были выдуманы от «большого ума». Оказалось, что достаточно добавить в конец запроса OPTIONS(RECOMPILE), как и советовал товарищ BelAnt. Тут выводы писать не буду, коли воно без мозоку то його выводы до сраки:(