Примеры "тяжелых" запросов в системе 1С 7.7, неэффективное использование хинтов оптимизации запросов в 1С |
В данной статье рассматриваются следующие утверждения:
Эксперимент В данном примере берется запрос 1с, отображающий справочник клиентов с сортировкой по реквизиту адрес. Row_ID (int) – Уникальный идентификатор строки. Обращение к SQL серверу формирующееся при открытии справочника, с заданной сортировкой по реквизиту Адрес, является динамическим курсором. Содержание курсора следующее: Select * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID. Для того, что бы было проще оперировать данной конструкцией из QA мы ее заменим на: Select top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID Обратите внимание, что в конструкции используется явное указание индекса NOLOCK INDEX=VI4135. Это указывает серверу, что стоит осуществлять сканирование и соответственно выбирать четкий план запроса по индексу VI4135. В этом содержится серьезная ошибка с точки зрения производительности и масштабируемости системы. Индекс VI4135 составной и он в себя включает три поля: SP4135, DESCR, SP4135. В запросе 1С поле DESCR исключается, в результате чего этот запрос становится неэффективным во всех отношениях. Этот пример является достаточно показательным во всех отношениях. На этом примере можно рассматривать и работу буферизации MSSQL, и проблемы нехватки оперативных ресурсов (оперативной памяти), и проблемы нагрузки на дисковую подсистему и т.д. и т.п. В данной статье я рассмотрю кратко все области понемногу. Рассмотрим как в QA будет выполняться три запроса: Запрос №1 Аналог запроса, который выполняет 1С при открытии справочника с сортировкой по реквизиту Адрес:Select top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_IDЗапрос №2 - видоизмененный запрос 1 без указания индекса Select top 50 * from SC46 order by SP4135, ROW_IDЗапрос №3 - видоизмененный запрос 1 с добавленным полем сортировки DESCR (Наименование) Select top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, Descr, ROW_ID Запрос №1 Статистика по операциям чтения: Table 'SC46'. Scan count 1, logical reads 529256, physical reads 7096, read-ahead reads 13103. Основная статистика по плану запросов: Интересно будет также понаблюдать за нагрузкой на дисковые массивы и на эффективность используемой свободной оперативной памяти на сервере. Системные счетчики:В момент запуска этого запроса показания счетчика Page life expectancy резко уменьшилось с 1800 до 200. Значения счетчика Avg Disk Queue length наоборот увеличилось с 0,6 до среднего 23 за промежуток времени, который выполняется запрос. Запрос №2 Статистика по операциям чтения:В момент запуска этого запроса показания счетчика Page life expectancy уменьшилось с 1800 до 500. Значения счетчика Avg Disk Queue length наоборот увеличилось с 0,4 до среднего 17 за промежуток времени, который выполняется запрос. Запрос №3Графический план запроса: Статистика по операциям чтения:Время выполнения этого запроса было столь ничтожно, что показания счетчиков остались неизменны в рамках допустимой погрешности. Выводы Анализируя план запроса №1 видно, что основные действия по обслуживанию запроса идут на операцию Bookmark Lookup. Это явно лишняя и неоптимальная операция. Сервер выбирает ее лишь только по тому, что клиентское приложение 1С ему явно указывает в хинте запроса идти по индексу VI4135. В результате выполняется лишняя операция, которая существенно увеличивает как нагрузку на сервер, так и увеличивает время исполнения запроса. По этому запросу видно: несмотря на то что временные характеристики являются важнейшими параметрами в оценке эффективности SQL запросов, тем не менее, они не являются достаточными. Подобный запрос является крайне несбалансированным с точки зрения распределения общей нагрузки. При выполнении он может отнять очень много ресурсов и этим сильно понизить общую масштабируемость системы. Во время его выполнения SQL серверу может не хватать оперативной памяти на буферизацию данных, следствием чего станет «торможение» операций, выполняемых другими пользователями. Во втором случае, если убрать хинты подсказок, оптимизатор SQL сервер сам выберет более оптимальный план запроса. На этом примере показано, что пользоваться хинтами и в явном виде выбирать план запроса, при недостатке знаний, лучше не стоит. Это может в некоторых случаях очень сильно подорвать масштабируемость всей системы. Проблемы будут не только у пользователя, который будет долго ожидать выполнения отчета, но и у остальных пользователей системы т.к. подобный запрос перетянет на себя большую часть ресурсов. В данном случае необходимо проводить обязательный мониторинг системы на предмет возникновения подобных отчетов. По сравнению с вариантом 1, в результатах выполнения запроса №2 отсутствует лишняя операция Bookmark Lookup и начальное сканирование идет по первичному ключу. Следствием этого является меньшее время выполнения и, что более важное, он гораздо меньше отнимает свободных ресурсов и у него меньше нагрузка на дисковые массивы. Тем не менее, если мы посмотрим результаты запроса №3, то мы увидим, что он отнимает на порядки меньше ресурсов и на порядки меньше время выполнения. Разница в результате заключается только в том, что если вдруг в БД будет 2-а клиента с одинаковым адресом (что само по себе маловероятно) то в этом случае они будут отсортированы не по Row_ID а по Наименованию. Понятно, что это не принципиально с точки зрения результата. Почему же в этом случае возникает такая большая разница? Разгадка кроется в наличии и составе индекса VI4135. Он составной и состоит из следующих полей (по порядку слева направо): SP4135, Descr, Row_ID. В случае если мы указываем Order by SP4135, Descr, Row_ID или указываем INDEX=VI4135 – в этом случае мы фактически выбираем подряд записи из индекса. Все дополнительные ресурсоемкие операции, типа сортировки длинных текстовых полей, отсутствуют, так как сам по себе индекс уже упорядочен. P.S. Данная конструкция для 1С 7.7. довольно типовая, и при больших объемах справочников и наличии больших строковых реквизитов с сортировкой может приводить к катастрофическим последствиям. Тем не менее эта ситуация решается. Компания "СофтПоинт" предлагает комплексные решения по внедрению системы мониторинга производительности, которая быстро поможет идентифицировать все «узкие» места производительности в вашей ИТ системе. Эта система позволят идентифицировать проблемы для произвольных ИТ систем, но для системы 1С 7.7. существует отдельная экспертная база данных настроенная с учетом ее специфики. Все подобные запросы легко «ловятся», и предлагаются варианты разрешения подобных проблем. По описанной выше проблеме существует отдельная внешняя компонента, которая подменяет вызовы 1С на более эффективные и таким образом разрешает проблемы производительности.
Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").
|