Оптимизация динамического списка

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

На управляемых формах 1С есть очень удобный и гибкий объект - ДинамическийСписок.
Удобный - создавать и поддерживать легко.
Гибкий - легко настраивается в пользовательском режиме.
Но есть у него большой недостаток - если его неправильно сконфигурировать, он будет работать очень медленно. Ниже несколько способов оптимизации динамических списков.

База для оптимизации

Для тестовых данных использовалась самописная база с реальными данными большого справочника клиентов, строк – около миллиона, колонок – 26. В динамическом списке к справочнику присоединен регистр состояний клиентов (срез последних). Размер справочника такой, что не разворачивается в файловом варианте.

Для замера производительности использовался SQL Server Profiler, который показывает время выполнения запроса без дополнительных затрат на интерфейс. Конкретные цифры в статье приводить не буду, так как они индивидуальны для каждой базы. 

 

Способы оптимизации:

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

Первым по важности для обеспечения быстродействия динамического списка является оптимизация запроса. По оптимизации запросов написано много статей, но основные принципы перечислены ниже:

  • При соединении таблиц использовать индексы
  • Не использовать вложенные запросы
  • Избегать в запросе большого количества таблиц
  • По возможности избегать условий «ИЛИ», «НЕ», «ПОДСТРОКА», «МЕСЯЦ» «ВЫБОР КОГДА» и т.д
  • Стараться не использовать динамические(вычисляемые) поля


2. Флаг "Динамическое считывание данных"

В свойствах запроса динамического списка (если он «Произвольный» и установлена основная таблица) доступен флаг «Динамическое считывание данных».

Если флаг установлен, то запрос забирает из БД маленькие порции данных (на текущей версии 1С – по 45). Прокрутка списка в этом случае может подвисать.

Если флаг не установлен, то порции больше (на текущей версии 1С – по 1000). Прокрутка в этом случае комфортнее, но на выполнение запроса требуется больше времени.

Общие рекомендации по установке флага «Динамическое считывание данных»:

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

 3. Группировки в динамическом списке.

Здесь все просто – использование группировок может очень значительно замедлять список. Рекомендую просто отказаться от них и не включать их в пользовательские настройки.

 4. Сортировка.

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

  • Сортировать список желательно по проиндексированным реквизитам
  • Не сортировать список по вычисляемым полям

 5. Отборы, поиск

Общие рекомендации:

  • Поля, по которым чаще всего устанавливаются отборы должны быть проиндексированы.
  • Если при этом сортировка списка отключена (т.е. сортировка производится по основному представлению - код или наименование основной таблицы динамического списка), а это почти всегда, то индексировать нужно «с доп. упорядочиванием».
  • Если на список накладывается несколько отборов, очень важен их порядок (на текущей версии платформы 1С). Правильный порядок отборов:
    1. Проиндексированные поля
    2. Неиндексированные поля
    3. Вычисляемые поля
  • Нарушение порядка может значительно замедлять список.
  • Контролировать порядок пользовательских отборов сложно, но можно, например, проиндексированные поля «вытащить» в отборы в конфигураторе, и они будут накладываться первыми.

  • Часто используемые сложные отборы (например, по вычисляемым полям или со сложными условиями) можно устанавливать в тексте запроса и оптимизировать. Для этого, например, можно использовать несколько форм динамического списка или несколько «вариантов» на одной форме.

 6. В пользовательском режиме

  • Лучше убирать ненужные к отображению колонки – это позволит облегчить запрос. Прирост к производительности правда очень незначительный.
  • Не использовать вывод дополнительных полей/характеристик через точку. Снижение производительности может быть очень высоким.

 7. Регламентные операции на MS SQL Server:

Обновление статистик, обслуживание индексов.

 8. Используйте SQL Server Profiler

Для отлавливания долгих запросов, анализа плана запроса и принятия дополнительных решений по оптимизации списка.

 

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

При таком подходе удалось оптимизировать динамический список тестовой базы с миллионным справочником, обновление списка с различными отборами не превышает 2-3 секунд.

См. также

Добавить вознаграждение
Комментарии
1. Виталий Кривенко (MoGar) 20.01.16 10:35 Сейчас в теме
"Если на список накладывается несколько отборов, очень важен их порядок (на текущей версии платформы 1С). Правильный порядок отборов:"
а проверяли план запроса в зависимости от порядка условий? Ведь при построении плана запроса сервер SQL сам может поменять порядок отборов, если посчитает нужным (в зависимости от тех же индексов и др. условий).
2. Сергей Смирнов (smirnov0ser) 64 20.01.16 13:05 Сейчас в теме
(1) MoGar,
Проверял, сейчас повторно запустил проверку с 2 отборами двумя вариантами:
1) Отбор по пустому менеджеру; отбор по вычисляемому полю - 458мс
2) Отбор по вычисляемому полю; отбор по пустому менеджеру - 2278мс

В тексте выполненных sql запросов различия в порядке условий сохранились, планы запросов разные.

К сожалению, во втором случае ms sql не поменял порядок условий, а также выбрал неоптимальный план запроса. Повлиять на это со стороны 1С пока не представляется возможным, кроме как накладывать отборы в "правильном" порядке.

Проверено также на типовой БП3.0, в запрос полного списка проводок добавлено "тяжелое" вычисляемое поле:
(ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.Регистратор.Номер КАК СТРОКА(10))) + "_ЛЯЛЯ" КАК ЛЯЛЯ

На список накладывал 2 вида отборов:
1) СчетДт = 60.01; ЛЯЛЯ содержит "ЛЯ",
2) ЛЯЛЯ содержит "ЛЯ"; СчетДт = 60.01,

По сумме времени запросов ( по данным profiler'а), первый вариант отработал в 3,6 раза быстрее, чем второй.
Если есть возможность, проверьте у себя, было бы интересно увидеть результаты на другой машине.
3. Сан Саныч (herfis) 54 20.01.16 13:27 Сейчас в теме
По поводу флага "Динамическое считывание данных". Очень для меня интересный момент.
Исходя из ваших рекомендаций, я понял что почти всегда имеет смысл его отключать, кроме случая тяжелых запросов и малой вероятности необходимости скролла. Т.е. можно все бросать и бежать снимать галку во всех формах простых журналов документов? Ведь скролл будет комфортнее работать и реже подтормаживать. Почему тогда по дефолту галка всегда стоит? Может, не все так гладко? Я понимаю, что минусом будет увеличение вероятности отображения неактуальной информации. Других минусов нет?
4. Сан Саныч (herfis) 54 20.01.16 13:57 Сейчас в теме
Поставил эксперимент, получил странный результат.
При снятии галки динамического считывания, подтормаживания при скроллах остались в рамках тех же порций, что и были. Т.е. чуть больше размера экрана.
Но при этом на динамическом списке с произвольным запросом с соединением среза последних по регистру сведений подтормаживание резко увеличилось. Т.е. похоже на то, что из БД выбирается в самом деле увеличенная порция данных, но выбирается она с той же самой периодичностью! Т.е. никакого профита - одни убытки. Возможно, дело в используемой СУБД.
Основные таблицы везде прописаны.
сервер 8.3.6.2390 на x64 Ubuntu 14.04, сервер PostgreSQL 9.2.4 на другом x64 Ubuntu 14.04
В общем, осторожней с этой галкой.
5. Liudmila Kishko (lkishko) 20.01.16 14:36 Сейчас в теме
Поля, по которым чаще всего устанавливаются отборы должны быть проиндексированы

имхо, нужно тоже соблюдать баланс индексов. когда их много, то таблицам это не очень нравится... как обычно, используем здравый смысл ))
smirnov0ser; +1 Ответить
6. Сергей Смирнов (smirnov0ser) 64 20.01.16 15:05 Сейчас в теме
(3) herfis,
Видимо типовые решения нацелены на большой объем документов, когда небольшие подвисание прокрутки предпочтительней зависания списка при обновлении или наложении отбора/поиска.

(4) herfis,
Тут ситуация чуть более сложная. При снятом флаге выборка из SQL Server'a идет по 1000 шт. Но на клиент сервером 1С они все равно передаются частями. То есть:
1) Флаг "Динамическое считывание данных" снят.
При первом открытии: запрос к СУБД на 1000 строк (подвисание)
При прокрутке списка : запроса к СУБД нет, но запрос, видимо, к серверу 1С (небольшое подвисание, что очень огорчает)
При долгой прокрутке: повторный запрос к СУБД на 1000 строк (подвисание)
2)Флаг "Динамическое считывание данных" установлен.
При первом открытии: запрос к СУБД на 45 строк (небольшое подвисание)
При прокрутке списка : повторный запрос к СУБД на 45 строк (небольшое подвисание)
При долгой прокрутке: повторный запрос к СУБД на 45 строк (небольшое подвисание)

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

Поясню что я имел в виду под процентом записей. Если из миллиона в список попадает 900 строк, то при динамическом считывании запрос быстро получит первые 45 подходящих строк и прервет запрос. Если динамическое считывание отключено, то запрос обработает все 900 строк (вероятно, в 20 раз дольше). А при обновлении эту долгую операцию повторит.
Если из миллиона строк в список попадает 100000, то выборка 45 строк и 1000 строк не будет сильно отличаться по скорости. Но прокрутка удобнее будет при выборке большой порции.


Например, на моих данных есть 2 вида списка.
Один показывает полный список и отборы там ставятся, в основном, по индексированным полям. Запрос выполняется быстро, а в списке обычно большое количество записей, часто используется прокрутка. Здесь у меня снят флаг "Динамическое считывание данных", так как в моем случае это облегчает прокрутку.
Второй показывает список со сложным отбором. Показывает около 100-500 строк из миллиона. Здесь у меня установлен флаг "Динамическое считывание данных", потому что это ускоряет открытие формы, обновление списка и наложение отборов. Но прокрутка в этой форме с подвисаниями.

По моему мнению, в общем случае лучше на больших таблицах флаг устанавливать.

Чуть позже дополню статью.
7. Сан Саныч (herfis) 54 20.01.16 15:44 Сейчас в теме
(6) smirnov0ser, Еще раз.
При снятии галки на "тяжелом" динамическом списке наблюдалось резко увеличившееся подвисание при прокрутке. Не при длительной прокрутке, а при обычной. Если бы дело было только в обращении на сервер приложений за порцией уже считанных данных, такого бы не наблюдалось. Время "подвисания" должно было либо остаться таким же, либо уменьшиться. Так что либо при обычной прокрутке каждый раз фигачится запрос на 1000 элементов, либо пропала какая-то другая оптимизация.
8. Сергей Смирнов (smirnov0ser) 64 20.01.16 16:28 Сейчас в теме
(7) herfis,
Странно, что подвисания увеличиваются, у меня обратная ситуация.

http://its.1c.ru/db/v8std/content/2149184381/hdoc/_top/%E4%E8%ED%E0%EC%E8%­F7%E5%F1%EA%EE%E5%20%F1%F7%E8%F2%FB%E2%E0%ED%E8%E5%20%E4%E0%­ED%ED%FB%F5
Здесь подтверждается, что при запросе 1000 строк, данные запоминаются в буфере на сервере, а потом, передаются на клиент частями. Видимо у Вас этот буфер работает медленнее, чем повторный запрос к БД.

На своей базе проверил - при отключенном динамическом считывании, при небольшой прокрутке запрос к БД не происходит. И прокрутка быстрее, чем при динамическом считывании.

9. Сан Саныч (herfis) 54 20.01.16 16:35 Сейчас в теме
(8) smirnov0ser, Я верю, что у вас так. Это, по крайней мере, логично.
Но в моей конфигурации, которую я описал выше, все не так радужно.
Вполне может быть что это косяк в линуксовой версии сервера приложений.
10. Константин Рыбаков (pyrkin_vanya) 265 29.01.17 23:11 Сейчас в теме
А я вот не совсем понял, что значит эта фраза "При соединении таблиц использовать индексы"? Использовать временные таблицы в запросе динамического списка нельзя. Про какие индексы вы говорите? Поясните пожалуйста.
11. Сан Саныч (herfis) 54 30.01.17 11:34 Сейчас в теме
(10) Речь о так называемом "попадании в индекс". Т.е. стараться в запросах эффективно использовать существующие индексы физических таблиц, а при острой необходимости - создавать недостающие. Самое банальное для примера - если в запросе динамического списка используется соединение с очень большим справочником по доп-полю с высокой селективностью, то имеет смысл это доп-поле проиндексировать.
smirnov0ser; +1 Ответить 1
12. Константин Рыбаков (pyrkin_vanya) 265 30.01.17 15:51 Сейчас в теме
13. Сергей Смирнов (smirnov0ser) 64 30.01.17 16:02 Сейчас в теме
(12) Индексы физических таблиц. Например, в динамическим списке связывается физическая таблица справочника "Товары" и РС "Состояние контрагентов" по полю "Контрагент". Так вот желательно, чтобы и в справочнике - реквизит "Контрагент" был проиндексирован (это свойство реквизита), и в регистре - измерение "Контрагент" было проиндексировано (свойство измерения, имеет смысл если оно не первое измерение).
14. Константин Рыбаков (pyrkin_vanya) 265 30.01.17 16:48 Сейчас в теме
А, менять индексирование в реквизите. Это не подходит. Без изменения конфы надо.
15. Сергей Смирнов (smirnov0ser) 64 31.01.17 11:34 Сейчас в теме
(14) У вас какой-то типовой динамический список подтормаживает?
Без изменения конфигурации проиндексировать не получится (средствами sql можно, но нежелательно).
16. Константин Рыбаков (pyrkin_vanya) 265 31.01.17 22:14 Сейчас в теме
Проблема немного другая. Есть УТ 11.3. В ней есть форма списка справочника номенклатура. Вот запрос ,что там используется.
ВЫБРАТЬ
    СпрНоменклатура.Ссылка КАК Ссылка,
    СпрНоменклатура.Код КАК Код,
    СпрНоменклатура.Наименование КАК Наименование,
    СпрНоменклатура.Артикул КАК Артикул,
    СпрНоменклатура.ВидНоменклатуры КАК ВидНоменклатуры,
    СпрНоменклатура.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    СпрНоменклатура.СтавкаНДС КАК СтавкаНДС,
    СпрНоменклатура.Родитель,
    ВЫБОР
        КОГДА СпрНоменклатура.ЕстьТоварыДругогоКачества
            ТОГДА 4 + ВЫБОР
                    КОГДА СпрНоменклатура.ПометкаУдаления
                        ТОГДА 1
                    ИНАЧЕ 0
                КОНЕЦ + ВЫБОР
                    КОГДА СпрНоменклатура.ИспользованиеХарактеристик = ЗНАЧЕНИЕ(Перечисление.ВариантыИспользованияХарактеристикНоменклатуры.НеИспользовать)
                        ТОГДА 0
                    ИНАЧЕ 2
                КОНЕЦ
        ИНАЧЕ ВЫБОР
                КОГДА СпрНоменклатура.ПометкаУдаления
                    ТОГДА 1
                ИНАЧЕ 0
            КОНЕЦ + ВЫБОР
                КОГДА СпрНоменклатура.ИспользованиеХарактеристик = ЗНАЧЕНИЕ(Перечисление.ВариантыИспользованияХарактеристикНоменклатуры.НеИспользовать)
                    ТОГДА 0
                ИНАЧЕ 2
            КОНЕЦ
    КОНЕЦ КАК ИндексКартинки,
    ВЫБОР
        КОГДА СпрНоменклатура.aspect_АдресКартинки = ""
            ТОГДА ЛОЖЬ
        ИНАЧЕ ИСТИНА
    КОНЕЦ КАК Картинка,
    СпрНоменклатура.aspect_СредняяПродажа КАК СредняяПродажаВМесяц,
    ТоварыНаСкладахОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
    ТоварыНаСкладахОстатки_СкладМастерок.ВНаличииОстаток КАК ВНаличииОстаток_СкладМастерок,
    ТоварыНаСкладахОстатки_СкладМагазин.ВНаличииОстаток КАК ВНаличииОстаток_СкладМагазин,
    СпрНоменклатура.aspect_ЗакупочнаяЦена КАК ЗакупочнаяЦена,
    СпрНоменклатура.aspect_РозничнаяЦена КАК РозничнаяЦена,
    СпрНоменклатура.aspect_ДатаПоследнегоИзменения КАК ДатаПоследнегоИзменения,
    СпрНоменклатура.aspect_ДатаПоследнейПродажи КАК ДатаПоследнейПродажи
ИЗ
    Справочник.Номенклатура КАК СпрНоменклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, ) КАК ТоварыНаСкладахОстатки
        ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, Склад = &СкладМастерок) КАК ТоварыНаСкладахОстатки_СкладМастерок
        ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки_СкладМастерок.Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, Склад = &СкладМагазин) КАК ТоварыНаСкладахОстатки_СкладМагазин
        ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки_СкладМагазин.Номенклатура
ГДЕ
	НЕ СпрНоменклатура.ЭтоГруппа
{ГДЕ
    (СпрНоменклатура.Ссылка В
            (ВЫБРАТЬ
                Сегменты.Номенклатура
            ИЗ
                РегистрСведений.НоменклатураСегмента КАК Сегменты
            ГДЕ
                Сегменты.Сегмент = &СегментНоменклатуры))}
...Показать Скрыть


ПРоблема в том, что иногда открываешь форму и она открывается через 2-3 секунды, а иногда через 10 минут. Вот и не понимаю почему. Может подсжадите?
17. Сергей Смирнов (smirnov0ser) 64 02.02.17 11:14 Сейчас в теме
В первую очередь, проверьте "вытащенные через точку" поля в пользовательском режиме. Их отключение может увеличить производительность.

База на SQL? Попробуйте пересчитать статистику и перестроить индексы, часто именно это является причиной нестабильной работы.
Если ничего не помогло - нужен детальный анализ через профайлер.

Ну и как вариант, чтобы убедиться, что причиной долгого открытия формы является именно динамический список, попробуйте отключить его.


18. Константин Рыбаков (pyrkin_vanya) 265 02.02.17 15:02 Сейчас в теме
Поля через точку примитивного типа. База файловая. Без динамического списка все летает.