Когда условие в срезе последних даже вредит

Публикация № 880836

Администрирование - Оптимизация БД (HighLoad)

SQL запрос срез последних оптимизация

19
Спойлер: оптимизатор MSSQL видит внешние, по отношению к срезу, условия, и строит план с их учетом.

Есть у нас регистр сведений с атрибутами документов. Строк в нем примерно 250 000 000. Да, миллионов. И иногда возникает естественное желание этим регистром воспользоваться. Например, так:

ВЫБРАТЬ
    Документы.ИД    КАК ИД,
    Срез_1.Значение КАК Атрибут1,
    Срез_2.Значение КАК Атрибут2,
    Срез_3.Значение КАК Атрибут3

ПОМЕСТИТЬ
    ДокументыСАтрибутами

ИЗ
    Документы КАК Документы

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,
            Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1)) КАК Срез_1
        ПО Документы.ИД = Срез_1.ИД

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,
            Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2)) КАК Срез_2
        ПО Документы.ИД = Срез_2.ИД

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,
            Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3)) КАК Срез_3
        ПО Документы.ИД = Срез_3.ИД

 

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

Программист идет в профайлер, смотрит время текущей версии запроса и видит это:

23 секунды, немного. Но и немало.

Пишет запрос, который выбирает атрибуты документов во временную таблицу. Добавляет условие по документам внутрь среза, потому что «так правильно» (https://its.1c.ru/db/metod8dev#content:2594:hdoc).

ВЫБРАТЬ
    Срез.Атрибут  КАК Атрибут,
    Срез.ИД       КАК ИД,
    Срез.Значение КАК Значение
ПОМЕСТИТЬ
    ВТЗначАтрибутов
ИЗ
    РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,

            Атрибут В (ВЫБРАТЬ ВТ1.Атрибут ИЗ ВТАтрибуты как ВТ1)

            И (ИД)
            В   (
                ВЫБРАТЬ
                    Документы.ИД
                ИЗ
                    Документы
                )
            ) КАК Срез

И соединяет полученную таблицу с документами

ВЫБРАТЬ
    Документы.ИД    КАК ИД,

    Срез_1.Значение КАК Атрибут1,
    Срез_2.Значение КАК Атрибут2,
    Срез_3.Значение КАК Атрибут3

ПОМЕСТИТЬ
    ДокументыСАтрибутами

ИЗ
    Документы КАК Документы

        ЛЕВОЕ СОЕДИНЕНИЕ ВТЗначАтрибутов КАК Срез_1
        ПО Срез_1.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1)
        И Документы.ИД = Срез_1.ИД

        ЛЕВОЕ СОЕДИНЕНИЕ ВТЗначАтрибутов КАК Срез_2
        ПО Срез_2.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2)
        И Документы.ИД = Срез_2.ИД

        ЛЕВОЕ СОЕДИНЕНИЕ ВТЗначАтрибутов КАК Срез_3
        ПО Срез_3.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3)
        И Документы.ИД = Срез_3.ИД

Здесь небольшое отступление, чтобы восполнить пробелы в структурах данных.

Таблица с атрибутами содержит 3 строки со значениями перечислений.

ВЫБРАТЬ
    ЗНАЧЕНИЕ(Перечисление.Атрибуты.Атрибут1) КАК Атрибут
ПОМЕСТИТЬ ВТАтрибуты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    ЗНАЧЕНИЕ(Перечисление.Атрибуты.Атрибут2)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    ЗНАЧЕНИЕ(Перечисление.Атрибуты.Атрибут2)
ИНДЕКСИРОВАТЬ ПО
    Атрибут

Таблица «Документы» довольно большая, там 300 000 строк.  Колонок в ней немного,  для данного примера это неважно.

ВЫБРАТЬ
    Док.ИД  КАК ИД
ПОМЕСТИТЬ
    Документы
ИЗ
    Документ.Какой_то_документ
ГДЕ
    Какие-то условия

Довольный 1С-ник запускает новый запрос в надежде получить значительный прирост скорости и немигающим взглядом смотрит в монитор:

Хуже в 5 раз. Почему? Да потому что количество атрибутов разное.  Атрибут 1 – 2000, Атрибут 2 – 120 000, Атрибут 3 – 120 000.

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

 

 

После оптимизации получился другой план, в котором сервер посчитал, что будет лучше сначала сформировать срез атрибутов по всем документам, и лишь после этого отобрать нужные документы. Причем, слегка промахнулся в оценке, выбрав в итоге 70 млн строк, вместо 7 млн. Одна из причин замедления - самый правый IndexSeek, выполнившийся 70 млн раз.

 

 

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

Вот условие, о котором идет речь:

Атрибут В (ВЫБРАТЬ ВТ1.Атрибут ИЗ ВТАтрибуты как ВТ1)

А вот новые данные по запросу (на прогретом буфере).

План запроса вернулся к исходному состоянию (сравните с первым скриншотом, там 3 таких блока), что логично, т.к. запрос тоже вернулся к исходному.

Но это только срез последних. А ведь еще есть соединение с таблицей документов. На него тратится 5 секунд, и это нивелирует все усилия по оптимизации данного запроса.

Такой вот, безрезультатный результат.

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

19

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. vosd 3 05.08.18 16:05 Сейчас в теме
В регистре сведений в каком порядке идут измерения?
Дмитрий74Чел; +1 Ответить
2. Поручик 4268 05.08.18 19:25 Сейчас в теме
Посмотрим за дискуссией
3. triviumfan 10 05.08.18 22:30 Сейчас в теме
А существующие индексы нам знать не обязательно?
Дмитрий74Чел; +1 Ответить
24. nicxxx 226 06.08.18 15:12 Сейчас в теме
(3) Индексы стандартные, кластерный по периоду и 2 некластерных по измерениям.
36. starik-2005 1855 07.08.18 11:13 Сейчас в теме
Смысла индексировать таблицу из трех строк, конечно, нет никакого, ибо Лог2(3)=1,7 - выигрыш нулевой. По поводу срезов, то я бы от них отказался. Но тут зависит от того, сколько обычно вариантов атрибутов у документа. Как я понимаю, атрибуты документов могут меняться со временем. Если таких ситуаций -> 0, то смысла в срезе нет, а если их масса, то в идеале вообще производить выборку атрибутов как-то отдельно и потом уже соединять. Главное - минимизировать чтение из таблиц и избежать дополнительных упорядочиваний, добиться использования индексов без скана.
44. nicxxx 226 07.08.18 15:23 Сейчас в теме
(36) Я ведь и попытался сделать выборку атрибутов отдельно. Сами видите к чему это привело.
4. TMV 14 06.08.18 07:09 Сейчас в теме
Без структуры регистра не пойдет
5. nicxxx 226 06.08.18 07:22 Сейчас в теме
Измерения: ИД, строка (20); атрибут (перечисление). Кластерный индекс в формате 8.2, т.е. поле _period слева.
10. kiruha 375 06.08.18 10:09 Сейчас в теме
(5)
Выложите скрин структуры регистра сведений. Ничего не понятно.
Что за секретность ?
11. triviumfan 10 06.08.18 10:24 Сейчас в теме
(5) Типичный регистр для таких целей.
Ну так делать отбор для среза в данном примере бессмысленно, что и показано. Оптимизировать тут нечего, словом совсем.
1й вариант - эталон, отбор по атрибуту, соединение по ИД, этого достаточно.
14. CyberCerber 282 06.08.18 12:53 Сейчас в теме
(5) А что за измерение ИД (строка 20)? Это для связи с доками? Почему не ссылка на доки? И даже для гуида коротковата. Вы сами как-то идентифицируете уникальные доки?
6. AHDP 7 06.08.18 09:19 Сейчас в теме
У документа есть атрибуты с разными датами?

1) Можно пример документа. Как определяются даты реквизитов и как контролируется их "правильная" последовательность?

2) Если отказаться от 1Совского среза последних, хотя бы для двух соедений, заменив их своим вариантом с дополнительным условием на дату документа/результата из первого среза, какие будут результаты?
7. mvk4d 06.08.18 09:51 Сейчас в теме
А почему не индексируете поля временных таблиц, по которым в дальнейшем идет соединение?
15. nicxxx 226 06.08.18 13:18 Сейчас в теме
(7)индексируются. Посмотрите на второй план, там справа есть 2 оператора IndexScan. Т.е. можно было и не индексировать. Эксперты правильно советуют не индексировать, т.к. временная таблица, как правило, меньшего размера и NestedLoops использует ее в качестве внутренней. Это всегда Scan.
8. mvk4d 06.08.18 09:54 Сейчас в теме
https://its.1c.ru/db/metod8dev#content:5842:hdoc:index_condition_corr
Цитата: "Внимание! Не забудьте проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения."
12. palsergeich 06.08.18 11:11 Сейчас в теме
(8) Индекс в общем то не бесплатный. И индексация 70 млн строк может привести к еще более интересным проблемам. Одна из рекомендаций 1с: эксперт - по возможности отказаться от индексации временных таблиц.
13. mvk4d 06.08.18 11:26 Сейчас в теме
(12) Понятно, спасибо. Но меня смущает слово "может". Пока не попробуем, не узнаем точно. Хотелось бы услышать мнение автора на этот счет.
16. nicxxx 226 06.08.18 13:20 Сейчас в теме
(13) Индексируется. Но смысла в этом нет, т.к. в плане видно IndexScan.
21. nicxxx 226 06.08.18 14:37 Сейчас в теме
(8) Совет по индексации временной таблицы даётся в расчете на то, что оптимизатор выберет MergeJoin. Это быстрее, чем NestedLoops, но вторая таблица также должна быть отсортирована.
25. herfis 281 06.08.18 15:25 Сейчас в теме
(21) Моя (и не только) практика показывает, что реальная польза от индексации временных таблиц - скорее исключение, чем правило. И ничего странного в этом нет, если подумать. Чаще всего временные таблицы не настолько велики, чтобы оптимизатор дал применению индексов зеленый свет. А на реально больших временных таблицах скорее всего хэш-таблицу строить будет.
9. caponid 06.08.18 10:06 Сейчас в теме
Мне почему-то режет взгляд первое измерение - строка - как то я не очень перевариваю их в регистрах...
у вас индексы весят как и данные?

почему то это мне напоминает статусы заказов..

А можно подробнее об архитектуре регистра и чем она обусловлена?
22. nicxxx 226 06.08.18 14:59 Сейчас в теме
(9) Строка в измерении это не такое большое зло, как может показаться. Документы приходят из разных внешних систем. Длина ИД от 6 до 10 знаков, в юникоде это до 20 байт. Для сравнения, ссылка занимает 16 байт. В итоге оверхед получается не слишком значительный. Размер индекса нетрудно посчитать, два поля из трёх - примерно 66% от кластерного. Подумываю сделать покрывающий вместо стандартного, чтобы из плана ушла операция KeyLookup.
17. lunjio 60 06.08.18 13:25 Сейчас в теме
Если не заморачиваться как в статье https://infostart.ru/public/551583/, то во-первых, нет необходимости три раза соединяться, можно 1 раз соединиться на таблицу и через выбор когда тогда значения устанавливать, использую группировку соответственно. Во-вторых, в каком порядке идут измерения "ИД" и "Атрибут", мне кажется атрибуты после ИД, почему в условии к виртуальной Условие на ИД не стоит первом и не оптимально было бы оставить только условие на ИД ? а на отбор с атрибутами не заморачиваться и сделать как написал выше.
ВЫБРАТЬ
    Документы.ИД    КАК ИД,
   МАКСИМУМ(ВЫБОР КОГДА ЗначенияАтрибутов.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1) ТОГДА ЗначенияАтрибутов.Значение КОНЕЦ) КАК Атрибут1,
       МАКСИМУМ(ВЫБОР КОГДА ЗначенияАтрибутов.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2) ТОГДА ЗначенияАтрибутов.Значение КОНЕЦ) КАК Атрибут2,
       МАКСИМУМ(ВЫБОР КОГДА ЗначенияАтрибутов.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3) ТОГДА ЗначенияАтрибутов.Значение КОНЕЦ) КАК Атрибут3

ПОМЕСТИТЬ
    ДокументыСАтрибутами

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



Можно попробовать с временной таблицей, следуя рекомендациям ИТС, но так тоже должно быть более оптимально.
26. VmvLer 06.08.18 16:06 Сейчас в теме
(17) из этого примера на громадных базах конечно условие

ИД В (ВЫБРАТЬ Ф.ИД ИЗ Документы КАК Ф) должно оперировать с временной таблицей, а на очень больших базах я его еще и убираю из виртуальной таблицей в секцию ГДЕ.

Условия в виртуальной таблице все-равно в SQL "соскакиваю" в ту же секцию ГДЕ.
28. A_Max 16 06.08.18 18:09 Сейчас в теме
ВЫБРАТЬ
	Документы.ИД,
	АтрибутыДокументов.Атрибут,
	АтрибутыДокументов.Значение,
	АтрибутыДокументов.Период
ИЗ
	Документы КАК Документы
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ АтрибутыДокументов КАК АтрибутыДокументов
			ЛЕВОЕ СОЕДИНЕНИЕ АтрибутыДокументов КАК АтрибутыДокументов_Последний
			ПО АтрибутыДокументов.ИД = АтрибутыДокументов_Последний.ИД
				И АтрибутыДокументов.Атрибут = АтрибутыДокументов_Последний.Атрибут
				И АтрибутыДокументов.Период < АтрибутыДокументов_Последний.Период
		ПО Документы.ИД = АтрибутыДокументов.ИД
ГДЕ
	АтрибутыДокументов.Атрибут В ("Атрибут1", "Атрибут2")
	И АтрибутыДокументов_Последний.ИД ЕСТЬ NULL
Показать


Ну а чтобы развернуть атрибуты в строку варианты можно как предложили (17) проверку в полях + группировка или поместить всё в ВТ и крутить как душе удобно добавив индексы.
kuzyara; YuriFm; +2 Ответить
34. nicxxx 226 07.08.18 10:22 Сейчас в теме
(28) А это вообще что? Срез последних из этого левого соединения не получится. И условие на NULL странное.
Прикрепленные файлы:
40. A_Max 16 07.08.18 14:54 Сейчас в теме
(34) Это именно срез последних и есть и именно за счёт вот этого странного условия на null
Проверьте.

Интересно увидеть план запроса на ваших данных. Генерировать у себя пока не стал.
41. herfis 281 07.08.18 14:59 Сейчас в теме
(40) Остроумная конструкция. Первый раз такую встречаю.
42. A_Max 16 07.08.18 15:04 Сейчас в теме
(41) Когда мне такой вариант получения среза показали я просто был в восторге от гениальной простоты (и до сих пор поражаюсь). А самое главное что можно делать срез по каким угодно критериям и измерениям/реквизитам и вообще не по регистрам, а по чём угодно!
43. herfis 281 07.08.18 15:07 Сейчас в теме
(42) Все способы ручного получения "последних" - универсальны.
Что касается этого, было бы интересно сравнить производительность с классическим двух-этапным вариантом и вариантом с коррелирующим подзапросом.
45. nicxxx 226 07.08.18 15:30 Сейчас в теме
(40) Картинку видите в моем посте? Строки, где есть NULL, будут результатом вашего среза для моего регистра. Как-то не похоже на срез последних. Ошибки нет у вас? Если взять Макс(атрибуты_последний.период) и условие НЕ NULL, то становится больше похоже на правду.
46. A_Max 16 07.08.18 15:35 Сейчас в теме
(45) Очень сложно разбирать рукописную табличку. Ваш вариант можелировал запросом во вложении.
Прикрепленные файлы:
Инфостат_topic197525#message2027832.txt
52. nicxxx 226 08.08.18 15:30 Сейчас в теме
(46) Проверил, действительно работает, как срез последних. Это я забыл, как строится left join:)
54. A_Max 16 08.08.18 16:31 Сейчас в теме
(52) Очень хочется план и результаты измерений для такого варианта увидеть.
Был бы ОЧЕНЬ признателен. Да и другим тоже будет интересно и полезно думаю
55. nicxxx 226 08.08.18 18:07 Сейчас в теме
47. nvv1970 07.08.18 20:46 Сейчас в теме
(40) ну ну.
Логически результат будет верный.

Если в рс счёт идёт на миллионы строк, но такой подход быстро превращается в тыкву, ибо СУБД быстро отхлебнет
89. nicxxx 226 13.08.18 12:48 Сейчас в теме
(40) План запроса пока не могу приложить, но время работы через left join и проверку на null в 10 раз хуже стандартного среза. Логических чтений в 15 раз больше
90. A_Max 16 13.08.18 16:20 Сейчас в теме
(89) Перечитал статью и комментарии.
Атрибут 1 – 2000, Атрибут 2 – 120 000, Атрибут 3 – 120 000
Документов 300 000

Судя по всему получается, что атрибутов других ещё ОЧЕНЬ много так что в первую очередь нужно отсеить их.
Так что нужно развернуть очередность джойнов чтобы документ подсоединялся в конце.
Нужен план и первого варианта и второго в котором с документом соединение третье, а не первое

Оптимельность зависит от количество документов, атрибутов и количество "вариантов" значения одного атрибута.
49. nvv1970 08.08.18 07:57 Сейчас в теме
(17) если убрать условие в параметрах, то план может быть проще, короче, быстрее. Оно ни к чему. Мсскл все равно строит план так, что нужная выборка будет получена "сразу", несмотря на многократную вложенность запросов.
Fox-trot; +1 Ответить
51. nicxxx 226 08.08.18 15:29 Сейчас в теме
(49) Вернусь из отпуска - попробую.
18. nicxxx 226 06.08.18 13:25 Сейчас в теме
Секретности нет. Опубликовал статью и уехал в отпуск без компьютера. Не могу сделать скриншот со структурой регистра. Но состав полей я уже сообщил. Измерения: строка, ссылка на перечисление. Ресурс всего один - ссылка на справочник атрибутов. Выбрана такая структура, потому что нужна историчность атрибутов. Атрибута с типом Дата нет, поэтому альтернативный срез здесь неприменим.
20. kiruha 375 06.08.18 14:02 Сейчас в теме
(18)
оричность атр

Спасибо, не понял сразу что атрибут - ресурс
19. q_i 378 06.08.18 13:31 Сейчас в теме
А если не пихать всё в одну временную таблицу ВТЗначАтрибутов, а сделать 3 временные таблицы:
1. ВТЗначАтрибутов1 = СрезПоследних(&ПериодСреза, Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1) И ИД В ...)
2. ВТЗначАтрибутов2 = СрезПоследних(&ПериодСреза, Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2) И ИД В ...)
3. ВТЗначАтрибутов3 = СрезПоследних(&ПериодСреза, Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3) И ИД В ...)
проиндексировать все их по ИД, а потом все связки со срезами последних из исходного запроса заменить на связки с этими таблицами?
23. nicxxx 226 06.08.18 15:03 Сейчас в теме
(19) Интересная мысль, но может получиться так, то будет три раза использован второй план запроса и время из 110 секунд превратится в 330. Надо проверять.
27. caponid 06.08.18 16:58 Сейчас в теме
Документов порядка 300к, атрибутов 250 000к - в среднем порядок набора атрибутов на документ 1к
А сколько документов участвует в типичном запросе? Все? или есть все таки какие то типичные ограничения?
31. nicxxx 226 07.08.18 09:53 Сейчас в теме
(27) Документов примерно 150 миллионов. Это за полгода. В примере рассмотрен типичный запрос с интервалом в три дня. Обычно на один документ приходится 2-4 атрибута, иногда ни одного.
29. triviumfan 10 06.08.18 20:19 Сейчас в теме
Я уже весь попкорн доел... сколько интересного!
jpg

Кому то структура регистра не нравится, кому-то строковое измерение...кто-то агрегацию использует, кто-то пытается переписать срез, кто-то рекомендует ИНДЕКСАЦИЮ ВТ! LOL
Да нету тут ничего совсем! Оптимизировать НЕ-ЧЕ-ГО!
Берём документы, делаем срез как было показано в первом примере, и никакой временной таблицы не надо - она лишняя.
Для тех кто в танке и им все равно нужна структура этого периодического регистра:
Изменения (по порядку): ИД, Атрибут
Ресурсы: Значение
Это типичный регистр для хранения доп. значений объектов!
30. kolya_tlt 11 07.08.18 09:43 Сейчас в теме
предлагаю оставить этот регистр в покое и использовать для истории (в смысле смотреть когда какой атрибут менялся), ввести второй - с текущими значениями атрибутов, т.е. 3 измерения ИД, Атрибут, Значение. понятное дело надо будет писать код обеспечивающий запись туда данных
32. nicxxx 226 07.08.18 09:59 Сейчас в теме
(30) За вас это уже сделала фирма 1С в платформе 8.3. Посмотрите на ИТС про итоги в регистрах сведений. Минусы этого подхода очевидны - рост времени транзакции на запись и рост размера таблицы. В моем случае прибавится примерно 80% от основной таблицы.
33. kolya_tlt 11 07.08.18 10:21 Сейчас в теме
(32)
За вас это уже сделала фирма 1С в платформе 8.3. Посмотрите на ИТС про итоги в регистрах сведений. Минусы этого подхода очевидны - рост времени транзакции на запись и рост размера таблицы. В моем случае прибавится примерно 80% от основной таблицы.

вы там так сильно заморачиваетесь и экономите место на харде? по-моему это последнее нам чем думают :) рост времени транзакции пользователю будет абсолютно не заметен, а если вы возьмёте этот подход и не станете использовать возможности платформы - тем более.
могу еще подкинуть идею сделать Ключи Атрибутов, сгруппировав записи регистра. т.е. в регистре будет одна запись вместо трёх и соответственно с тремя ресурсами. но тут надо посмотреть на статистику использования ваших атрибутов. в любом случае скорее вы не будете реализовать этот подход из-за большой трудоёмкости данного подхода.
35. nicxxx 226 07.08.18 10:28 Сейчас в теме
Размер таблицы 200 ГБ:) вся база - 1.2 ТБ. На боевом сервере рэйд из SSD на 1.5 ТБ. Мы реально заморачиваемся и экономим место, т.к. диски недешевые. Про составное поле думал, но пока нет четкого представления, как перестроить бизнес-логику.
50. alex-l19041 9 08.08.18 15:14 Сейчас в теме
53. nicxxx 226 08.08.18 15:33 Сейчас в теме
(50) Ничего крутого. Backup - 50 минут, Restore - 40. Опыта конечно добавляет. Начинаешь думать о последствиях изменения любого флажка в реквизитах регистров. Становится единственным возможным способом включить индексирование на колонке - создать новый индекс в SSMS.
58. kolya_tlt 11 09.08.18 08:34 Сейчас в теме
(35) слишком большая таблица, задумайтесь над тем чтобы её начать пилить. возможно некоторые атрибуты можно перенести в табличные части документа. как например был единый огромный регистр Значения свойств объектов, который был "размазан" по табличным частям объектов
59. nicxxx 226 09.08.18 09:56 Сейчас в теме
(58) Ее используют 2 вида документов, поэтому нет смысла делить. Вместо одной на 250 млн строк получим две, на 100 и 150 млн.
60. kolya_tlt 11 09.08.18 10:18 Сейчас в теме
(59) да, но типы таблиц то разные. там РС, а получите более простую. попробуйте перенести и выполнить замеры на тесте.
63. nicxxx 226 09.08.18 12:46 Сейчас в теме
(60) Это невозможно по архитектурным причинам. Документы тоже в регистре сведений:) В двух регистрах, если точнее. Почему? Так увидел архитектор(не я). Они затем агрегируются, чтобы записать не миллион проводок в хозрасчетный, а десять.
37. glog 07.08.18 11:30 Сейчас в теме
Я стараюсь такие большие таблицы выносить в отдельные базы и подключать их к 1С через внешние источники данных. Так гораздо больше возможностей по оптимизации (партицирование, сложные индексы и т.д.). Такой подход себя оправдывает не во всех случаях, конечно.
38. nicxxx 226 07.08.18 12:46 Сейчас в теме
(37) Невозможно хранить ссылки:( Также нельзя инсертить и джойнить с таблицами внутри базы.
39. Fox-trot 99 07.08.18 14:04 Сейчас в теме
все же не понятно почему вредит именно "условие срезе последних"
48. nvv1970 07.08.18 22:20 Сейчас в теме
Что-то не нашел ни решений, ни выводов... ни полных планов запроса (можно же сохранять по человечески, а не картинками).
Ни структуры регистра... ИД надеюсь у вас первым стоит? Иначе быть беде...

Интересно, какое время занимает скан индекса по измерениям?

Не нашел теста такого варианта (не обязательно тестить 3 среза сразу):
    Документы КАК Документы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,) КАК Срез_1
        ПО Документы.ИД = Срез_1.ИД И Срез_1.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1)

Из практики - он должен быть самым производительным.

Плохо или хорошо условие в срезе - зависит от многих факторов: от самих условий, от типа соединения среза с внешней таблицей...
Встречаю часто, что 1С любит в условия запросов (при условии в параметрах) добавлять EXISTS (SEL ECT 1 FR OM ....) - вот вам гарантированные NLoopsы и миллиарды/биллионы чтений.

Не нужно морочиться однозначными выводами и рекомендациями (никому не советовал бы их делать), потому что существуют разные типы и версии субд с кучей вариантов настроек, tf, влияющих на оптимизацию и компиляцию планов.
Да, есть советы и примеры, когда что-то явно тупит, но лучше иметь свой опыт. На чужом далеко не уедешь (
Да, срез может сильно удивлять и доставлять проблемы (особенно с периодичностью по регистратору, или на PG), как вы ни пытаетесь лечь под индексы.
56. triviumfan 10 08.08.18 19:10 Сейчас в теме
(48)
Из практики - он должен быть самым производительным.

Т.е. ты сначала выбираешь все записи регистра с периодом меньше указанной даты, группируешь их, вычисляя последний период, соединяешь с таблицей для получения значений ресурсов, и только после этого соединяешь с основной таблицей? И это работает быстрее, чем сначала отфильтровать?
Кажется, надо вызывать психиатора.
57. nvv1970 08.08.18 22:54 Сейчас в теме
(56) ТЫ типичный 1с-ник?
Откройте для себя планы запросов, уровни оптимизации запросов, логические и физические операторы, построение планов выполнения (читай - "порядка физических манипуляций с данными") и прочие области психиатрии.
Но если вдруг вы со всем этим знакомы, то откуда такие выпады?

Представьте три уровня вложенных запросов с условием только на внешнем уровне:
Выбрать Поле Из (Выбрать Поле Из (Выбрать Поле Из Таблица)) Где Условие
Вопрос: будет ли СУБД сканировать (получать по вашему) всю таблицу на вложенных уровнях?
Ответ: конечно же нет.
СУБД пошлет и 1с, и программиста в... и применит это условие на нижнем уровне.
Вот такие разрывы шаблонов...

Так и в моем примере... Наложение условий соединения будет выполненно в первую очередь, т.е. "на нижнем уровне".
61. triviumfan 10 09.08.18 11:35 Сейчас в теме
(57)
Так и в моем примере... Наложение условий соединения будет выполнено в первую очередь, т.е. "на нижнем уровне".

Только если СУБД достаточно интеллектуальна. Мы же, свою очередь, должны помочь ей это сделать, чтобы СУБД выбрала оптимальный план.
Но судя по вашему запросу, вы лишь затрудняете это.
62. Fox-trot 99 09.08.18 11:45 Сейчас в теме
(61)
Но судя по вашему запросу, вы лишь затрудняете это.
спорное утверждение
68. triviumfan 10 09.08.18 18:22 Сейчас в теме
70. nvv1970 10.08.18 00:18 Сейчас в теме
(68) только не говорите такое Морозову, Богачеву... Они на экзамене или тренинге как раз пример такой приводят, что не все золото, что стандарт 1с. И разжевывают причину этого...
Не бросайтесь злыми словами, а потрудитесь сравнить планы запросов и сами запросы в СУБД... Там все предельно просто и понятно. Причины на лицо.

ПС: "достаточно интеллектуальна" - смешно... Вы окончательно меня убедили, что не владеете механизмами MSSQL или хотя бы PGSQL.
73. triviumfan 10 10.08.18 00:35 Сейчас в теме
(70) А вы не убедили меня, настаивая на том, что оптимизатор - "бог", он все сделает как надо.
У меня есть платформа 8.2, sql2014, регистр с ценами на 70кк записей. Проведу тесты с очисткой статистики и кеша, а также поковыряюсь в настройках субд. Но я все же найду контрпример, чтобы заткнуть сего умника.
75. nvv1970 10.08.18 08:07 Сейчас в теме
(73) ой все...
Я не говорил, что запросы работают всегда одинаково. Естественно планы компилируются по разному в зависимости от условий, на процесс можно влиять.
Всегда можно сделать плохо. Это не нужно доказывать. Дурное дело не хитрое.
А вот хорошо сделать не всегда получается.

В 99.999% случаев (если специально не мешать) СУБД скомпилирует хороший быстрый план.

Короче, спите дальше в своем неведении. Хотите показать себя грамотным человеком - покажите положительный, а не отрицательный результат.
80. triviumfan 10 10.08.18 09:53 Сейчас в теме
(75) Ясно, "слился". Мой "агр" был лишь на
Из практики - он должен быть самым производительным.

Он не может быть производительнее по определению, а лишь идентичен, либо проигрывает. Вот и всё.
64. vasilev2015 1002 09.08.18 17:32 Сейчас в теме
Зря Вы статью https://its.1c.ru/db/metod8dev#content:2594:hdoc цитировали. Там речь идет о виртуальной таблице срез последних с _отсутствующей_ датой среза. В платформе 8.3 такой запрос может обращаться к таблице итогов, использование параметров внутри полностью оправданно. У вас же дата среза используется.
71. nvv1970 10.08.18 00:21 Сейчас в теме
(64) предполагается, что итоги (срез) не включены (это слишком просто, чтобы вообще обсуждать). Речь конкретно про проблемы соединения с обычным срезом.
65. vasilev2015 1002 09.08.18 17:41 Сейчас в теме
Правда заключается в том, что виртуальные таблицы не все одинаково полезные. Например, таблица остатков полезна безусловно, а срез последних с датой или обороты с детализацией до регистратора - бесполезные, лучше обращаться к регистру напрямую.
66. Silenser 504 09.08.18 17:44 Сейчас в теме
Без доступа к базе конечно сложно говорить, но как вариант можно посмотреть фрагментацию индексов и статистику. Возможно их нужно перестроить и обновить, по дефолту SQL обновляет статистику не по всей таблице, а по ее части, что в некоторых случаях может иметь негативный эффект. Обычно указание отборов (не сложных) в параметрах виртуальной таблицы все же приводит к увеличению скорости запроса.
67. vasilev2015 1002 09.08.18 17:52 Сейчас в теме
Готов на спор повозиться с вашим регистром и улучшить время запроса с 23 до 10 секунд ))
edkuznetsov; +1 Ответить
69. nicxxx 226 09.08.18 18:30 Сейчас в теме
(67)Можно сгенерировать аналогичный объем строк и попробовать ускорить. Вся информация есть в топике и комментариях. Или могу продублировать сводно.
77. vasilev2015 1002 10.08.18 08:58 Сейчас в теме
(69) Поймите, когда я сгенерирую аналогичный объем и поспорю сам с собой, то выиграю спор самостоятельно. Я ожидал с Вашей стороны нормальное предложение спора.
78. nicxxx 226 10.08.18 09:34 Сейчас в теме
(77) Не понимаю смысла этого спора. Если у вас есть желание заняться оптимизацией, то все в ваших руках. Если получится сократить время до 10 секунд - ваше кунг-фу сильнее:)
79. vasilev2015 1002 10.08.18 09:49 Сейчас в теме
(78) я занимаюсь оптимизацией либо за деньги либо за азарт. Вам ваш запрос безразличен или у Вас есть интерес его ускорить ?
81. nicxxx 226 10.08.18 10:33 Сейчас в теме
(79) Я помощи не просил. Ни за деньги, ни за азарт. Оптимизировать, отказавшись от среза я и сам могу. У меня уже есть процедура, которая по расписанию удаляет старые версии атрибутов. Ее лишь нужно подключить к продакшн-базе.
82. vasilev2015 1002 10.08.18 10:42 Сейчас в теме
(81) да, попробуйте обращаться напрямую к регистру АтрибутыДокументов, группировать по полям Атрибут, ИД, максимум по полю Дата, условие Где Дата =< &ПериодСреза. Это должно работать быстрее чем срез последних три раза. Мне интересно, что получится. Напишите.
87. Fox-trot 99 10.08.18 17:05 Сейчас в теме
(81)
У меня уже есть процедура, которая по расписанию удаляет старые версии атрибутов. Ее лишь нужно подключить к продакшн-базе.
тады проще ваще удалить дату
72. nvv1970 10.08.18 00:29 Сейчас в теме
(67) 10 сек - это тоже не решение. Вне зависимости от объема таблицы поиск по индексу и отсутствие миллиардных циклов - это всегда доли секунды. Много миллиардные таблицы тоже возвращают данные за 0.0ххх сек.
Здесь или что-то вроде 0,5 или долгая дичь...
SlavaKron; +1 Ответить
76. vasilev2015 1002 10.08.18 08:56 Сейчас в теме
(72) Десять секунд - это время, полученное простым расчетом: вместо трех соединений использовать одно в три раза быстрее. И я тоже думаю, это не предел. Идея простая: использовать обращение к самой таблице регистра без среза.
83. nvv1970 10.08.18 14:45 Сейчас в теме
(76) А у меня идея другая: добиться использования Merge, вместо медленного HashMatch. Без tf, гайдов и других "секретных" приемов (а-ля, ща покручу скуль)))).
Идея простая: использовать обращение к самой таблице регистра без среза.

Не туда смотрите. Какая разница? В СУБД в итоге вы сделаете один и тот же запрос, тот же плохой план, вид сбоку. Они только в 1с будут внешне отличаться.
84. vasilev2015 1002 10.08.18 15:20 Сейчас в теме
(83) я указал в (82), какой запрос нужно сделать. Надеюсь, автор попробует.
85. nicxxx 226 10.08.18 16:41 Сейчас в теме
(84) Обязательно попробую, сразу после отпуска, в понедельник.
86. nicxxx 226 10.08.18 16:44 Сейчас в теме
(83)Чтобы получить Merge сначала надо сделать Sort, а это недешевая операция.
88. nvv1970 11.08.18 11:49 Сейчас в теме
(86) все верно, но все относительно... Например, относительно хешджоина...)
Пробуйте и расскажите нам.
74. SlavaKron 10.08.18 06:45 Сейчас в теме
Пока нет возможности протестировать на сервере SQL. Шаблон для генерации данных в прикреплении.
Прикрепленные файлы:
1Cv8.dt
91. nvv1970 15.08.18 08:13 Сейчас в теме
Вы забили на тему? Или не отошли ещё от отпуска?)))
92. nicxxx 226 15.08.18 10:48 Сейчас в теме
Я погряз в других задачах, которые тоже надо выполнять. Думаю, на выходных займусь этой проблемой.
93. w.r. 213 26.04.19 19:54 Сейчас в теме
А почему нельзя написать примерно так:

ВЫБРАТЬ
    Документы.ИД    КАК ИД,
    ВЫБОР 
       КОГДА Срез.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1) ТОГДА 
     Срез.Значение 
    КОНЕЦ КАК Атрибут1,
    ВЫБОР 
       КОГДА Срез.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2) ТОГДА 
      Срез.Значение 
    КОНЕЦ КАК Атрибут2,
    ВЫБОР 
       КОГДА Срез.Атрибут = ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3) ТОГДА
      Срез.Значение 
    КОНЕЦ КАК Атрибут3

ПОМЕСТИТЬ
    ДокументыСАтрибутами

ИЗ
    Документы КАК Документы

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АтрибутыДокументов.СрезПоследних(&ПериодСреза,
            Атрибут В (ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут1),
ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут2),
ЗНАЧЕНИЕ(Перечисление.АтрибутыДокументов.Атрибут3)
))КАК Срез
        ПО Документы.ИД = Срез.ИД
Показать


И не нужно огород городить с 3мя соединениями к одному регистру
94. nicxxx 226 26.04.19 21:05 Сейчас в теме
Огород с тремя соединениями работает быстрее за счет технологии, называемой PREDICATE PUSHDOWN. Проверено на практике, я пробовал выполнить запрос, аналогичный вашему. Когда MSSQL джойнит виртуальную таблицу, то оптимизатор применяет фильтры, наложенные на главную таблицу. Смотрим на запрос ниже. Оптимизатор применит условие Doc.Ref IN() не только к таблице _Document123 , но и к таблице движений регистра накопления _AccumRg567.
SELECT
Doc.Ref,
RegVT.Amount
FROM
_Document123 AS Doc
LEFT JOIN (SELECT
Reg.Recorder,
Reg.Amount AS Amount
FROM
_AccumRg567 AS Reg
GROUP BY
Reg.Recorder) AS RegVT
ON RegVT.Recorder = Doc.Ref
WHERE
Doc.Ref IN ( здесь какое-то условие отбора, например по количеству документов, помещающихся на одну страницу динамического списка).

Почему оптимизатор так сделает? Потому что незачем читать лишние данные, которые все равно окажутся не востребованы в джойне, т.к. это LEFT JOIN.
Кстати, Postgres так не умеет. Недавно столкнулся с проблемой тормозов в РЛС при пролистывании журнала. В фактическом плане запроса стало понятно, что Postgres читает 200 000 строк из таблицы регистра, а MSSQL на таком же запросе - всего 100. Время соответственно 10 сек на отображение 35 строк на PG и 1 сек на MSSQL. Вы все еще за экономию на лицензиях?
sergathome; acanta; +2 Ответить
95. sergathome 26.04.19 21:27 Сейчас в теме
(94) это запредел для многих местных "гуру"."". ага. :) Однако цена вопроса тоже становится слишком велика. Просто цена.MSSQL Standard (16 cores max)... Мильоны, Карл !!!
99. nicxxx 226 26.04.19 21:45 Сейчас в теме
(95) Standard - до 24 ядер
(https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing)
лицензия на 2 ядра - 187000 руб
(https://www.softmagazin.ru/soft/produkty_microsoft/sql_server/7NQ-01158)
на 24 ядра - 4.5 млн руб.
прошу прощения, 2,25 млн.
Да, недешево.
100. sergathome 26.04.19 21:47 Сейчас в теме
(99) я,я, натюрлих. заграница тоже не дремлет. выбор становится всё сложнее...

зы по 14-му - 16. Ну не суть, конечно.
96. acanta 56 26.04.19 21:32 Сейчас в теме
(94)жалобу на сборку постгрес от 1с на хотлайн 1с отправляли?
97. nicxxx 226 26.04.19 21:34 Сейчас в теме
(96) Да. Но дело не в 1С, а в оптимизаторе Postgres.
Оставьте свое сообщение