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

Провел на выходных (чтоб никто не мешал) исследования на копии реальной базы на рабочем сервере. Сравнивал варианты с использованием ВНУТРЕННЕЕ СОЕДИНЕНИЕ и В.
вариант запроса 1:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ПО ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь

вариант запроса 2:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ПО ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент
И ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь

вариант запроса 3:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь)

вариант запроса 4:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент
И ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь)

Результат теста (на 1000 циклов. за один цикл выполнялись все четыре запроса).
вариант 1 - 489 сек.
вариант 2 - 491 сек.
вариант 3 - 490 сек.
вариант 4 - 338 сек.

Вывод: конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ и В в чистом виде обрабатываются одинаково, планы запросов у них тоже одинаковые. Всё согласно теории и рекомендациям с ИТС и всяких книжек от 1С.
НО! использование конструкции В с добавлением внутрь выборки отбора по текущему значению сравнения дает прирост скорости на 30-31%, и план запроса у него иной, менее нагруженный данными. А вот про это как-то не упоминают, все время рекомендуя использовать только конструкцию СОЕДИНЕНИЕ.
P.S. на RLS конструкция 4 дает прирост скорости до 50% по сравнению с другими вариантами.

0-bma1 >А где в третьем варианте условие на Контрагента?
А так ничего удивительного. В первых двух вариантах тянутся данные из ДоступностьКонтрагентов, а в четвертом - нет. Естественно это быстрее.

2(1) Точно, неправильно скопировал запрос в сообщение. Там на самом деле так:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ГДЕ
ЗаказПокупателя.Контрагент В
(ВЫБРАТЬ
ДоступностьКонтрагентов.Контрагент
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь)

2-bma1 >В первых трех вариантах происходит чтение данных из таблиц, а в четвертом варианте присутствует оператор EXISTS, который, в свою очередь, скорее всего просто проверяет вхождение параметров в индекс.

Красиво!

я постоянно так делаю

0-bma1 > 5 очков, осталось провести тестирование на разных СУБД. Есть *.dt? Или количество записей в таблицах приведи хотя бы...

2(6) Разных СУБД под рукой нет, да и желания настолько углубляться тоже. Стоит SQL2008. Решал конкретную задачу - устранение узкого места при просмотре конкретного журнала. Число записей в таблицах - в ЗаказыПокупателей примерно 120 000 записей. в таблице ДоступностьКонтрагентов - 8 000 записей.

Хотя результаты не столь однозначны. Прогнал эту же серию тестов на базе под рабочей нагрузкой. Результат варианта 2 неожиданно вышел вперед на 3% по сравнению с вариантом 4. Однако, использовав эти варианты в РЛС получил иную картину. Замерял секундомером журнал заказов за текущий квартал. Пробегал курсором сверху вниз до конца в ручном режиме (все время нажата клавиша "вниз"). При использовании в РЛС по документу ЗаказПокупателя варианта с "ВНУТРЕННЕЕ СОЕДИНЕНИЕ" - время пробежки в среднем 122 секунды. При использовании 4-ого варианта "В" - 45 секунд. Т.е. тут уже за пределами статистической погрешности. Несколько раз менял запрос в РЛС и делал замеры. При этом одновременно запускал простейший тестовый пример, делал запрос вида:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
исполнял Запрос.Выполнить() в цикле
И мерял время по отладчику. Вариант с соединением при такой проверке давал результат одинаковый или на 3-5% лучше чем с выборкой.
Т.е. в РЛС поведение запросов сильно отличается от поведения запросов в отчетах и обработках.
Платформа 8.2.18.109
На 8.3.4 еще не гонял, т.к. на нее не переходил пока...

Короче, чешу тыковку и думаю, в чем тут хитрость, и как ее использовать.

7-bma1 > У списка, если я правильно понимаю, запрос курсорный. Твой цикл - запрос статический. Ну и для РЛС количество записей в таблицах не так важно, как для статического запроса. В РЛС я только 4-й способ и использую, даже не проводя исследований - в 1Се все исследовали до нас. Для полноты обсуждения нам бы твои планы исполнения, а так же структуру регистра и настройки индексирования поглядеть...

// пакетный запрос рулит
ВЫБРАТЬ разрешенные ДоступностьКонтрагентов.Контрагент КАК Контрагент
поместить НашиКонтрагенты
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь
индексировать по Контрагент
;

ВЫБРАТЬ разрешенные
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
где ЗаказПокупателя.Контрагент В (выбрать * из НашиКонтрагенты)

10-Jimbo > В шаблон ограничения доступа его вставь теперь, умник...

10-Jimbo >Совсем не эффективно.

Никак не могу понять, влияет ли на скорость выполнения запроса порядок следования выражений в конструкции ГДЕ?

13-bma1 >Если MSSQL, то можешь не волноваться, оптимизатор скуля сам всё расставит как надо.

(11) кому и что вставлять речи не было
(12) автор запустит и узнаем удивительные цифры

2(15) нафига в этом запросе опция РАЗРЕШЕННЫЕ, регистр ДоступностьКонтрагентов как раз и определяет, кто кому разрешен. По определению.

речь про RLS была, убери раз мешает. какие цифры выдаст?

2(17) Выдало 2 138 секунд.

18-bma1 >и не удивительно :)

просветите меня, если отфильтровать заказы тоьлко по РАЗРЕШЕННЫМ контрагентам (то есть заюзать эту опцию) будет быстрее?

или у автора запрос размещён в месте, где эту конструкцию использовать нельзя?

20-Зелёный тролль > [url]http://goo.gl/xEr6Iv[/url]
Автор как бы пытается оптимизировать то, что скрывается за этой конструкцией...

2(20) Я и пытаюсь определить наиболее оптимальный механизм фильтрации в RLS. Типовое RLS меня давно уже не устраивает своей негибкостью и излишним универсализмом, когда одним запросом пытаются сделать абсолютно все фильтры. В результате в запросе оказывается куча ненужного хлама, а управление доступами становится неуправляемым (если количество контрагентов, к примеру и пользователей растет и требуется создать пересекающиеся области доступа).

Вот выдержка из моей статьи для сторонних разработчиков CRM системы, когда меня попросили описать схему расстановки доступов в 1С:
В типовой конфигурации 1С:Управление Торговлей разграничение доступа пользователей к контрагентам осуществляется через группы доступа и группы пользователей. У каждого контрагента в карточке проставляется группа доступа (каждый контрагент может быть только в одной группе доступа).
Каждый пользователь обязательно включается в какую-либо группу пользователей (может быть включен в несколько групп пользователей).
Каждой группе пользователей указывается список групп доступа, с указанием, имеет он для данной группы доступа только право чтения или имеет еще право записи. Право чтения дает одновременно право использования контрагента в документах. Право записи позволяет изменять карточку контрагента.
Ответственными в заказах указываются пользователи, непосредственно сделавшие заказ.
Плюсы данной схемы:
Схема относительно простая. Включив или исключив пользователя из какой-либо группы пользователей мы сразу изменяем его права по доступу с целой группе контрагентов.
Минусы данной схемы:
Схемой сложно администрировать при большом количестве контрагентов и пользователей. Создание пересекающихся множеств контрагентов для разных пользователей требует большого объема ручной работы по созданию новой группы пользователей, и ее расстановке контрагентам, а так же необходимости указания правил доступа к этой группе контрагентов для целого ряда групп пользователей.
Если пользователь только один, то для Пользователя №1 необходимо создать всего две группы доступа к контрагентам:
а. Группа №1 - контрагенты, которых Пользователь №1 может изменять,
б. Группа №2 - контрагенты, которых Пользователь №1 может видеть, но не может изменять.
И создать одну группу пользователей "Группа А", в которой перечисляются доступы к группам доступа:
Группы доступа Уровень доступа
Группа №1 Запись
Группа №2 Чтение
Пользователь №1 включается в группу пользователей "Группа А"
Если добавляется еще один Пользователь №2, у которого нет общих контрагентов с Пользователем №1.
Тогда необходимо создать еще две группы доступа:
а. Группа №3 - контрагенты, которых Пользователь №2 может изменять,
б. Группа №4 - контрагенты, которых Пользователь №2 может видеть, но не может изменять.
И необходимо создать одну группу пользователей "Группа Б", в которой перечисляются доступы к группам доступа:
Группы доступа Уровень доступа
Группа №3 Запись
Группа №4 Чтение
Пользователь №2 включается в группу пользователей "Группа Б".

Если же добавляется Пользователь №2, у которого есть общие контрагенты с Пользователем №1. Тогда Необходимо создать ШЕСТЬ новых групп доступа:
а. Группа №3 - контрагенты, которых только Пользователь №2 может изменять,
б. Группа №4 - контрагенты, которых только Пользователь №2 может видеть, но не может изменять,
в. Группа №5 - контрагенты, которых Пользователь №2 может изменять, и Пользователь №1 может изменять,
г. Группа №6 - контрагенты, которых Пользователь №2 может изменять, и Пользователь №1 может видеть,
д. Группа №7 - контрагенты, которых Пользователь №2 может видеть, и Пользователь №1 может изменять,
е. Группа №8 - контрагенты, которых Пользователь №2 может видеть, и Пользователь №1 может видеть.
Затем, у части контрагентов из группы доступа Группа №1, группа доступа меняется на Группу №5 и Группу№7, а у части контрагентов из Группы №2, группа доступа меняется на Группу №6 и Группу №8.
После этого надо создать группу пользователей "Группа Б", в которой перечисляются доступы к группам доступа:
Группы доступа Уровень доступа
Группа №3 Запись
Группа №4 Чтение
Группа №6 Запись
Группа №7 Чтение
В группе пользователей "Группа А" вносятся изменения:
Группы доступа Уровень доступа
Группа №1 Запись
Группа №2 Чтение
Группа №6 Чтение
Группа №7 Запись
Создается дополнительная группа доступа "Группа В", в которой перечисляются доступы к группам доступа:
Группы доступа Уровень доступа
Группа №5 Запись
Группа №8 Чтение
Пользователь №1 теперь должен быть включен в группы: "Группа А" и "Группа В".
Пользователь №2 включается в группы пользователей "Группа Б" и "Группа В".
Такая схема работоспособна на небольших клиентских базах, при небольшом количестве пользователей. При росте количества пользователей и/или контрагентов в базе, управление доступом становится крайне затруднительным делом, с огромным количеством ручного труда и большим риском ошибок. На практике, в организациях использующих разделение доступа к контрагентам, случаи, когда возможно частичное перекрытие списков доступных контрагентов, считаются недопустимыми. В крайнем случае организации отказываются от ограничения доступа пользователей к контрагентам типовым способом (например разделяя пользователей по разным базам и объединяя информацию потом только в центральной базе, что создает значительные сложности из-за ситуаций одновременного резервирования одного и того-же товара в разных базах, разноски платежей с ручным разделением по разным накладным, если они были сформированы в разных базах и значительно снижает производительность работы). Чаще организации переделывают типовой механизм в принципе.
Схема не позволяет отслеживать основных менеджеров в заказах покупателей и рассчитывать по ним переменную часть зарплаты менеджеров.
Схема совершенно не соответствует техническому заданию по настройке программы ЭЛМА.

В статье была куча диаграмм с пересекающимися областями. Но и без них можно разобраться.

В принципе, в 8.2 (за 8.3 не скажу) если основная нагрузка у пользователя будет на отчеты - RLS выгоднее делать на СОЕДИНЕНИЕ, если интерактивное листание журналов - на условии В...

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

лучше бы сделали владельца объекта, который мог бы делегировать права другим юзерам.

[quote=Billi;35044326]НО! использование конструкции В с добавлением внутрь выборки отбора по текущему значению сравнения дает прирост скорости на 30-31%, и план запроса у него иной, менее нагруженный данными.[/quote]

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

26-ЧестныйЧеловек >А причём здесь я? [img]http://s7.rimg.info/beb8dfcf85da0257b1cfa1583ae5d43a.gif[/img]

2(26) А не выйдет!
Если внутри подзаппроса в конструкции В можно поставить условие сравнения с реквизитом вышележащего запроса (ГДЕ ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент), то в конструкции СОЕДИНИТЬ такой отбор во вложенном подзапросе недопустим.

27-Billi > Извините, ошибся))
28-bma1 > да ну, вы что-то не так понимаете..
вот запрос:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
внутреннее соединение
(ВЫБРАТЬ ПЕРВЫЕ 1
1 как ПолеВложенного
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент
И ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь) как Вложенный
По 1 = Вложенный.ПолеВложенного

Понял про Контрагента.
У Вас в третьем запросе нет отбора по нему, поэтому 2 варианта:
1. Убрать отбор по Контрагенту из моего запроса
2. Поле контрагент также выбирать во вложенном и использовать его в условиях соединения.

Поэксперементируйте с соединением с вложенным подзапросом, в РЛС он часто дает производительность в несколько раз по сравнению с В или другими способами. Проверено лично

2(30)
1. Убрать отбор по Контрагенту из моего запроса - мы получим сравнение неизвестно чего с неизвестно чем, т.к. результатом выполнения этого вложенного запроса будет просто факт того, что пользователю вообще разрешен доступ к какому-то контрагенту, а разрешен ли ему доступ к конкретному контрагенту - мы эту информацию не получаем.
2. Поле контрагент также выбирать во вложенном и использовать его в условиях соединения - а это четвертый вариант.

1. Т.е. Ваш 3й вариант это сравнение неизвестно чего с чем, т.к. там не используется отбор по контрагенту?
2. Ничего это не 4й вариант, т.к. ваш вариант использует В, мой inner join, план запроса будет разным

2(32) по 3-му варианту см пост 2.
А " Поле контрагент также выбирать во вложенном и использовать его в условиях соединения." не получится, мы не можем делать отбор по этому полю в присоединяемом запросе. Иначе мы получаем на выходе очень большой объем данных, который потом надо сравнивать с вышележащими данными. Т.о. если мы хотим отобрать в подзапросе данные максимально - приходится использовать В. И я уже писал. что для листания журналов эта конструкция дает преимущество, а соединение лучше работает на выборках для запросов.