MS SQL оптимизация запросов

Я тут пообещал поделиться опытом оптимизации SQL запросов. Мне достаточно много приходилось работать над оптимизацией и на работе не раз просили рассказать, как я оптимизирую запросы. Тут нет какого-то секрета и, хотя я пытался поделиться, это все же такой процесс, в котором мастерство приходит с опытом.

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

Это вводная часть, где я расскажу в статье и покажу в видео (его можно найти в конце этой статьи) базовые вещи о индексах и статистике.

О индексах и статистике можно писать очень много и возможно будет еще статья и видео. А это такой вводный курс, чтобы показать базу.

Когда я узнаю о том, что на сервере есть медленно выполняющийся запрос, первое на что я смотрю – статистику io. Это такое слабое звено, позволяет быстро определить легкие проблемные места SQL запросов.

В SQL Server есть несколько вариантов получения данных о том, как выполняется запрос – но именно статистика получается очень легко и тут же показывает проблемные места в читабельном виде.

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

Давайте займемся тем, чем любят заниматься программисты по ночам – посмотрим на код. Итак, для вводного курса рассмотрим простой пример с запросом:

select * from Member where WSEmail = ‘noreply@flenov.info’

У меня в тестовой базе данных почти 200 тысяч записей и поиск по ней происходит мгновенно. Если посмотреть на время выполнения, то будет ноль секунд, что очень хорошо. Но давайте включим отображение статистики, и посмотрим на нее. Я всегда включаю статистику io и время time:

set statistics io on set statistics time on

Теперь после выполнения SQL запроса в SQL Management Studio внизу окна будет появляться не только результат, но и на закладке Messages будет показана статистика выполнения:

В моем случае статистика выглядела так:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) Table ‘Member’. Scan count 1, logical reads 174261, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 187 ms, elapsed time = 187 ms.

Первые четыре строчки – это время компиляции запроса и для такого простого особо проблем не должно возникнуть, а я уже выполнял запрос, поэтому и 0 секунд.

После этого идет статистика выполнения и тут нужно смотреть на количество сканирований и количество чтений (logical reads, physical reads и др). У нас сейчас запрос простой, который решается одним сканированием. Судя по количеству чтений, сканировалась абсолютно вся таблица.

Казалось бы, да и черт с ним, что сканируется вся таблица, если запрос выполняется так быстро – ноль секунд. И если запрос выполняется только один раз, то можно и забыть и забить. Но если сразу тысяча человек будет выполнять этот запрос и искать по разной фамилии? Даже при такой статистике нагрузка на базу данных будет серьезная, а если в базе будет миллион записей, то сервер может серьезно затормозить.

Для оптимизации нужно создать индекс, который ускоряет поиск:

create index IX_Member_WSEmail on Member ( WSEmail )

Более подробно о создании индексов можно почитать здесь: Индексы в MS SQL Server и еще немного интересной информации о индексах здесь: опции индексов.

Здесь я только скажу, что при создании индексов на таблицу, где много данных и с большим количеством выполняемых запросов может стать проблемой. Создание индекса по умолчанию потребует блокировки, что может стать препятствием. Индекс может не создаваться, потому что данные заблокированы или сайт может лечь, если индекс будет долго создаваться. Чтобы этого не произошло, нужно добавлять опцию: (online = on)

create index IX_Member_WSEmail on Member ( WSEmail ) with (online = on)

Снова выполняем запрос и смотрим на статистику:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 2 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) Table ‘Member’. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

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

Попробуем взглянуть на план выполнения – графическое представление того, как сервер реально выполнял запрос. Для этого включаем опцию отображения плана – в меню Query выбираем Include Actual Execution Plan (или нажимаем Ctrl+M). Если теперь выполнить запрос, то появится еще одна вкладка – Execution Plan:

Здесь у нас две ветки и читать их нужно справа на лево. Самый правый блок – это то, с чего началось выполнение – Index Seak по индексу IX_Member_WSEmail. Наш простой запрос ищет данные по колонке WSEmail и эта колонка есть в индексе IX_Member_WSEmail, поэтому имеет смысл использовать его. И как мы уже увидели после создания индекса, результат как говориться на io. В индексе находятся индексируемые колонки и первичный ключ. Это все, что узнает сервер, когда находит строку по индексу. Но наш запрос выводит совершенно все колонки и чтобы найти оставшиеся данные, серверу приходится по первичному ключу находить их. Этот процесс быстрый – Key Lookup, потому что это первичный ключ, но он все же отнимает немного времени. И скоро мы увидим сколько. Получается, что серверу необходимо выполнить как бы две операции – поиск по индексу основного ключа, а потом по этому ключу найти данные колонок. А если выводить на экран только колонки WSEmail и первичный ключ MemberID? Эти данные уже есть индексе и второй Key Lookup не понадобиться. Посмотрим, как это будет выглядеть в статистике:

select WSEmail, MemberID from Member where WSEmail = ‘noreply@flenov.info’

Статистика падает с 7 чтений до 3:

(1 row(s) affected) Table ‘Member’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

А план выполнения начинает выглядеть идеально просто:

Допустим, нам необходимо вывести на экран имя человека, в моем случае это колонка WSFirstName:

select WSEmail, MemberID, WSFirstName from Member where WSEmail = ‘noreply@flenov.info’

Теперь запросу нужно будет снова делать два поиска – чтобы найти первичный ключи, а потом по нему найти реальную строку, чтобы выцепить имя. И это в принципе не страшно, потому что поиск по первичному ключу занял всего 4 операции чтения, но что, если выводиться 1000 строк? Тогда уже будет 4000 операций. А если запрос у нас не такой простой и в нем потом еще есть left join на какую-то другую таблицу с именами, где, по имени храниться судьба человека (такой гороскоп по имени).

Можно создать индекс, который будет индексировать по email и по имени:

create index IX_Member_WSEmail on Member ( WSEmail, WSFirstName )

И хотя запрос фильтрует данные только по e-mail, этот индекс все же будет работать и позволит нам быстро найти данные, и в индексе будет уже имя и поэтому второй поиск уже не нужен будет. Круто, но не совсем эффективно. Имя меняется не часто и если мы по нему реально не ищем, то по нему индексировать смысла нет, но есть возможность сказать серверу, чтобы он хранил вместе с индексом еще и колонку WSFirstName, для этого есть такая фишка, как include:

create index IX_Member_WSEmail on Member ( WSEmail ) include(WSFirstName)

Теперь данные индексируются только по колонке WSEmail, что нам и нужно, но вместе с индексом храниться не только первичный ключ, но и имя.

Конечно же, теперь при обновлении данных в колонке WSFirstName придется обновлять и индекс, но так как мы не индексируем по этой колонке, то к перестроению данных это не приведет. Такая операция будет очень быстрой.

Еще один пример, который может выиграть от такого индекса:

select * from Member where WSEmail like ‘noreply%’ and WSFirstName = ‘Михаил’

Мы выводим все колонки и от второго поиска по первичному ключу всех данных не убежать. Все колонки включать в индекс смысла нет, потому что это превратить его практически в первичный, просто не кластерный. Но.. Когда сервер отфильтрует данные по WSEmail по первому индексу и найдет допустим 1000 строк, он может тут же сократить эти данные и проверить имя и результат сократиться до (допустим) 100 строк. То есть Key Lookup может выполняться только 100 раз. Если колонка WSFirstName не включена в индекс, то эту операцию придется уже делать 1000 раз.

Чтобы индексы работали наиболее эффективно, тип данных значения и колонки должно совпадать. Как видите, в моем случае я просто передаю строку в одинарной кавычки, как varchar, потому что колонка имеет тип именно varchar. Если попробовать передать nvarchar:

select * from Member where WSEmail = N’noreply@flenov.info’

Теперь строка e-mail адреса передается в качестве nvarchar и это серьезно ударит по производительности. Изменился только тип строки, которую мы сравниваем, а посмотрите как обрушилась статистика:

(1 row(s) affected) Table ‘Member’. Scan count 1, logical reads 684, physical reads 0, read-ahead reads 408, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Или вот такой пример, так обычно делают многие движки, когда выполняют запросы и примерно так будет выполнять запросы популярный Dapper или даже .NET фреймворк:

declare @email nvarchar(200) = N’noreply@flenov.info’ select WSEmail, MemberID from Member where WSEmail = @email

Когда вы в .NET выполняете запрос, то создается переменная и она передается запросу.

Когда я работал над сайтом регистрации продуктов для Sony, то допустил такую ошибку. На главной странице сайте есть автокомплитер, где пользователь может ввести код товара. Пока пользователь вводит, на заднем плане происходит поиск и когда я запустил этот сайт, то он нереально затормозил, хотя этот сайт не такой уж и популярный и по посещаемости самый слабый из всех, что я делал для Sony. Начали исследовать, а оказалось, что для этого проекта я перешел на Dapper, который по умолчанию все переменные делал nvarchar, а в базе данных у нас все было просто varchar (сайт только для США). В результате, даже небольшой нагрузки на сайт хватало для серьезного падения производительности. Пришлось хакать Dapper, чтобы он не создавал переменные Unicode, а делал их простыми varchar

Если тип колонки и искомого значения совпадают, то будет использоваться Index Seek. Если не совпадают, то Index Scan – сканирование по индексу. Что используется для поиска можно увидеть в Execution Plan

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

Если ваш фреймворк косячит и передает неверно параметры, а у вас нет доступа к исходникам, чтобы исправить, то можно использовать вот такой финт ушами:

declare @email varchar(200) = ‘noreply@flenov.info’ select WSEmail, MemberID from Member where WSEmail = cast(@email as varchar)

Здесь я с помощью cast привожу email переменную к правильному типу, чтобы он совпадал с типом данных колонки.

На этом наверно остановимся, для первого вводного курса этого должно быть достаточно. Если эта статья оказались интересна, рекомендую все же посмотреть еще и видео. Лайкни его, мне приятно видеть, если моя работа полезна и интересна другим. Подпишись на канал. Если будет востребованность, будет смысл продолжать эту тему.

Если будет продолжение, то со статистикой мы еще столкнемся не раз.

12 Февраля 2019 | Базы данных

Поделитесь с друзьями

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание

В языке запросов в операциях выбора и в условиях отборов используются логические выражения:

|
|
В ( | ) |
В ( ) |
МЕЖДУ И |
ЕСТЬ NULL |
ССЫЛКА |
ПОДОБНО

> | = |

]

Логическим выражением может быть:

  • обычное языка запросов, если его результат имеет логический тип;
  • двух выражений языка запросов; выполняются в соответствии с правилами сравнения значений описанными в Правила сравнения значений;
  • оператор проверки совпадения / не совпадения значения выражения с одним из перечисленных или со значениями, содержащимися в результате другого запроса;
  • оператор проверки вхождения значения выражения в диапазон;
  • оператор проверки значения выражения на NULL;
  • оператор проверки ссылочного значения выражения на ссылку на определенную таблицу;
  • оператор проверки строкового значения на подобие шаблону.

При сравнении значений используются правила сравнения значений, описанные ниже.

Правила сравнения значений

Поскольку в языке запросов могут сравниваться значения разных типов, определены правила, по которым выполняется сравнение двух значений. Данные правила используются для:

  • сравнения значений в операторах сравнения;
  • определения максимального и минимального значений в агрегатных функциях МИНИМУМ и МАКСИМУМ;
  • упорядочивания записей результата запроса в соответствии с порядком, заданным в предложении УПОРЯДОЧИТЬ ПО.

Если типы значений отличаются друг от друга, то отношения между значениями определяются на основании приоритета типов:

  • тип NULL (самый низший);
  • тип Булево;
  • тип Число;
  • тип Дата;
  • тип Строка;
  • ссылочные типы.

Отношения между различными ссылочными типами определяются на основе внутренних ссылочных номеров таблиц, соответствующих тому или иному типу.

Если типы данных совпадают, то производится сравнение значений по следующим правилам:

  • у типа Булево значение ИСТИНА больше значения ЛОЖЬ;
  • у типа Число обычные правила сравнения для чисел;
  • у типа Дата более ранние даты меньше более поздних;
  • у типа Строка сравнения производится в соответствии с установленными национальными особенностями базы данных без учета концевых пробелов;
  • ссылочные типы сравниваются на основе своих значений (номера записи и т. п.);
  • не допускается сравнение полей неограниченной длины (строки неограниченной длины, ХранилищеЗначения, поле ТипЗначения из таблицы планов видов характеристик).

Важно! Любая операция сравнения двух значений, в которой участвует хотя бы одно значение NULL, дает результат, аналогичный значению ЛОЖЬ.

Оператор проверки совпадения значения

Форма оператора В для проверки совпадения с одним из перечисленных

Оператор В позволяет проверить, совпадает ли значение выражения, указанного справа от него, с одним из значений, описанных слева. Если совпадает хотя бы с одним – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное. Сравнение значений производится по правилам, описанным в Правила сравнения значений.

Выбрать
Справочник.Номенклатура.Наименование

Где
Справочник.Номенклатура.Родитель.Наименование
В («Бытовая техника», «Оргтехника»)

Форма оператора В для проверки принадлежности по иерархии

Для справочников проверка может осуществляться и на принадлежность по иерархии. Результатом оператора В ИЕРАРХИИ будет ИСТИНА, если значение выражения слева является ссылкой на элемент справочника и входит во множество значений справа или иерархически принадлежит какой-нибудь группе, содержащейся в этом множестве:

// В качестве параметра Группа в запрос передается ссылка
// на какую-либо группу справочника Номенклатура.

Выбрать
Справочник.Номенклатура.Наименование

Где
Справочник.Номенклатура.Ссылка В ИЕРАРХИИ (&Группа)

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

Выбрать
Справочник.Номенклатура.Наименование

Где
Справочник.Номенклатура.Ссылка В ИЕРАРХИИ
(
ВЫБРАТЬ
Справочник.Номенклатура.Ссылка
ГДЕ
Справочник.Номенклатура.Наименование = «Одежда»
)

Форма оператора В для проверки совпадения значения с одним из результата запроса

Примером применения данного оператора может послужить следующее:

// Выбрать названия товаров, которые присутствовали в расходных накладных
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка В
(
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ
Документ.РасхНакл.Состав КАК РасхНаклСостав
)

Результат запроса:

Товары

Кран

Вантус

Стол

Стул

Для получения противоположного результата, то есть, если нужно определить, что значение не совпадает ни с одним из результата запроса, запрос выглядит следующим образом:

// Выбрать названия товаров, которые присутствовали в расходных накладных
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка НЕ В
(
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ
Документ.РасхНакл.Состав КАК РасхНаклСостав
)

Товары

Сантехника

Мебель

Заметим, что из запроса операции В возможно обращение к полям таблиц, которые встречались во внешнем запросе до появления операции.

// Выбрать названия товаров, которые присутствовали в расходных накладных
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка В
(
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ
Документ.РасхНакл.Состав КАК РасхНаклСостав
ГДЕ
РасхНаклСостав.Товар = Товары.Ссылка
)

Товары

Кран

Вантус

Стол

Стул

Оператор проверки вхождения значения в диапазон

Оператор МЕЖДУ позволяет проверить, входит ли значение выражения, указанного справа от него, в диапазон, указанный слева (вместе с границами диапазона). Если входит – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное. Сравнение значений производится по правилам, описанным в Правила сравнения значений.

Пример:

Выбрать
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЗакупочнаяЦена
Где
Справочник.Номенклатура.ЗакупочнаяЦена МЕЖДУ 100 И 1000

Оператор проверки значения на NULL

Оператор ЕСТЬ NULL позволяет проверить значение выражения слева от него на NULL. Если значение равно NULL – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

Выбрать
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЗакупочнаяЦена
Где
Справочник.Номенклатура.ЗакупочнаяЦена Есть NULL

Оператор проверки ссылочного значения

Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного справа от него, ссылкой на таблицу, указанную слева. Если да – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Разыменование таблиц описано в параграфе «Разыменование полей».

Выбрать
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЕдиницаИзмерения
Где
Справочник.Номенклатура.ЕдиницаИзмерения Ссылка Справочник.ЕдиницыИзмерения

Оператор проверки строки на подобие шаблону

Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип строка. Если значение выражения удовлетворяет шаблону – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ.

Следующие символы в строке шаблона являются служебными и имеют смысл, отличный от символа строки:

  • % (процент): последовательность, содержащая любое количество произвольных символов
  • _ (подчеркивание): один произвольный символ
  • (в квадратных скобках один или несколько символов): любой одиночный символ из перечисленных внутри квадратных скобок
    В перечислении могут встречаться диапазоны, например a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона.
  • (в квадратных скобках значок отрицания, за которым следует один или несколько символов): любой одиночный символ, кроме тех, которые перечислены следом за значком отрицания

Любой другой символ означает сам себя и не несет никакой дополнительной нагрузки.

Если в качестве самого себя необходимо записать один из перечисленных символов, то ему должен предшествовать . Сам (любой подходящий символ) определяется в этом же операторе после ключевого слова СПЕЦСИМВОЛ.

Причем перед этой последовательностью может располагаться произвольный набор символов.

Процедура КнопкаВыполнитьНажатие(Кнопка) КоличествоОбходов = 100500; Для Инд = 0 По КоличествоОбходов Цикл //такая вот нехитрая эмуляция длительной операции Индикатор = Инд*100/КоличествоОбходов; КонецЦикла; КонецПроцедурыРисунок 2. Обычные формы – пример длительной операции с выводом процента выполнения в индикатор

Как говорится – «проще не придумаешь». Никакого «лишнего» кода, всё максимально прозрачно, понятно и самое главное – работает!

На этом моменте кто-то из читателей ностальгично вздохнёт, так как мы переходим к следующей части статьи – к управляемым формам.

Управляемые формы

С приходом платформы 8.2 и управляемых форм ситуация с индикатором в корне изменилась. Но дело совсем не в версии платформы и не в новых формах, а в появлении новых видов клиентов для «1С:Предприятие» – тонкого и web-клиента. Схематично работу в управляемом режиме можно представить в следующем виде:

Рисунок 3. Схема работы «1С:Предприятие» в управляемом режиме

Если провести сравнение с обычным режимом, то сразу бросаются следующие отличия:

  • Постоянного соединения клиентской части с базой данных нет – их взаимодействие происходит только через сервер 1С (в файловом режиме вместо сервера 1С выступает некоторая эмуляция, но это уже другая история)
  • Отсутствует постоянное соединение клиентской части с серверной
  • Встроенный язык разделён на клиентский и серверный.

Есть ещё один важный, но не отображённый на схеме нюанс – серверный вызов, посредством которого происходит взаимодействие клиентской части и серверной. Он может быть инициирован только клиентом. А это значит, что со стороны сервера отправить данные на сторону клиента можно только в том случае, если с соответствующего клиентского сеанса пришёл запрос.

Дополнительно о серверных вызовах можно прочитать в статье Как работает серверный вызов в 1С

Как всё это повлияло на использование индикатора для отображения информации о ходе выполнения длительного процесса? Давайте подробно в этом разберёмся.

Обычно длительный процесс заключается в сложной обработке данных. Будь то групповое проведение документов, проведение сложных расчётов или выборка данных и заполнение ими отчётной формы.

Получается, что в том или ином виде всегда требуется доступ к базе данных и/или выполнение методов, доступных только на стороне сервера. Для этого на него необходимо передать управление.

Во время выполнения серверного вызова клиентская часть 1С становится неактивной и блокируется для дальнейшей работы до тех пор, пока вызов не завершится и управление не вернётся на сторону клиента. Поэтому, даже если во время выполнения процедуры на сервере производить изменение значения реквизита, отвечающего за данные индикатора, то на клиент вернётся только финальная величина.

Получается, что если взять код из предыдущего примера и просто разбить его на клиентскую и серверную часть, то получим долгое подвисание приложения, после чего индикатор резко установится в значение «100%».

&НаСервере Процедура КомандаВыполнитьНажатиеНаСервере() КоличествоОбходов = 100500; Для Инд = 0 По КоличествоОбходов Цикл //такая вот нехитрая эмуляция длительной операции Индикатор = Инд*100/КоличествоОбходов; КонецЦикла; КонецПроцедуры &НаКлиенте Процедура КомандаВыполнитьНажатие(Команда) КомандаВыполнитьНажатиеНаСервере(); КонецПроцедуры Рисунок 4. Попытка использовать код из обычного режима в управляемом

При таком использовании индикатора от него пользы никакой нет. Поэтому разработчики идут на различные хитрости – выполняют длительные процессы «порционно» с отображением на полосе индикатора значений относительно «порций»; используют динамическое уменьшение скорости движения индикатора в зависимости от времени выполнения процесса; или придумывают другие варианты. Но ни один из них не имеет ничего общего с отображением выполнения конкретного процесса в режиме реального времени.

Именно поэтому при обновлении типовых конфигураций рядом с индикатором мы наблюдаем фразу примерно следующего содержания: «эта операция может занять длительное время – от нескольких минут до нескольких часов». И отчасти поэтому при формировании отчётов крутятся «безликие» круги-индикаторы, которые в виде простой анимации указывают на то, что программа вовсе не зависла.

Кстати, в «1С:Бухгалтерии предприятия, ред. 3.0» разработчики нашли интересное решение (видимо, чтобы бухгалтеры не скучали) – вместо кругов разместили милого котёнка.

Не знаю, как на счет настроения, но понимания о планируемом времени окончания выполнения процесса этот котёнок точно не прибавляет.

Один из вариантов вывода индикатора для отображения хода выполнения длительного процесса заключается в следующем:

  1. Длительная операция запускается в виде фонового задания, в ходе выполнения которого промежуточные результаты помещаются во временное хранилище.
  2. На стороне клиента запускается обработчик ожидания, который с определённой периодичностью опрашивает временное хранилище и выводит данные из него в полосу индикатора.

Программный код будет состоять из двух частей: из кода интерфейсной части (в нашем случае – обработки) и кода общего модуля. Модуль формы обработки будет выглядеть примерно так:

&НаКлиенте Процедура КомандаВыполнитьНажатие(Команда) //стартуем выполнение длительной операции на сервере в фоновом задании КомандаВыполнитьНажатиеНаСервере(); //подключаем обработчик ожидания для мониторинга выполнения процесса ПодключитьОбработчикОжидания(«ОбработчикОжидания»,5,Истина); КонецПроцедуры &НаСервере Процедура КомандаВыполнитьНажатиеНаСервере() АдресХранилища = ПоместитьВоВременноеХранилище( Новый Структура(«Инд,КоличествоОбходов»,0,1),ЭтаФорма.УникальныйИдентификатор); МассивПараметров = Новый Массив; МассивПараметров.Добавить(АдресХранилища); ФЗ = ФоновыеЗадания.Выполнить(«ОбщийМодуль1.ФоновыйПроцесс»,МассивПараметров); КонецПроцедуры &НаКлиенте Процедура ОбработчикОжидания() Экспорт ДанныеОВыполнении = ПолучитьИзВременногоХранилища(АдресХранилища); Если ТипЗнч(ДанныеОВыполнении) = Тип(«Структура») Тогда Индикатор = ДанныеОВыполнении.Инд*100/ДанныеОВыполнении.КоличествоОбходов; Если ДанныеОВыполнении.Инд <> ДанныеОВыполнении.КоличествоОбходов Тогда ПодключитьОбработчикОжидания(«ОбработчикОжидания»,5,Истина); КонецЕсли; КонецЕсли; КонецПроцедуры Рисунок 5. Управляемые формы – пример длительной операции с выводом процента выполнения в индикатор. Модуль формы обработки

А общий модуль будет содержать процедуру для запуска в фоновом режиме:

//Фоновое задание для УправляемыеФормы_Вариант2 Процедура ФоновыйПроцесс(Адрес) Экспорт КоличествоОбходов = 100500; Для Инд = 0 По КоличествоОбходов Цикл //такая вот нехитрая эмуляция длительной операции ПоместитьВоВременноеХранилище( Новый Структура(«Инд,КоличествоОбходов», Инд,КоличествоОбходов), Адрес); КонецЦикла; КонецПроцедуры Рисунок 6. Управляемые формы – пример длительной операции с выводом процента выполнения в индикатор. Общий модуль

Для тех, кто захочет проверить работоспособность данного примера, дополнительно приведём скриншот формы обработки:

Рисунок 7. Управляемые формы – пример длительной операции с выводом процента выполнения в индикатор. Форма обработки

Несмотря на то, что индикатор будет «живым», сказать, что эффект достигнут и пример рабочий, на сто процентов нельзя. Потому что между реальным состоянием выполнения процесса и интерфейсом пользователя есть аж целых две «прослойки» – временное хранилище и обработчик ожидания. При таком подходе мало того, что отображение идёт не в режиме реального времени, так ещё и возрастает возможность возникновения различного вида сбоев из-за использования промежуточных звеньев.

Подводя итог получаем, что для «правильной» работы индикатора в управляемых формах (то есть, как в обычном режиме – с привязкой к выполнению процесса в реальном времени) не хватает возможности инициировать сервером отправку данных на сторону клиентской части. И тут наступает самое время поговорить про сюрприз, который фирма «1С» преподнесла разработчикам в новой платформе «8.3.10».

Платформа «8.3.10»

На самом деле в платформе «1С:Предприятие 8.3.10» касательно индикатора ровным счётом ничего не изменилось. Там есть много других интересных «фишек». Например, система взаимодействий, инкрементальная выгрузка конфигурации в XML, новые режимы основного окна. Но всё это не относится к теме данной статьи. Поэтому, можно было бы уже закончить писать… Если бы не наши «пытливые умы».

Давайте чуть-чуть пристальнее взглянем на систему взаимодействий. Для этого возьмём описание с одного из сайтов фирмы «1С»:

Система взаимодействия – это механизм, позволяющий пользователям одной информационной базы системы «1С:Предприятие» общаться друг с другом в режиме реального времени с помощью текстовых сообщений и видеозвонков… Обмен сообщениями между клиентской частью и сервером взаимодействия осуществляется по протоколу WebSocket.

Теперь посмотрим на определение протокола WebSocket на сайте Википедии:

WebSocket – протокол полнодуплексной связи, предназначенный для обмена сообщениями между клиентом и веб-сервером в режиме реального времени…

Обратите внимание на ключевые слова: двухсторонняя связь между клиентом и сервером в режиме реального времени! То, чего так не хватало для нормального функционирования индикатора в управляемых формах! Давайте для лучшего понимания взглянем на схему работы «1С:Предприятие» и системы взаимодействий:

Рисунок 8. Схема работы «1С:Предприятие» с системой взаимодействий

Остаётся вопрос: можно ли использовать систему взаимодействий для вывода хода выполнения процесса в индикатора? Ответ – можно конечно, но… Давайте сначала рассмотрим возможность информирования пользователя о ходе выполнения длительного процесса при помощи только системы взаимодействия.

Для этого нам потребуется длительный процесс, выполняемый в фоне:

&НаСервере Функция КомандаВыполнитьНажатиеНаСервере() ФоновыеЗадания.Выполнить(«ОбщийМодуль1.ОбработатьФоновоеЗадание»); КонецФункции &НаКлиенте Процедура КомандаВыполнитьНажатие(Команда) //стартуем выполнение длительной операции на сервере в фоновом задании КомандаВыполнитьНажатиеНаСервере(); КонецПроцедуры Рисунок 9. Платформа 8.3.10 – пример длительной операции, выполняющейся в фоновом задании. Модуль формы обработки //Фоновое задание для СистемаВзаимодействий Процедура ОбработатьФоновоеЗадание() Экспорт Для Инд = 0 По 100500 Цикл //такая вот не хитрая эмуляция длительной операции КонецЦикла; КонецПроцедуры Рисунок 10. Платформа 8.3.10 – пример длительной операции, выполняющейся в фоновом задании. Общий модуль

Который мы дополним программным кодом работы с системой взаимодействия, достаточным для информирования пользователя о ходе выполнения процедуры в виде оповещений. Для этого нам будет необходимо немного изменить процедуру обработки фонового задания в общем модуле:

//Фоновое задание для СистемаВзаимодействий Процедура ОбработатьФоновоеЗадание() Экспорт МенеджСВ = Новый МенеджерСистемыВзаимодействия; //создаём обсуждение, в которое будем сообщать о ходе выполнения Обсуждение = МенеджСВ.СоздатьОбсуждение(); Обсуждение.Заголовок = «Служебное»; Обсуждение.Участники.Добавить(МенеджСВ.ИдентификаторТекущегоПользователя()); Обсуждение.Записать(); Для Инд = 0 По 100500 Цикл //такая вот нехитрая эмуляция длительной операции //отправляем сообщение только один раз в 250 обходов цикла Если Инд%250 = 0 Тогда //подготовка и отправка сообщения Сообщ = МенеджСВ.СоздатьСообщение(Обсуждение.Идентификатор); УстановитьПривилегированныйРежим(Истина); Сообщ.Автор = МенеджСВ.ИдентификаторТекущегоПользователя(); Сообщ.Дата = ТекущаяДата(); Сообщ.Текст = «Выполнение: » + Строка(Инд*100/100500) + «%»; Попытка Сообщ.Записать(); Исключение КонецПопытки; УстановитьПривилегированныйРежим(Ложь); КонецЕсли; КонецЦикла; КонецПроцедуры Рисунок 11. Платформа 8.3.10 – пример длительной операции с выводом процента выполнения в виде оповещения. Общий модуль

Если изучить программный код, то будет понятно – в момент старта фонового задания создаётся «Обсуждение», в котором будет выводится информация о ходе процесса. А при выполнении длительного процесса в «Обсуждение» выводятся сообщения с указанием процента выполнения. Каков будет результат и где его искать – видно на следующем рисунке:

Рисунок 12. Платформа 8.3.10 – пример длительной операции с выводом процента выполнения в виде оповещения. Пример работы

Таким образом, у пользователя будет возможность увидеть процент выполнения фонового задания в режиме реального времени. Если он знает, куда смотреть.

С точки зрения эргономики данный вариант проигрывает выводу информации в виде индикатора на самой форме обработки. Поэтому при желании к описанному механизму можно «прикрутить» и индикатор. Но! Пока это возможно сделать только при помощи подключенного обработчика ожидания, который инициирует серверный вызов для анализа полученных сообщений и вывода данных в индикатор.

&НаКлиенте Процедура ОбработчикОжидания() Экспорт Вывод_В_Индикатор() КонецПроцедуры // ВыводПрогрессора() &НаСервере Процедура Вывод_В_Индикатор() МенеджСВ = Новый МенеджерСистемыВзаимодействия; ОтборСВ = Новый ОтборСообщенийСистемыВзаимодействия; ОтборСВ.Количество = 1; ОтборСВ.НаправлениеСортировки = НаправлениеСортировки.Убыв; ОтборОбсужд = Новый ОтборОбсужденийСистемыВзаимодействия; ОтборОбсужд.КонтекстноеОбсуждение = Ложь; ВсеОбсуждения = МенеджСВ.ПолучитьОбсуждения(ОтборОбсужд); Для Каждого ТекОбсуждение Из ВсеОбсуждения Цикл Если ТекОбсуждение.Заголовок = «Служебное» Тогда Прервать; КонецЕсли; КонецЦикла; ОтборСВ.Обсуждение = ТекОбсуждение.Идентификатор; ВсеСообщения = МенеджСВ.ПолучитьСообщения(ОтборСВ); Если ВсеСообщения.Количество() > 0 Тогда Сообщение = ВсеСообщения; Иначе Возврат; КонецЕсли; ЗначениеПроцента = СтрЗаменить(Сообщение.Текст,»Выполнение: «,»»); ЗначениеПроцента = СтрЗаменить(ЗначениеПроцента,»%»,»»); Попытка ЗначениеПроцента = Число(СокрЛП(ЗначениеПроцента)); Исключение Возврат; КонецПопытки; Индикатор = ЗначениеПроцента; КонецПроцедуры Рисунок 13. Платформа 8.3.10 – пример длительной операции с выводом процента выполнения в индикатор. Процедуры обработчика ожидания

Конечно, это очередное использование промежуточного звена, неоправданные серверные вызовы и как следствие – запоздание отображения от режима реального времени.

Связано это с тем, что некоторые методы работы с системой взаимодействия не доступны на клиенте. Но это будет меняться, и разработчики платформы «1С:Предприятие» уже анонсировали, что в версии 8.3.11 на основе системы взаимодействий появится возможность инициирования сервером передачи информации на сторону клиента!

Вместо заключения

В настоящее время эргономика инструмента играет большую роль в дальнейшей его судьбе – будет оно активно использоваться/применяться или его отложат/выкинут.

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

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

Поэтому при разработке конфигураций необходимо обращать внимание на программный код, выполнение которого может занять продолжительное время. И предусматривать механизмы оповещения пользователя о том, что программа выполняет процесс, а не зависла.

Конечно, не всегда представляется возможным в начале длительной операции предвидеть объем обрабатываемых данных или время выполнения операции. В этих случаях использование индикатора не будет давать пользователю достоверной информации о ходе процесса и его использование становится нецелесообразным. Поэтому порой приходится выкручиваться различными способами, чтобы работа в программе вызывала меньше негативных эмоций – использовать анимированные кольца или милых котят.

Но если есть возможность получить привязку выполнения процесса к реальному времени, то это нужно делать и выводить пользователю эту полезную информацию. Согласитесь – ведь приятно иметь возможность прогнозировать «успею ли я сходить налить себе кофе, пока этот отчёт формируется”?

P.S.

Если вас заинтересовала система взаимодействий, и вы хотели бы попробовать с ней поработать, но не знаете, что это такое и с чего начать – читайте следующую статью Система взаимодействий в платформе 8.3.10.

А пока – пишите в комментариях истории из своего опыта по работе с индикаторами!

Об авторе

Автор статьи – Павел Ванин

г. Владимир

E-mail: pahich@mail.ru

Выгрузка ИБ с примерами и PDF-версия статьи для участников группы ВКонтакте

Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.

Если Вы еще не вступили в нее – сделайте это сейчас, и в блоке ниже (на этой странице) появятся ссылки на скачивание материалов.

Выгрузка ИБ с примерами:Ссылка доступна для зарегистрированных пользователей)
Если Вы уже участник группы – нужно просто повторно авторизоваться в ВКонтакте, чтобы скрипт Вас узнал. В случае проблем решение стандартное: очистить кэш браузера или подписаться через другой браузер.

Время выполнения отчетов – один из ключевых показателей успешности внедрения 1С. Учитывая, что все отчеты строятся на основе выполнения запросов к базе данных, длительность работы запросов – ключевой показатель. Далеко не всегда время работы устраивает пользователей со стороны заказчика, поэтому администраторам приходится оптимизировать проблемные участки. Чтобы их найти в коде, можно использовать такой инструмент, как SQL Profiler.

Что такое MS SQL Profiler и где его найти?

В 1С запросы пишут на встроенном языке, который очень похож на SQL. В процессе работы текст запроса конвертируется в стандартный SQL-запрос и выполняется на стороне СУБД. И иногда, чтобы понять, в чем проблема, разработчику 1С нужно смотреть именно на SQL-версию своего запроса. Многие разработчики используют для этого такой инструмент, как MS SQL Profiler.

С его помощью программист сможет оценить схему SQL-запроса и его длительность, и найти проблемные места. Также есть возможность отфильтровать нужные вам данные – по чрезмерной длительности выполнения, имени таблицы и другим критериям. Информацию можно представить не только в виде текста и таблиц, но и с помощью наглядной графики. Найти этот инструмент можно в MS SQL Server – как одну из многочисленных опций программного обеспечения по управлению базами данных.

Естественно, использовать эту возможность смогут только владельцы клиент-серверных баз. Если же у вас используется файловая, то в качестве замены можно посоветовать технологический журнал от 1С. Конечно, он не настолько удобен и вариативен, но при должных навыках и из него можно узнать всю нужную информацию. Для этого понадобится достаточная квалификация, знания и мощное оборудование, так как запуск технологического журнала потребует приличных ресурсов.

Чтобы открыть MS SQL Profiler и начать работу с базами данных, надо запустить MS SQL, в открывшемся окне, в меню «Сервис» найти нужное приложение и открыть его. По щелчку откроется еще одно окно с просьбой указать сервер, где находятся наши клиент-серверные базы данных. Вы можете подключаться к нескольким серверам и разбираться в структуре их запросов к SQL.

Рис.1 Что такое MS SQL Profiler и где его найтиРис.2 Что такое MS SQL Profiler и где его найти

В терминологии SQL-процесс записи и последующего просмотра событий называется «трассировкой». Именно поэтому после указания данных сервера БД и логина с паролем откроется окно со свойствами трассировки. Наиболее интересной и значимой для нас является закладка «Выбор событий», поскольку там настраивается, что мы хотим видеть в трассировке.

Как настроить MS SQL Profiler для 1С

Чтобы не перегружать сервер и не занимать лишнее пространство на диске, нужно грамотно отфильтровать необходимые события. Несмотря на текстовую подсказку, разъясняющую суть столбца и строки, для оптимального результата нужны определенные знания. Также можно воспользоваться опытом других людей, чем мы и воспользуемся, проставив галки напротив следующих событий:

  • Showplan Statistics Profile – отвечает за отражение подробных сведений плана запросов в текстовом виде;
  • Showplan XML Statistics Profile – отразит графическое представление выполнения запроса;
  • PRC: Completed – будут отражаться тексты запросов, выполняющихся в виде процедур с параметрами;
  • SQL: Batch Completed – трассировка запросов без параметров.

Рис.3 Как настроить MS SQL Profiler для 1С

После сделанной настройки мы сможем увидеть все запросы для всех баз данных, зарегистрированных на указанном сервере. Чтобы исследовать данные конкретной базы, необходимо настроить фильтры для выбранных событий. С помощью кнопки на форме настроек «Фильтры столбцов…» укажем следующие свойства нашей трассировки:

  • DatabaseName – воспользуемся в настройках механизмом «Похоже на» и занесем туда наименование нашей БД. После установки подобного фильтра в нашей трассировке будут отражены только запросы, обрабатываемые в конкретной базе. Возможен множественный выбор, чтобы отобрать 2, 3 и более баз;

Рис.4 DatabaseName

  • DatabaseID – отбор по идентификатору конкретной базы. Узнать этот параметр можно, создав и выполнив запрос «SELECT db_id()” в SQL Server Management Studio. Выбрав в дереве конкретную базу, вы сможете узнать все ее параметры, в том числе и идентификатор;

Рис.5 DatabaseID

    Бесплатная
    консультация
    эксперта Наталья Севорина Консультант-аналитик 1С Спасибо за Ваше обращение! Специалист 1С свяжется с вами в течение 15 минут.

  • Duration – настраивается длительность исполнения запроса. Указывается в миллисекундах;

Рис.6 Duration

  • TextData – фильтр по тексту запроса. Можно отобрать только обращающиеся к конкретной таблице или полям. Необходимо воспользоваться маской из синтаксиса 1С;

Рис.7 TextData

  • RowCounts – отбор по количеству возвращаемых строк. Так вы можете отловить запросы, возвращающие огромное количество строк, чтобы наложить дополнительные условия.

Рис.8 RowCounts

Возможностей намного больше, и если необходима дополнительная настройка – воспользуйтесь подсказками MS SQL Server Profiler, отображающимися в этом же окне. После вышеперечисленных этапов настройки остается только воспользоваться кнопкой «Запустить» и наблюдать за отображением процессов. Окно трассировки разделено на 2 части: в верхней перечисляются события и их характеристики, а в нижней – дополнительная информация.

Работа с трассировкой MS SQL Profiler

Для тестирования трассировки откроем консоль запросов на базе, на которую настроен MS SQL Profiler, и выполним простейший запрос. В окне трассировки отражаются все запросы, подходящие по условиям фильтров, и среди них нужно найти наш запрос. Поможет в этом нижняя часть окна трассировки, где и будет находиться запрос на языке SQL, найдя который, мы можем получить следующую информацию:

  • Сколько выполнялся запрос в тысячных долях секунды – столбец Duration;
  • Сколько произошло чтений из базы данных – Reads;
  • Количество возвращаемых строк – RowCounts;
  • Дату начала и дату окончания в тысячных долях секунды – StartTime и EndTime.

Рис.9 Работа с трассировкой MS SQL Profiler Рис.10 Работа с трассировкой MS SQL Profiler

Если вы перейдете на вышестоящую строку, то в нижней половине перед вами будет графическая схема выполнения запроса. Еще одна вышестоящая строка покажет вам текстовый план выполнения запроса на SQL. Эти данные, как и всю трассировку, можно сохранить в файл для последующего открытия и анализа. Отдельные данные с помощью контекстного меню и функции «Извлечь данные события», а целиком через меню «Файл» — «Сохранить как».

В высоко нагруженных базах бывает очень сложно отследить выполнение своего запроса. Для этого предусмотрены механизмы остановки, паузы и запуска трассировки, чтобы в окно попадали только нужные нам запросы. Они расположены в основном меню или в самом окне трассировки. Также предусмотрена возможность очищать окно вывода информации – используется кнопка в виде стиральной резинки.

Рис.11 Очистка окна вывода информации

MS SQL Profiler содержит в себе обширные возможности для нахождения запросов, серьезно сказывающихся на быстродействии 1С и других систем. Научившись работать с ним однажды, вы будете способны находить проблемные места в любых системах, работающих с базами данных SQL. MS SQL достаточно распространена, и подобные навыки будут по достоинству оценены работодателями.

Add a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *