К списку форумов К списку тем
Регистрация    Правила    Главная форума    Поиск   
Имя: Пароль:
Рекомендовать в новости

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

0 - 28.04.2014 - 10:14
Провел на выходных (чтоб никто не мешал) исследования на копии реальной базы на рабочем сервере. Сравнивал варианты с использованием ВНУТРЕННЕЕ СОЕДИНЕНИЕ и В.
вариант запроса 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% по сравнению с другими вариантами.



1 - 28.04.2014 - 13:16
0-bma1 >А где в третьем варианте условие на Контрагента?
А так ничего удивительного. В первых двух вариантах тянутся данные из ДоступностьКонтрагентов, а в четвертом - нет. Естественно это быстрее.
2 - 28.04.2014 - 14:09
2(1) Точно, неправильно скопировал запрос в сообщение. Там на самом деле так:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
ГДЕ
ЗаказПокупателя.Контрагент В
(ВЫБРАТЬ
ДоступностьКонтрагентов.Контрагент
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь)
3 - 28.04.2014 - 15:30
2-bma1 >В первых трех вариантах происходит чтение данных из таблиц, а в четвертом варианте присутствует оператор EXISTS, который, в свою очередь, скорее всего просто проверяет вхождение параметров в индекс.
4 - 28.04.2014 - 18:19
Красиво!
Гость
5 - 28.04.2014 - 18:49
я постоянно так делаю
Гость
6 - 28.04.2014 - 21:01
0-bma1 > 5 очков, осталось провести тестирование на разных СУБД. Есть *.dt? Или количество записей в таблицах приведи хотя бы...
7 - 28.04.2014 - 21:37
2(6) Разных СУБД под рукой нет, да и желания настолько углубляться тоже. Стоит SQL2008. Решал конкретную задачу - устранение узкого места при просмотре конкретного журнала. Число записей в таблицах - в ЗаказыПокупателей примерно 120 000 записей. в таблице ДоступностьКонтрагентов - 8 000 записей.

Хотя результаты не столь однозначны. Прогнал эту же серию тестов на базе под рабочей нагрузкой. Результат варианта 2 неожиданно вышел вперед на 3% по сравнению с вариантом 4. Однако, использовав эти варианты в РЛС получил иную картину. Замерял секундомером журнал заказов за текущий квартал. Пробегал курсором сверху вниз до конца в ручном режиме (все время нажата клавиша "вниз"). При использовании в РЛС по документу ЗаказПокупателя варианта с "ВНУТРЕННЕЕ СОЕДИНЕНИЕ" - время пробежки в среднем 122 секунды. При использовании 4-ого варианта "В" - 45 секунд. Т.е. тут уже за пределами статистической погрешности. Несколько раз менял запрос в РЛС и делал замеры. При этом одновременно запускал простейший тестовый пример, делал запрос вида:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
исполнял Запрос.Выполнить() в цикле
И мерял время по отладчику. Вариант с соединением при такой проверке давал результат одинаковый или на 3-5% лучше чем с выборкой.
Т.е. в РЛС поведение запросов сильно отличается от поведения запросов в отчетах и обработках.
Платформа 8.2.18.109
На 8.3.4 еще не гонял, т.к. на нее не переходил пока...
8 - 28.04.2014 - 21:40
Короче, чешу тыковку и думаю, в чем тут хитрость, и как ее использовать.
Гость
9 - 28.04.2014 - 22:22
7-bma1 > У списка, если я правильно понимаю, запрос курсорный. Твой цикл - запрос статический. Ну и для РЛС количество записей в таблицах не так важно, как для статического запроса. В РЛС я только 4-й способ и использую, даже не проводя исследований - в 1Се все исследовали до нас. Для полноты обсуждения нам бы твои планы исполнения, а так же структуру регистра и настройки индексирования поглядеть...
10 - 29.04.2014 - 08:40
// пакетный запрос рулит
ВЫБРАТЬ разрешенные ДоступностьКонтрагентов.Контрагент КАК Контрагент
поместить НашиКонтрагенты
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь
индексировать по Контрагент
;

ВЫБРАТЬ разрешенные
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
где ЗаказПокупателя.Контрагент В (выбрать * из НашиКонтрагенты)
Гость
11 - 29.04.2014 - 10:24
10-Jimbo > В шаблон ограничения доступа его вставь теперь, умник...
12 - 29.04.2014 - 10:46
10-Jimbo >Совсем не эффективно.
13 - 29.04.2014 - 11:22
Никак не могу понять, влияет ли на скорость выполнения запроса порядок следования выражений в конструкции ГДЕ?
14 - 29.04.2014 - 11:35
13-bma1 >Если MSSQL, то можешь не волноваться, оптимизатор скуля сам всё расставит как надо.
15 - 29.04.2014 - 14:03
(11) кому и что вставлять речи не было
(12) автор запустит и узнаем удивительные цифры
16 - 29.04.2014 - 15:55
2(15) нафига в этом запросе опция РАЗРЕШЕННЫЕ, регистр ДоступностьКонтрагентов как раз и определяет, кто кому разрешен. По определению.
17 - 29.04.2014 - 16:51
речь про RLS была, убери раз мешает. какие цифры выдаст?
18 - 29.04.2014 - 20:01
2(17) Выдало 2 138 секунд.
19 - 29.04.2014 - 20:13
18-bma1 >и не удивительно :)
20 - 29.04.2014 - 20:34
просветите меня, если отфильтровать заказы тоьлко по РАЗРЕШЕННЫМ контрагентам (то есть заюзать эту опцию) будет быстрее?

или у автора запрос размещён в месте, где эту конструкцию использовать нельзя?
Гость
21 - 29.04.2014 - 22:17
20-Зелёный тролль > http://goo.gl/xEr6Iv
Автор как бы пытается оптимизировать то, что скрывается за этой конструкцией...
22 - 30.04.2014 - 10:16
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 включается в группы пользователей "Группа Б" и "Группа В".
Такая схема работоспособна на небольших клиентских базах, при небольшом количестве пользователей. При росте количества пользователей и/или контрагентов в базе, управление доступом становится крайне затруднительным делом, с огромным количеством ручного труда и большим риском ошибок. На практике, в организациях использующих разделение доступа к контрагентам, случаи, когда возможно частичное перекрытие списков доступных контрагентов, считаются недопустимыми. В крайнем случае организации отказываются от ограничения доступа пользователей к контрагентам типовым способом (например разделяя пользователей по разным базам и объединяя информацию потом только в центральной базе, что создает значительные сложности из-за ситуаций одновременного резервирования одного и того-же товара в разных базах, разноски платежей с ручным разделением по разным накладным, если они были сформированы в разных базах и значительно снижает производительность работы). Чаще организации переделывают типовой механизм в принципе.
Схема не позволяет отслеживать основных менеджеров в заказах покупателей и рассчитывать по ним переменную часть зарплаты менеджеров.
Схема совершенно не соответствует техническому заданию по настройке программы ЭЛМА.
23 - 30.04.2014 - 10:21
В статье была куча диаграмм с пересекающимися областями. Но и без них можно разобраться.

В принципе, в 8.2 (за 8.3 не скажу) если основная нагрузка у пользователя будет на отчеты - RLS выгоднее делать на СОЕДИНЕНИЕ, если интерактивное листание журналов - на условии В...
24 - 30.04.2014 - 18:27
получается группы доступа вообще лишние. т.к. самое оно это для каждого объекта иметь свою персональную группу доступа.
25 - 30.04.2014 - 18:28
лучше бы сделали владельца объекта, который мог бы делегировать права другим юзерам.
Гость
26 - 04.05.2014 - 18:30
Цитата:
Сообщение от Billi Посмотреть сообщение
НО! использование конструкции В с добавлением внутрь выборки отбора по текущему значению сравнения дает прирост скорости на 30-31%, и план запроса у него иной, менее нагруженный данными.
Подсказываю самый оптимальный вариант:
Переделай 4й запрос на внутреннее соединение с вложенным подзапросом. Да, с вложенным подзапросом, хотя с ними фирма 1С не рекомендует соединяться, но в данном случае это самый оптимальный вариант, т.к. справа у тебя всегда будет одна запись независимо от размера регистра ДоступностьКонтрагентов
27 - 04.05.2014 - 19:05
26-ЧестныйЧеловек >А причём здесь я?
28 - 05.05.2014 - 14:18
2(26) А не выйдет!
Если внутри подзаппроса в конструкции В можно поставить условие сравнения с реквизитом вышележащего запроса (ГДЕ ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент), то в конструкции СОЕДИНИТЬ такой отбор во вложенном подзапросе недопустим.
Гость
29 - 06.05.2014 - 20:37
27-Billi > Извините, ошибся))
28-bma1 > да ну, вы что-то не так понимаете..
вот запрос:
ВЫБРАТЬ
ЗаказПокупателя.Ссылка
ИЗ
Документ.ЗаказПокупателя КАК ЗаказПокупателя
внутреннее соединение
(ВЫБРАТЬ ПЕРВЫЕ 1
1 как ПолеВложенного
ИЗ
РегистрСведений.ДоступностьКонтрагентов КАК ДоступностьКонтрагентов
ГДЕ
ДоступностьКонтрагентов.Контрагент = ЗаказПокупателя.Контрагент
И ДоступностьКонтрагентов.Пользователь = &ТекущийПользователь) как Вложенный
По 1 = Вложенный.ПолеВложенного
Гость
30 - 06.05.2014 - 20:40
Понял про Контрагента.
У Вас в третьем запросе нет отбора по нему, поэтому 2 варианта:
1. Убрать отбор по Контрагенту из моего запроса
2. Поле контрагент также выбирать во вложенном и использовать его в условиях соединения.

Поэксперементируйте с соединением с вложенным подзапросом, в РЛС он часто дает производительность в несколько раз по сравнению с В или другими способами. Проверено лично
31 - 07.05.2014 - 08:17
2(30)
1. Убрать отбор по Контрагенту из моего запроса - мы получим сравнение неизвестно чего с неизвестно чем, т.к. результатом выполнения этого вложенного запроса будет просто факт того, что пользователю вообще разрешен доступ к какому-то контрагенту, а разрешен ли ему доступ к конкретному контрагенту - мы эту информацию не получаем.
2. Поле контрагент также выбирать во вложенном и использовать его в условиях соединения - а это четвертый вариант.
Гость
32 - 07.05.2014 - 20:11
1. Т.е. Ваш 3й вариант это сравнение неизвестно чего с чем, т.к. там не используется отбор по контрагенту?
2. Ничего это не 4й вариант, т.к. ваш вариант использует В, мой inner join, план запроса будет разным
33 - 07.05.2014 - 22:05
2(32) по 3-му варианту см пост 2.
А " Поле контрагент также выбирать во вложенном и использовать его в условиях соединения." не получится, мы не можем делать отбор по этому полю в присоединяемом запросе. Иначе мы получаем на выходе очень большой объем данных, который потом надо сравнивать с вышележащими данными. Т.о. если мы хотим отобрать в подзапросе данные максимально - приходится использовать В. И я уже писал. что для листания журналов эта конструкция дает преимущество, а соединение лучше работает на выборках для запросов.


К списку вопросов
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск




Copyright ©, Все права защищены