gifts2017

Преобразование T-SQL запроса MSSQL в запрос 1С 8

Опубликовал Invaa (Invaa) в раздел Программирование - Практика программирования

Одним из традиционных способов определения узких мест работы информационной базы 1с 8 под MSSQL является отлов медленных запросов профайлером. Но видя "много букоф" на языке T-SQL, не так уж и просто понять, как найти запрос 1с, который их порождает. Особенно, если дополнительно используется механизм ограничения прав на уровне записей. В данной статье предлогается один из подходов упрощающих жизнь оптимизатору с использованием обработки, конвертирующей T-SQL запрос в запрос, похожий на язык запросов 1с 8.

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

T-SQL запрос:

SELECT
MIN(CASE WHEN T2.SDBL_RLS_SIGNAL_ = 0x01 AND ISNULL(T7.SDBL_RLS_SIGNAL_,0x01) = 0x01 THEN 0x01 ELSE 0x00 END),
T1._Document367_IDRRef,
T7.Date_Time_,
T7.Number_,
T7.Fld7948RRef,
T7.Fld7949RRef,
T7.Fld7950RRef,
T7.Fld7956RRef,
T7.Fld7954_,
T7.Fld7955RRef,
CAST(T7.Fld7951_ AS NVARCHAR(300)),
T7.Fld7946_,
T7.Fld7947_,
T7.Fld7962RRef,
T7.Fld17647RRef
FROM _Document367_VT7991 T1 WITH(NOLOCK)
INNER JOIN (SELECT
CASE WHEN (EXISTS(SELECT
@P1 AS Q_002_F_000_
FROM (SELECT DISTINCT
@P1 AS SDBL_RLS_SIGNAL_,
T5._Fld16928RRef AS Q_001_F_000RRef
FROM _InfoRg16925 T5 WITH(NOLOCK)
INNER JOIN
_InfoRg16833 T6 WITH(NOLOCK)
ON ((((
T5._Fld16926 = @P2) AND (T6._Fld16834RRef = @P3)) AND (T6._Fld16835_TYPE = @P4 AND T6._Fld16835_RTRef = @P5 AND T6._Fld16835_RRRef = @P6)) AND (T6._Fld16837_TYPE = T5._Fld16927_TYPE AND T6._Fld16837_RTRef = T5._Fld16927_RTRef AND T6._Fld16837_RRRef = T5._Fld16927_RRRef))) T4
WHERE 1=1)) THEN @P1 ELSE @P7 END AS SDBL_RLS_SIGNAL_,
T3._IDRRef AS IDRRef
FROM _Document367 T3 WITH(NOLOCK)) T2
ON T1._Document367_IDRRef = T2.IDRRef
LEFT OUTER JOIN (SELECT
CASE WHEN (EXISTS(SELECT
@P1 AS Q_002_F_000_
FROM (SELECT DISTINCT
@P1 AS SDBL_RLS_SIGNAL_,
T10._Fld16928RRef AS Q_001_F_000RRef
FROM _InfoRg16925 T10 WITH(NOLOCK)
INNER JOIN
_InfoRg16833 T11 WITH(NOLOCK)
ON ((((
T10._Fld16926 = @P2) AND (T11._Fld16834RRef = @P3)) AND (T11._Fld16835_TYPE = @P4 AND T11._Fld16835_RTRef = @P5 AND T11._Fld16835_RRRef = @P6)) AND (T11._Fld16837_TYPE = T10._Fld16927_TYPE AND T11._Fld16837_RTRef = T10._Fld16927_RTRef AND T11._Fld16837_RRRef = T10._Fld16927_RRRef))) T9
WHERE 1=1)) THEN @P1 ELSE @P7 END AS SDBL_RLS_SIGNAL_,
T8._IDRRef AS IDRRef,
T8._Date_Time AS Date_Time_,
T8._Number AS Number_,
T8._Posted AS Posted_,
T8._Fld7946 AS Fld7946_,
T8._Fld7947 AS Fld7947_,
T8._Fld7948RRef AS Fld7948RRef,
T8._Fld7949RRef AS Fld7949RRef,
T8._Fld7950RRef AS Fld7950RRef,
T8._Fld7951 AS Fld7951_,
T8._Fld7954 AS Fld7954_,
T8._Fld7955RRef AS Fld7955RRef,
T8._Fld7956RRef AS Fld7956RRef,
T8._Fld7962RRef AS Fld7962RRef,
T8._Fld17647RRef AS Fld17647RRef
FROM _Document367 T8 WITH(NOLOCK)
WHERE
T8._Date_Time >= @P8 AND T8._Date_Time @P9 AND T8._Fld7948RRef = @P10 AND T8._Fld7956RRef = @P11) T7
ON T1._Document367_IDRRef = T7.IDRRef
WHERE T7.Posted_ = @P1 AND ((T7.Date_Time_ >= @P8) AND (T7.Date_Time_ @P9)) AND (T7.Fld7948RRef = @P10) AND (T7.Fld7956RRef = @P11) AND (T1._Fld7994_TYPE = @P12)
GROUP BY
T1._Document367_IDRRef,
T7.Date_Time_,
T7.Number_,
T7.Fld7948RRef,
T7.Fld7949RRef,
T7.Fld7950RRef,
T7.Fld7956RRef,
T7.Fld7954_,
T7.Fld7955RRef,
CAST(T7.Fld7951_ AS NVARCHAR(300)),
T7.Fld7946_,
T7.Fld7947_,
T7.Fld7962RRef,
T7.Fld17647RRef
ORDER BY 3, 4

Преобразованный запрос:

ВЫБРАТЬ

МИНИМУМ(ВЫБОР КОГДА T2.SDBL_RLS_SIGNAL_ = 1 И ISNULL(T7.SDBL_RLS_SIGNAL_,0x01) = 1 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ),

T1.Документ.ПлатежноеПоручениеВходящее_Ссылка,
T7.Дата_,
T7.Номер_,
T7.Организация,
T7.СчетОрганизации,
T7.Контрагент,
T7.ВидОперации,
T7.СуммаДокумента,
T7.ВалютаДокумента,
ВЫРАЗИТЬ(T7.НазначениеПлатежа КАК СТРОКА(300)),
T7.ДатаВходящегоДокумента,
T7.НомерВходящегоДокумента,
T7.Ответственный,
T7.Автор
ИЗ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа T1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ВЫБОР КОГДА (СУЩЕСТВУЕТ(ВЫБРАТЬ
@P1 КАК Q_002_F_000_
ИЗ (ВЫБРАТЬ РАЗЛИЧНЫЕ
@P1 КАК SDBL_RLS_SIGNAL_,
T5.ГруппаДоступа КАК Q_001_F_000RRef
ИЗ РегистрСведений.ТаблицыГруппДоступа T5
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ГруппыЗначенийДоступа T6
ПО ((((T5.Таблица = @P2) И (T6.ВидДоступа = @P3)) И (T6.ЗначениеДоступа = @P4 И T6.ЗначениеДоступа = @P5 И T6.ЗначениеДоступа = @P6)) И (T6.ГруппаДоступа = T5.Пользователь И T6.ГруппаДоступа = T5.Пользователь И T6.ГруппаДоступа = T5.Пользователь))) T4
ГДЕ 1=1)) ТОГДА @P1 ИНАЧЕ @P7 КОНЕЦ КАК SDBL_RLS_SIGNAL_,
T3._Ссылка КАК Ссылка
ИЗ Документ.ПлатежноеПоручениеВходящее T3 ) T2
ПО T1.Документ.ПлатежноеПоручениеВходящее_Ссылка = T2.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ВЫБОР КОГДА (СУЩЕСТВУЕТ(ВЫБРАТЬ
@P1 КАК Q_002_F_000_
ИЗ (ВЫБРАТЬ РАЗЛИЧНЫЕ
@P1 КАК SDBL_RLS_SIGNAL_,
T10.ГруппаДоступа КАК Q_001_F_000RRef
ИЗ РегистрСведений.ТаблицыГруппДоступа T10
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ГруппыЗначенийДоступа T11
ПО ((((T10.Таблица = @P2) И (T11.ВидДоступа = @P3)) И (T11.ЗначениеДоступа = @P4 И T11.ЗначениеДоступа = @P5 И T11.ЗначениеДоступа = @P6)) И (T11.ГруппаДоступа = T10.Пользователь И T11.ГруппаДоступа = T10.Пользователь И T11.ГруппаДоступа = T10.Пользователь))) T9
ГДЕ 1=1)) ТОГДА @P1 ИНАЧЕ @P7 КОНЕЦ КАК SDBL_RLS_SIGNAL_,
T8._Ссылка КАК Ссылка,
T8._Дата КАК Дата_,
T8._Номер КАК Номер_,
T8._Проведен КАК Проведен_,
T8.ДатаВходящегоДокумента КАК ДатаВходящегоДокумента,
T8.НомерВходящегоДокумента КАК НомерВходящегоДокумента,
T8._Организация КАК Организация,
T8._СчетОрганизации КАК СчетОрганизации,
T8._Контрагент КАК Контрагент,
T8.НазначениеПлатежа КАК НазначениеПлатежа,
T8.СуммаДокумента КАК СуммаДокумента,
T8._ВалютаДокумента КАК ВалютаДокумента,
T8._ВидОперации КАК ВидОперации,
T8._Ответственный КАК Ответственный,
T8._Автор КАК Автор
ИЗ Документ.ПлатежноеПоручениеВходящее T8
ГДЕ T8._Дата >= @P8 И T8._Дата @P9 И T8._Организация = @P10 И T8._ВидОперации = @P11) T7
ПО T1.Документ.ПлатежноеПоручениеВходящее_Ссылка = T7.Ссылка
ГДЕ T7.Проведен_ = @P1 И ((T7.Дата_ >= @P8) И (T7.Дата_ @P9)) И (T7.Организация = @P10) И (T7.ВидОперации = @P11) И (T1.Сделка = @P12)
СГРУППИРОВАТЬ ПО
T1.Документ.ПлатежноеПоручениеВходящее_Ссылка,
T7.Дата_,
T7.Номер_,
T7.Организация,
T7.СчетОрганизации,
T7.Контрагент,
T7.ВидОперации,
T7.СуммаДокумента,
T7.ВалютаДокумента,
ВЫРАЗИТЬ(T7.НазначениеПлатежа КАК СТРОКА(300)),
T7.ДатаВходящегоДокумента,
T7.НомерВходящегоДокумента,
T7.Ответственный,
T7.Автор
УПОРЯДОЧИТЬ ПО 3, 4

Осталось почистить ненужную информацию и строки, касающиесь RLS.

Получаем:

ВЫБРАТЬ
МИНИМУМ(ВЫБОР КОГДА T2.SDBL_RLS_SIGNAL_ = 1 И ISNULL(T7.SDBL_RLS_SIGNAL_,0x01) = 1 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ),
T1.Документ.ПлатежноеПоручениеВходящее_Ссылка,
T7.Дата_,
T7.Номер_,
T7.Организация,
T7.СчетОрганизации,
T7.Контрагент,
T7.ВидОперации,
T7.СуммаДокумента,
T7.ВалютаДокумента,
ВЫРАЗИТЬ(T7.НазначениеПлатежа КАК СТРОКА(300)),
T7.ДатаВходящегоДокумента,
T7.НомерВходящегоДокумента,
T7.Ответственный,
T7.Автор
ИЗ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа T1

ГДЕ T7.Проведен_ = @P1 И ((T7.Дата_ >= @P8) И (T7.Дата_ @P9)) И (T7.Организация = @P10) И (T7.ВидОперации = @P11) И (T1.Сделка = @P12)
СГРУППИРОВАТЬ ПО
T1.Документ.ПлатежноеПоручениеВходящее_Ссылка,
T7.Дата_,
T7.Номер_,
T7.Организация,
T7.СчетОрганизации,
T7.Контрагент,
T7.ВидОперации,
T7.СуммаДокумента,
T7.ВалютаДокумента,
ВЫРАЗИТЬ(T7.НазначениеПлатежа КАК СТРОКА(300)),
T7.ДатаВходящегоДокумента,
T7.НомерВходящегоДокумента,
T7.Ответственный,
T7.Автор
УПОРЯДОЧИТЬ ПО 3, 4

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

Здесь параметры вида @P1 - это выражения, соответствующие некоторым параметрам запроса 1с. Иначе говоря, вместо них будет что-то вроде &МойПараметр.

P.S. Это первая пробная версия. Так что пожелания и баг репорты приветствуются Smile

Скачать файлы

Наименование Файл Версия Размер
Обработка конвертации запроса 8.2 146
.epf 19,92Kb
19.07.12
146
.epf 19,92Kb Скачать

См. также

PowerTools от 1 000
Подписаться Добавить вознаграждение

Комментарии

1. Герман (German) 20.07.12 23:38
Как учитываются параметры виртуальных таблиц?
Как интерпретируется DATEPART?
Что делаете с типизацией?

А вообще уже было Трассировка Ei
3,5 года назад :)
2. Invaa (Invaa) 21.07.12 13:33
За ссылку спасибо, действительно хороший вариант.

Что касается работы с виртуальными таблицами и DATEPART, то пока это не релизовано.
Типизация у меня реализована следующим образом: синтаксис заменяется на аналог из 1С если такой имеется и просто переводится на русский, если аналогичного типа в 1С нет.
3. kiril lipatov (kilokilo) 27.07.12 01:22
(1) (2) Но в профайлере же мы увидим оптимизированные движком SQL запрос, а не преобразованный исходный? Или я ошибаюсь?
4. Alexandr t (eligor) 27.07.12 09:55
Безусловно в данной публикации рассмотрен и приведен нетривиальный метод преобразования запросов, довольно поучительный.
Но кажется в поиске "узких мест" не обязательно выбирать путь "подземный ход на чердак".
Если вы являетесь администратором данной базы данных 1С, то достаточно опросить пользователей и выявлять подобные "узкие места" в работе базы моделированием ситуации с использованием встроенного замера производительности 1С там достаточно одного нажатия на будильник и в конфигураторе вы увидите где именно происходят задержки выполнения, это намного легче чем использование сторонних средств, да и далеко не всегда базы 1С крутятся именно на MS SQL.
Но в целом автору статьи большой респект за описание методики и модели оценочного преобразования запроса T-SQL в аналог запроса языка 1С.
5. Invaa (Invaa) 27.07.12 10:06
(3) kilokilo,

Мы увидим оптимизированный запрос движком 1С. SQL Server оптимизирует план выполнения запроса, при этом текст T-SQL запроса переданный 1Сом не меняется.
6. Invaa (Invaa) 27.07.12 10:12
(4) eligor,

замер производительности - это очень хорошая вещь, но он покажет только производительность в конкретном сеансе и в конкретной среде работы сеанса. А отловленные запросы профайлером могут указать и на сам проблемный сеанс (например, сеанс пользователя с существенно отличными от остальных пользовательскими настройками) и на проблемные условия в среде в которой работает сеанс (время дня, когда выполняется закрытие смены другими пользователями и т.п.).
7. Alexandr t (eligor) 27.07.12 10:27
Обычно "проблемные условия в среде в которой работает сеанс (время дня, когда выполняется закрытие смены другими пользователями и т.п.)." выявляются опросом пользователей, в 99% случаев сами-же пользователи и обращаются.
И как скажите мне профайлером вообще можно оценить конкретный сеанс!???? Только источник увидеть, т.е. пользовательскую машину, а если БД на управляемых формах, либо WEB приложение!?
Профайлер вещь хорошая..., но количество запросов которые "сыпет" 1С в БД MS SQL оооочень велико, для отрисовки справочника например на 1 обновление 1С делает 2(ДВА) запроса один по колонке сортировки, другой по сортировки UIN-ов, для чтения регистров от 4 до 6 запросов), и довольно проблематично выявлять запросы с большим количеством duration's либо reads'ов вот это бы автоматизировать...
Да и повторюсь MS SQL как среда развертывания баз 1С далеко не панацея.
8. Alexandr t (eligor) 27.07.12 10:39
Invaa 27.07.12 10:06
(3) kilokilo,
"SQL Server оптимизирует план выполнения запроса" - ужас люди!!!! SQL Server не оптимизирует план запроса, он ЕГО СТРОИТ!!!!
А вот то, что 1С параметризует запрос это очень хорошо, но все равно кэш запросов довольно быстро растет, потому что в 1С все равно их много.

Как вариант использовать ключи сеанса типа /clearcashe и сброса пользовательских настроек непосредственно в БД MS SQL 1С таблички Config, ConfigSave, v8users, Params и !!! Files, но это крайне осторожно, не забывая о версионности объектов и ссылок.
9. Доржи Балбаров (Angeros) 30.07.12 07:19
Ни разу не приходилось прибегать к профайлеру. Все проблемы с производительностью решаются с помощью шаблонных решений оптимизации запроса. Такие как - не обращаться к данным в базе более 1ого раза. использовать индексы, иногда помогает разбивка запроса на более простые части. и т.д.
10. Invaa (Invaa) 06.08.12 15:27
(8) eligor,

прежде чем капс-локить на кого-нибудь, разберитесь в мат. части...

/ClearCache — очистка кэша клиент-серверных вызовов. На кеш запросов (имелась в виду статистика оптимизации в ms sql server?) этот параметр не влияет.

вот вам статья на "почитать" про оптимизацию плана выполнения запроса ms sql server:
http://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/

и еще интересная статья Гилева
http://www.gilev.ru/1c/81/index/optimquery.htm
11. Alexandr t (eligor) 07.08.12 09:48
(10) Invaa,
Ну хорошо, хорошо не буду спорить по поводу /ClearCache , сами надеюсь почитаете про него, он же ведь не влияет на сам MS SQL в общем, а только какой-то сброс для одного рабочего места, правильно? А по поводу оптимизации планов запросов... ну тут поспорить можно, вот я себе как-то смутно представляю при чем тут оптимизация запросов на самом SQL? Из 1С-ки формируется запрос, уже переведенный в T-SQL, для него строится план выполнения 1,2, и т.д. (строит сам MS SQL) и на основе статистики в данный момент времени сам MS SQL и выбирает какой ему лучше использовать план, либо построит новый. Но все это лишь бла бла бла, на самом деле наверное все-же лучше правильно организовать построение и хранение данных в самой 1С и по возможности избегать написание монстроидальных запросов(в 8.3 корректно поддерживается более 400 уровней вложенности запросов дерзайте), для полей(реквизитов, измерений и т.п.) связи и условий выборки лучше вводить индексы, ну и тому подобные несложные вещи. Повторюсь MS SQL не панацея! и у микросовта всегда был свой взгляд на "разработку и реализацию" любых комплексов и серверов... эх ка бы не удачная реализация офисной печати+пакет офисных програм... глядишь винда была бы и не такая популярная, не зацикливайтесь на ней
12. Alexandr t (eligor) 07.08.12 09:50
(10) Invaa,
Цитата из вашей ссылки от Гилева
Поиск длительных запросов SQL Server Profiler-ом

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