Долгий запрос по журналу документов.

Вот такой план запроса:
https://disk.yandex.ru/i/N2MUCRsS0gZzTw

Текст тут:
https://disk.yandex.ru/d/yhYQKslhFK7rRQ

Выполняется почти минуту, документов в журнале более 10 млн., отбор по двум проиндексированным полям.
Можно ускорить?
Статистику обновлял (но без fullscan).

Сам запрос формируется платформой при листании в списке журнала (ОФ).

Текста нет

скорее всего что-то есть в привыводе

(2) Текст самого запроса?
Думал, плана достаточно.
Вот:

SELECT TOP 44
T1._Date_Time,
T1._Number,
T1._Fld7839RRef,
T1._Fld7849RRef,
T1._Fld10451,
T1._Fld17794,
T1._Fld7848RRef,
T1._Fld17793,
T1._Fld7841,
T1._Fld7850RRef,
T1._Fld7840RRef,
T1._Fld17792,
T1._Fld10524,
T1._DocumentTRef,
T1._DocumentRRef,
T1._Marked,
T1._Posted,
T2._Description,
T3._Description,
T4._Description,
T5._Description
FROM _DocumentJournal7838 T1 WITH(NOLOCK)
LEFT OUTER JOIN _Reference31 T2 WITH(NOLOCK)
ON T1._Fld7839RRef = T2._IDRRef
LEFT OUTER JOIN _Reference59 T3 WITH(NOLOCK)
ON T1._Fld7849RRef = T3._IDRRef
LEFT OUTER JOIN _Reference21 T4 WITH(NOLOCK)
ON T1._Fld7850RRef = T4._IDRRef
LEFT OUTER JOIN _Reference101 T5 WITH(NOLOCK)
ON T1._Fld7840RRef = T5._IDRRef
WHERE (T1._Fld7851RRef = P1 AND T1._Fld7848RRef = @P2)
ORDER BY (T1._Date_Time) ASC, (T1._DocumentTRef) ASC, (T1._DocumentRRef) ASC

Меня в плане запроса смущает 13301189 раз вызываемый  |--Clustered Index Seek(OBJECT:([ts].[dbo].[_DocumentJournal7838]....

Правильно смущает?

ну он думал, что строк с отбором по складу будет сильно меньше

вывод - кривая статистик

"13301189 раз вызываемый" - это он отобрал по складу вместо 1 133... строк и прилеплял к нему данные из кластерного индекса для вывода. по идее нужно ударить пользователя по рукам и установить сортировку по дате вместо той, которая у него стоит.

(8) разве сортировка по дате не по умолчанию?

(5) что может быть лучше чем индекс сик? Ничего

(9) по умолчанию. но пользователи любят потыкать в другие поля. а если там накладывается отбор - то возникают спецэффекты

(10) только для случая автора там явно будет лучше скан всей таблицы

без нестедлупса

а, у автора и так по дате, см ORDER BY (T1._Date_Time) ASC, (T1._DocumentTRef) ASC, (T1._DocumentRRef) ASC

тогда ладно, линейку можно убрать... на этот раз

Для выкладывающих планы запроса в тексте - отдельный котел.

(16) План в XML
https://disk.yandex.ru/d/mqsXIKbGZ4SpLg

(17) Все нормально. Лучше не получится.

Это фактический или расчетный план? Смущает расхождение ожидаемого и фактического количества данных.

Попробуй Склад индексировать с доп.упорядочиванием.

(18) мне кажется, должно получиться, если оно не будет отбирать все документы по складу, потом прилеплять к ним дату, сортировать и выводить 44 из них. что-то со статистикой. ну, или руками подкрутить индекс по складу, чтоы в него  (T1._Date_Time) ASC, (T1._DocumentTRef) ASC, (T1._DocumentRRef) добавились

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

надо итс открывать, но мне лень.

(19) доп упорядочение как раз и добавит

(18) Событие Showplan Statistics Profile - это фактический?
Попробую доп упорядочивание.

так по плану соединение каждого справочника в 3 раза дороже чем чем отбора по складу и поиска полей

(23) Оно. Но если это то, что ты выложил - у тебя статистика неактуальна.

(24) Не разочаровывайте меня, Анатолий.

а нет, там же нарстающим итогом цена

(25) Неактуальна по каким таблицам?

(28) журнала доков

(29) Сделал UPDATE STATISTICS _DocumentJournal7838 with fullscan - быстрее не стало

(30) так надо кэш планов сбросить

после жтого

(31) Да бесполезно.
Сейчас у SQL 2 пути

- найти в некластерном все записи склада, многомилионов keylook-upпом поглядеть в кластерном дату, время, ссылку и по ней сортировать эту йобу и затем вытащить 44 записи из этого. Это сейчас мы видим в плане.
- Пройтись по кластерному многомиллионов записей, выбирая записи со складами, и дождаться 44 накопленных записи. Но SQL не знает, как быстро найдутся эти записи и делать так не будет, так как читать кластерный тяжело.

Когда мы добавим до упорядочивание, возможно, SQL удовлетворится выборкой 44 записей результата поиска по некластерному, не подглядывая в кластерный.

Спасибо. Вечером запущу реструктуризацию, потом доложусь.

(33) не, ну там, вроде, есть статистика по равномерности значений. и тогда он может понять, что для получения 44 записей ему достаточно будет отсканить 1000 записей кластерного...

(35) Инфу в студию. В статистику по кластерному индексу только ссылка и входит.
Но если ты покажешь гистограмму с расшифровкой, вопрос будет снят :-)

(36) https://docs.microsoft.com/ru-ru/sql/relational-databases/statistics/statistics?view=sql-server-ver15
во, "вектор плотностей".

вообще уже давно надо нейросетку туда пихнуть, чтобы она подправляла планы

при сильном различии ожидаемого и реального

После индексирования с доп.упорядочиванием - летает!
https://disk.yandex.ru/i/iGw0OM6Pvlgn8g

(40) А скинь xml-ку

(41) всё забываю спросить, китайщину то внедрили ?
Тоскливо, но думаю, как-ить тоже придётся архив лепить.

(42) Внедрили. Работает.

(43) я пробовал подменять на-ходу обычную марку на китайщину во всех реквизитах. Красиво не получилось. Как вы реализовали, если сканят марку в реквизит, и надо быстро найти ее значение, если она уже в базе как китайщина ?

(44) В элементе справочника Марки храню md5-хеш кода марки.

Если не нашли по коду марки - ищем по md-5 хешу марки.

md-5 получаю в  виде строки так:

Функция ПолучитьХешМарки(КодМарки) Экспорт
    Хеширование=Новый ХешированиеДанных(ХешФункция.MD5);
    Хеширование.Добавить(КодМарки);
    ДвоичныеДанныеХеша=Хеширование.ХешСумма;
    Возврат ПолучитьСтрокуИзДвоичныхДанных(ДвоичныеДанныеХеша, "UTF-16LE");
КонецФункции

(44) Ну и код марки восстанавливаю, делая ее обычной маркой.

(45) ага, спасибо

(41) https://disk.yandex.ru/d/0s2KT22BGSzjhw
А каким инструментом её смотришь?

(48) MS SQL Enterprise manager

(48)SentryOne PlanExplorer неплох

(40) а текст запроса не изменился?

Всё-таки не понятно, почему изначально начал искать в индексе _Docume7838_ByField16028_RR по полю _Fld7848RRef - это точно склад а не статус?

Потому что после добавления индекса по складу с доп. упорядочиванием условие по складу устанавливается по полю _Fld7851RRef

Оч странно что такая ошибка была изначально в оценке строк...

(40) куда пропало условие по второму полю?

(0) WHERE (T1._Fld7851RRef = P1 AND T1._Fld7848RRef = @P2)

что за поля?

(51) Не менялся. Всё так же ставится отбор в журнале.

(53) Да вроде не пропал. Проверил еще раз - запрос такой же.

(54) Справочник и перечисление.


В общем, на ИТС всё написано, просто не знал:
"В варианте "Индексировать с доп. упорядочиванием" в динамическом списке может быть обеспечен эффективный просмотр больших объемов информации с отбором по значению данного реквизита и с упорядочиванием, соответствующим основному упорядочиванию для данного объекта. В этом случае наличие индекса включающего реквизит, по которому выполняется отбор, и поля основного упорядочивания позволит системе использовать индекс при просмотре списка."
https://its.1c.ru/db/metod8dev/content/2742/hdoc

(55)
>>Справочник и перечисление.

Тогда у тебя в первом запросе сначала шел поиск по перечислению, а не по складу


(55) во втором плане где у тебя поиск по _Fld7848RRef?

(56)  Еще раз проверил.
Запрос:
SELECT TOP 44
T1._Date_Time,
T1._Number,
T1._Fld7839RRef,
T1._Fld7849RRef,
T1._Fld10451,
T1._Fld17794,
T1._Fld7848RRef,
T1._Fld17793,
T1._Fld7841,
T1._Fld7850RRef,
T1._Fld7840RRef,
T1._Fld17792,
T1._Fld10524,
T1._DocumentTRef,
T1._DocumentRRef,
T1._Marked,
T1._Posted,
T2._Description,
T3._Description,
T4._Description,
T5._Description
FROM _DocumentJournal7838 T1 WITH(NOLOCK)
LEFT OUTER JOIN _Reference31 T2 WITH(NOLOCK)
ON T1._Fld7839RRef = T2._IDRRef
LEFT OUTER JOIN _Reference59 T3 WITH(NOLOCK)
ON T1._Fld7849RRef = T3._IDRRef
LEFT OUTER JOIN _Reference21 T4 WITH(NOLOCK)
ON T1._Fld7850RRef = T4._IDRRef
LEFT OUTER JOIN _Reference101 T5 WITH(NOLOCK)
ON T1._Fld7840RRef = T5._IDRRef
WHERE (T1._Fld7851RRef = P1 AND T1._Fld7848RRef = @P2)
ORDER BY (T1._Date_Time) ASC, (T1._DocumentTRef) ASC, (T1._DocumentRRef) ASC



План к нему:
https://disk.yandex.ru/d/fT1bqqXiWncsgw

(57) Сейчас да, поиск идет по двум полям. Но кажется что созданный индекс (кот. с доп. упорядочиванием) не при чем, т.к. в плане он не используется. Точнее его создание помогло в том плане что произошла реструктуризация, обновились индексы и старый кривой план был удален из кэша и и для этой таблицы был сформирован новый план. Почему был изначально кривой план сложно сказать, как вариант - для журнала был установлен отбор по какому-то очень редкому значению статуса, этот план закэшировался и потом из-за parameter sniffing стал использоваться для всех других значений перечисления.

(58) Убрал индексирование с доп.упорядочиванием - снова список умирает.

(59) Теперь ты знаешь, как убить динсписок ))

(59) Базу выложить нет возможности?

(60) Это же не динсписок, у него ОФ.

(61) Нет.
Погоняю еще.

(62) Это форма списка журнала документов, Михаил. Там запрос то почти такой же, как в динсписке.

(62) А какая разница, если можно убить? )))

(59) А покажи плиз состав индекса _Docume7838_ByDocDate_TR
а) до индексирования реквизита с доп. упорядочиванием
б) после включения индексирования реквизита с доп. упорядочиванием

(56) мало инфы. Опиши подробнее, покажи структуру хранения (с индексами)

Итого:
- включаю индексирование с доп.упорядочиванием только по статусу - летает
- включаю индексирование с доп.упорядочиванием только по складу - тормозит

Наверное так звезды сошлись для этой базы.

(в тексте запроса выше: _Fld7851RRef - склад (справочник), _Fld7848RRef - статус документа (перечисление))

(66) _Docume7838_ByDocDate_TR - там _Date_Time, _DocumentTRef, _DocumentRRef. Индекс стандартный для всех журналов.

Большое спасибо H A D G E H O G s  за просвещение!