Добрый день.

Есть запрос вида

ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
        ПО Контрагенты.Ссылка = РеквизитыКонтрагентов.Ссылка
ГДЕ
    РеквизитыКонтрагентов.Ссылка ЕСТЬ NULL

Записей в справочнике 32 млн, в регистре сведений 65 млн.

Время выполнения такого запроса 3 минуты, в результате 60 записей примерно, можно как то ускорить такой запрос?

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

"получить контрагентов, у которых отсутствуют записи в регистре сведений" //
Влоб не предлагать? Запрос к таблице регистра с выбором тамошних ссылок и этот фильтр на условие к таблице контрагентов

добавить в справочник индексируемое поле "ЕстьРеквизиты" и отбирать по нему

(2) такое сложно организовать, данные в справочник контрагенты попадаю разными способами, в том числе прямой записью в скуль, подписки не работают в этом случае, доработать все пути создания много времени займет

(1) не совсем понятно, как применить фильтр к таблице? можно набросок запроса?

(3) >в том числе прямой записью в скуль
ну сами себе расставили грабли - сами и терпите. Без денормализации в любом случае будет скан, причем обеих таблиц.

можно сделать отдельный РС "КонтрагентыБезРеквизитов", будет даже эффективнее, чем (2)

(0) "Записей в справочнике Контрагенты 32 млн" - "центральная" налоговая или ПФР что ли? Откуда столько контрагентов взяли?

150 млн население страны. 32 млн контрагентов у (0).
Это получается примерно по 1 контрагенту на каждые пять человек, включая младенцев и стариков.
Может это не контрагенты все-таки?

(8)У них филиал транснациональной корпорации, что ты понимаешь :)

(6) а как его заполнять? сначала же также надо найти их

(7) есть дубли контрагентов, с ними боремся, но борьба продвигается не быстро

а скока показывает
ВЫБРАТЬ РАЗЛИЧНЫЕ
Ссылка
ИЗ
РегистрСведений.РеквизитыКонтрагентов

?

(9)Правда работают только с 60-ю

(12) явно 32 000 000 - 60

(10) повесить фоновое раз в 600 сек

(10) фоновое уже висит, много ресурсов отнимает, ищу способы уменьшить

(4) "где"

(11) Дубли!!!! Да у вас фабрика клонов - фирм однодневок. На каждый день новую по ходу заводите. Как фирмы прокладки.
Причину появления двойников устранили?

https://crate.io/a/sql-subquery-vs-left-join/

вот например люди ускорили LEFT JOIN / IS NUL в 260 раз ))

(18) видел я одну конфу еще на клюшках.., там спр номенклатура сделали подчинение спр Организации...

(18) причину устраняем, но процесс не быстрый, а болит уже сейчас
не об этом речь, в каком направление двигаться и так понятно и работы ведутся.

хотел бы пока ускорить временное решение

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

***************
ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ГДЕ
    НЕ РеквизитыКонтрагентов.Ссылка В
        (ВЫБРАТЬ
                РеквизитыКонтрагентов.Ссылка КАК Ссылка
        ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
        СГРУППИРОВАТЬ ПО
                РеквизитыКонтрагентов.Ссылка)

(21) Расскажи всем какой быстрее. Только запусти по раза два хотя бы каждый.

(22) Ты попал мужик

(21) У старых контрагентов реквизиты заполняются? Может сделать проверку только для "новых"? Скажем, последние 10% кодов.

(23) между LEFT JOIN и NOT EXISTS нет разницы
https://dba.stackexchange.com/questions/121034/best-practice-between-using-left-join-or-not-exists

(26) Тссс!!!!

(22) Фу

(0) ну для начала я бы не выбирал наименование на этом шаге, поигрался бы с индексированием .

(28) Подожди ФУ. :-)
Ща (0) расскажет, а зачем он этот запрос вообще хочет.

Сегодня не пятница? Нет?
Странно.

(22) первый запрос 80 секунд, второй 120, но уже быстрее простого левого соединения, спасибо за пример.

сейчас еще план запроса посмотрю

еще можно

ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
Где не 1 в (ЫБРАТЬ первые 1
                1
        ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов где РеквизитыКонтрагентов.Ссылка = Контрагенты.Ссылка)

А так может и дольше, так как ВТ большая может быть...

ВЫБРАТЬ
   РеквизитыКонтрагентов.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_РеквизитыКонтрагентов
ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
СГРУППИРОВАТЬ ПО
   РеквизитыКонтрагентов.Ссылка
ИНДЕКСИРОВАТЬ ПО
   РеквизитыКонтрагентов.Ссылка
;

ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеквизитыКонтрагентов КАК РеквизитыКонтрагентов  
        ПО Контрагенты.Ссылка = РеквизитыКонтрагентов.Ссылка
ГДЕ
    РеквизитыКонтрагентов.Ссылка ЕСТЬ NULL

****************************
По аналогии (33)

ВЫБРАТЬ
   РеквизитыКонтрагентов.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_РеквизитыКонтрагентов
ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
СГРУППИРОВАТЬ ПО
   РеквизитыКонтрагентов.Ссылка
ИНДЕКСИРОВАТЬ ПО
   РеквизитыКонтрагентов.Ссылка
;

ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
Где не 1 в (ВЫБРАТЬ первые 1
                1
        ИЗ ВТ_РеквизитыКонтрагентов КАК РеквизитыКонтрагентов где РеквизитыКонтрагентов.Ссылка = Контрагенты.Ссылка)

(33) 32 мильёна запросов к регистру?

(35) Там по одной записи, первой попавшейся.

(32) Так что там с производительностью? Еще "три" варианта скинули :-)

зачем поле Наименование?

(38) В (0) было. По мне так оно не нужно.

(36) Просто всегда полагал, что большое количество запросов, даже по одной записи будет долго.
Интересно посмотреть что будет у автора на практике.

(40) На самом деле не долго, но на 32 миллионах записях интересно посмотреть.... :-)

v8: запрос not exists

(42) Там (33) модератор :-)

(0), (32) Каков результат-то? Обед что ли?

коллеги не волнуйтесь, я тут еще, обновляется статистика, сейчас закончится, продолжу тесты.
:) спасибо за участие

(45) никто не волнуется, всем пофиг))

Я волнуюсь

(43) я и тут модератор. а где 1 в (выбрать первые 1 1 ) преобразуется в exists

(48) А такой запрос преобразуется в exists?
где А.А1 в (выбрать первые 1 В.А1 ИЗ ....)

(0) Записей в справочнике 32 млн, в регистре сведений 65 млн. ... Поля связи все проиндексированы.

А толку от индексов, если лопатить целиком две таблицы?

(50) чтобы мердж делать

А вам точно наименование нужно? Уберите его из запроса.

(26) https://crate.io/a/sql-subquery-vs-left-join/
    там вполне рабочий способ. Должен Ускорить запрос раз в 1000
    что-то так
    
ВЫБРАТЬ
    СправочникКонтрагенты.Ссылка
ИЗ
    Справочник.Контрагенты КАК СправочникКонтрагенты
ГДЕ
    СправочникКонтрагенты.Ссылка НЕ В (ВЫБРАТЬ Владелец ИЗ РегистрСведений.ПериодическиеРеквизитыКонтрагент)

(53) Там используется оператор IN, но никак не NOT IN.
Не годится.

(53) думаешь у всех дб одинаковые косяки?

Я думаю не важно.
  Суть такая, что когда делаешь левое соединение, то анализируется таблица в 60 млн записей на is null
  А когда Ссылка НЕ В () там используются индексы
  У меня нет под рукой базы с большим количеством записей

(51) кстати, насчет мерге... Нужно переписать запрос как-то так, и должно быть щастье:

ВЫБРАТЬ
    Контрагенты.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Ссылки
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
        ПО Контрагенты.Ссылка = РеквизитыКонтрагентов.Ссылка
ГДЕ
    РеквизитыКонтрагентов.Ссылка ЕСТЬ NULL

;

ВЫБРАТЬ
    Контрагенты.Ссылка КАК Ссылка,
    Контрагенты.Код КАК Код,
    Контрагенты.Наименование КАК Наименование,
    Контрагенты.ИНН КАК ИНН
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Ссылки КАК ВТ_Ссылки
        ПО Контрагенты.Ссылка = ВТ_Ссылки.Ссылка

Это если РеквизитыКонтрагентов.Ссылка - первое измерение в РС

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

(0)
Может это сделать на sql , все равно как вы же сами пишите запись у вас идет и напрямую.

Известно, что итоговых записей будет мало

Измерение
РеквизитыКонтрагентов.Ссылка

на первом месте?

Регистр не периодический?

(57) Думаешь, наименование портит merge из за того, что используется некластерный индекс по наименованию?

(0) первые 100 как вариант

А если выбрать одну таблицу во временную с +1,а потом обьединить с другой но -1 и сгруппировать?

Вообще забавно.
Я бы повтыкал в профайлер.
Автор, дашь базу или доступ?
Я ниче не сломаю.

(65) Особенно базу ))), чего сразу не написал выложи куда нить .

(66) ftp-шник, гугл диск, нуачо. SQL жмет базы только в путь

(62) ага

(58) в (34) сразу написал, что может быть медленнее, если большая таблица в ПОМЕСТИТЬ. Но тут смотреть надо. От данных зависит.
Что за конфигурация не написано. Может каждый реквизит отдельной записью РС храниться. Даже не может, а скорее всего так и есть...

Вопрос как (0) собирается базу от двойников чистить. Если реквизитов нет, то удалить? А если есть....
(63) Правильно написал. Найди одинаковых. Пометь основного. Посмотри на реквизиты. Далее замена ссылок и удаление. Если РБД, то ещё веселее - надо обмены смотреть.

провел еще тесты
(33) заняло по времени 115 секунда
(34) первый запрос 215, второй 138, записей много, логично что дольше стало

(52) похоже на (22) вторую часть, проверю сейчас немного по другому, но думаю так же будет примерно 120 секунд

(61) измерение было второе, сделал первым, результат остались примерно такими же

(72) регистр периодический?

(66) базу не могу к сожалению, 3 терабайта и безопасники не разрешат :(

(72) независимый, забыл отписать, индекс кластерный и там и там

(69) как двойники чистить уже проработали, с этим нет сложностей, просто на это надо время, база филиальная, путей созданий контрагентов много, выше отписывался, порядок наводим потихоньку

(70) Замеры запросом напрямую SQL делали ?

Тогда ставлю на mdop<>1

самый простой сейчас способ ускориться  - вынести таблицы на отдельные диски

(78) в данном случае мдоп > 1 таки как раз может ускорить

(75) Собери план запроса, действительный и в xml. Только так можно победить

(81) а что там собирать: 2 скана и мердж

3 минуты для такого объема данных это много? Как часто запускается запрос?

(82) Это делается за десяток секунд.

А так, по существу, можно запустить параллельно несколько обработок с неким фильтром.

Я ни разу не эксперт, но мысль такая пришла - скан кластерного индекса по сути есть скан всей таблицы. А если сделать отдельный некластерный индекс по ссылке? Он же будет сравнительно небольшим по объему и должен гораздо быстрее сканироваться.

Или же выбор колонки из индексированного поля кластерного индекса не будет приводить к чтению всей таблицы? Подскажите, знатоки MS SQL?

(86) Идем на партнерку и просим 1С сделать кастомные индексы

(87) Ну раз они уже нарушают и создают контрагентов прямой записью в SQL (и огребают сейчас проблем из-за этого), то добавить кастомный индекс не должно быть проблемой. Ну и эксперимента ради - хотя бы для проверки теории.

(86) некластерный уже пробовал, прироста можно сказать нет, есть погрешности в +- 5 секунд при выполнении запросов, но это от нагрузки зависит

(81) план запроса скан и мердж, и с кластерными индексами и с некластерными, одинаковый

Так. У меня подобное соединение справочника на 4млн и РС на 2.8 млн занимает 2.7 секунды на прогретом кеше.

У меня 2 некластерных скана и hashjoin

(78) с mdop пока поиграться не дает dba админ, попробую в выходные

(8)
Контрагенты и физлица это как бы совсем не одно и то же

(86) Что-то ты до кластерного докапываешься. По нему ведь не только скан бывает.

А автор случаем проверяет не в консоли запросов? Вдруг у него там стоит выводить 100000 записей и основное время тратится на вывод строк?

(96) в консоле, но в результат выводится 60 записей

(91) супер компьюче у вас

Ну на самом деле у меня новые вопросы к SQL

(98) так 4 мегазаписи гуидов это всего-то в районе ста мегабайт табличка

Как так то?
http://prntscr.com/ov5387

Зачем здесь индекс _Reference51_ByPredefinedIDNotUniq, если в нем нет поля _IDRRef ?

Или SQL неявно обращается к данным и на плане запроса это не показывает?

Вот текст запроса
SELECT
T1._IDRRef,
T2._Fld27656
FROM dbo._Reference51 T1
LEFT OUTER JOIN dbo._InfoRg27654 T2
ON ((T2._Fld27655RRef = T1._IDRRef)) AND (T2._Fld931 = P1)
WHERE ((T1._Fld931 = @P2)) AND (T2._Fld27655RRef IS NULL)

(92) у меня тоже hashjoin, если убрать наименование из запроса, по времени 170 секунд заняло

(103) Ты скриншот плана запроса покажи, как я в (101)

(101) Или я что-то не знаю про HashJoin ?

(101) а WHERE ((T1._Fld931 = @P2)) что?

(106) ОбластьДанных. Живите теперь с этим.

(107) Купила баба порося.

(107) а, это... я помню, жестко выпиливал как-то раз это разделение к херам. не нужно оно в 90% случаев, однако пихают во все тповые.ю оставили бы в корп версиях только - всем бы жилось легче

(109) Как ты его выпилишь? Оно же - памятник.

(104) https://prnt.sc/ov599f

(110) сначала напильником, потом шкуркой, потом мягкой тряпочкй

но это гомункул был, созданный из демо базы БСП, а не типовая

(111) В регистре сведений в измерении сидит еще кто-то кроме этого справочника?

(114) нет, только один справочник контрагенты

(115) А, все, вижу, в РС больше записей чем в справочнике. Поэтому хэш для справочника делается.

(115) Ну, попробуйте воткнуть индекс в справочник по полю _IDREF

(117) результаты примерно такие же по времени.
Коллеги всем спасибо за помощь, пока выбрали вариант из (22), ускорение хоть и небольшое, всего в 2 раза, но хоть что-то